Re: Thanks - Part One - Re: Executing SP in VB6 - Mailing list pgsql-odbc

It SHOULD work.

Where do you get the error from? The server? VB?

If, as suggested by Andrew, you change the SQL query to:

'SELECT usuario FROM user WHERE user = $1'

... then you have to change the return value of your function. What you have done, certainly.

You should have something like:

CREATE OR REPLACE FUNCTION sp_user_search(varchar)
  RETURNS int4 AS
  ' select usuario from user where user = $1'
LANGUAGE 'sql' VOLATILE;

... in case usuario is an int4.

Limiting data throughput is always a good idea. But maybe this is not a big concern for your specific application?


-----Message d'origine-----
De : pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] De la part de Sandro Yaqub Yusuf
Envoyé : mardi, 5. octobre 2004 19:56
À : pgsql-odbc@postgresql.org
Objet : Re: Thanks - Part One - Re: [ODBC] Executing SP in VB6

Hello Andrew...

I already tried to do that, but it didn't work.

PostGresQL return me a ERROR saying that the syntax was invalidates.



But thank you even so for the help.


Sandroyy



----- Original Message -----
From: "Andrew Ayers" <aayers@eldocomp.com>
To: "Sandro Yaqub Yusuf" <sandro@proservvi.com.br>
Sent: Tuesday, October 05, 2004 1:14 PM
Subject: Re: Thanks - Part One - Re: [ODBC] Executing SP in VB6


I have never done any of this, but looking at your code, it seems that
you would want to change the select in the stored procedure to something
like:

'SELECT usuario FROM user WHERE user = $1'

This should only return the full user name as desired, I think. Unless
you are wanting something more "generalized", so you can do 'SELECT *',
then isolate a column (any one of them, however many there are) from the
result set - I am certain there is a way to do it, but having never
played around with this, I am unable to offer much more advice.

Andrew Ayers

Sandro Yaqub Yusuf wrote:
> Hello my dears friends,
>
> Thanks for yours colaborations.
>
> It´s here the part of solution about my problem with colaboration of
> everbody that help me (Philippe Lang, Iain, Andrew Ayers, Rick
> Sivernell, Corey W. Gibbs):
>
>
---------------------------------------------------------------------------------------------------------------------------------------
>
>
> POSTGRESQL:
>
> CREATE OR REPLACE FUNCTION sp_user_search(varchar)
>  RETURNS SETOF user AS
> ' select * from user where user = $1'
>  LANGUAGE 'sql' VOLATILE;
>
>
---------------------------------------------------------------------------------------------------------------------------------------
>
>
> VISUAL BASIC 6 with ADO 2.8 without STORED PROCEDURE:
>
> Dim adoBD As ADODB.Connection
> Dim rsTB    As New ADODB.Recordset
>
> Set adoBD = New ADODB.Connection
>
> adoBD.ConnectionString =
> "driver=PostgreSQL};server=localhost;database=SICCEV;port=5432;uid=ryan;pwd=displace;"
>
> adoBD.Open
>
> Set rsTB = adoBD.Execute("select * from user where user = 'Sandro';")
>
> Do While Not rsTB.EOF
>       MsgBox rsTB!Usuario
>
>       rsTB.MoveNext
> Loop
>
> rsTB.Close
> adoBD.Close
>
> RESULT OF EXECUTION: Sandro Yaqub Yusuf
>
>
---------------------------------------------------------------------------------------------------------------------------------------
>
>
> VISUAL BASIC 6 with ADO 2.8 with STORED PROCEDURE:
>
> Dim adoBD As ADODB.Connection
> Dim rsTB    As New ADODB.Recordset
>
> Set adoBD = New ADODB.Connection
>
> adoBD.ConnectionString =
> "driver=PostgreSQL};server=localhost;database=SICCEV;port=5432;uid=ryan;pwd=displace;"
>
> adoBD.Open
>
> Set rsTB = adoBD.Execute("select sp_user_search('Sandro');")
>
> Do While Not rsTB.EOF
>       MsgBox rsTB(0)
>
>       rsTB.MoveNext
> Loop
>
> rsTB.Close
> adoBD.Close
>
> RESULT OF EXECUTION: (1,Sandro,123,"Sandro Yaqub Yusuf")
>
>
---------------------------------------------------------------------------------------------------------------------------------------
>
>
> The question is how I do for to isolate the columns when come
> (1,Sandro,123,"Sandro Yaqub Yusuf"). I would like to get only the column
> FULLNAME using the STORED PROCEDURE. Have I do a function in VB for to
> isolate the RESULTS of STORED PROCEDURES in POSTGRESQL ?
>
> Thanks more one times...
>
> Sandroyy
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
>
>


-- CONFIDENTIALITY NOTICE --

This message is intended for the sole use of the individual and entity to
whom it is addressed, and may contain information that is privileged,
confidential and exempt from disclosure under applicable law. If you are not
the intended addressee, nor authorized to receive for the intended
addressee, you are hereby notified that you may not use, copy, disclose or
distribute to anyone the message or any information contained in the
message. If you have received this message in error, please immediately
advise the sender by reply email, and delete the message. Thank you.


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org