Re: Thanks - Part One - Re: 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(at)eldocomp(dot)com>
To: "Sandro Yaqub Yusuf" <sandro(at)proservvi(dot)com(dot)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.