Re: Thanks - Part One - Re: Executing SP in VB6

#1

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.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:

Quote:

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=SICC EV;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=SICC EV;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

#2

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 bigconcern for your specific application?

-----Message d'origine-----
De : pgsql-odbc-owner (AT) postgresql (DOT) org [mailto

gsql-odbc-owner (AT) postgresql (DOT) org] De la part de Sandro Yaqub Yusuf
Envoyé : mardi, 5. octobre 2004 19:56
À : pgsql-odbc (AT) postgresql (DOT) 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 (AT) eldocomp (DOT) com>
To: "Sandro Yaqub Yusuf" <sandro (AT) proservvi (DOT) 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:

Quote:

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=SICC EV;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=SICC EV;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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Thanks - Part One - Re: Executing SP in VB6

comp.databases.postgresql.interfaces.odbc comp.databases.postgresql.interfaces.odbc

Re: Thanks - Part One - Re: Executing SP in VB6 - 10-05-2004 , 12:55 PM

Re: Thanks - Part One - Re: Executing SP in VB6 - 10-06-2004 , 01:40 AM