Re: Executing SP in VB6

I've never done it either, but I have some code (which incidentally I got
from asktom.oracle.com) that you may be able to adapt. The original code
uses a simple select and bind variables. Because it uses binds it should be
easy to adapt to a SP's parameters:

Sub ParameterExample()

Dim cmd As New ADODB.Command

Dim rs As New ADODB.Recordset

Dim prm As ADODB.Parameter

cmd.ActiveConnection = Provider=OraOLEDB.Oracle;User
ID=scott;Password=tiger;Data Source=oraDSN;

' Set the command's text, and specify that it is an SQL statement.

cmd.CommandText = "SELECT item_name FROM v_syouhin_info WHERE deptno = ?"

cmd.CommandType = adCmdText

' Set up a new parameter for the select.

Set prm = cmd.CreateParameter("deptno", adInteger, adParamInput, , 10)

cmd.Parameters.Append prm

' Create a recordset by executing the command.

Set rs = cmd.Execute

' Loop through the recordset and print the first field.

Do While Not rs.EOF

Debug.Print rs(0)

rs.MoveNext

Loop

' Close the recordset.

rs.Close

End Sub

Modify it as follows to use a stored procedure:

Sub ParameterExample()

Dim cmd As New ADODB.Command

Dim rs As New ADODB.Recordset

Dim prm As ADODB.Parameter

cmd.ActiveConnection = Provider=OraOLEDB.Oracle;User
ID=scott;Password=tiger;Data Source=oraDSN;

' Set the command's text, and specify that it is an SQL statement.

cmd.CommandText = "STORED_PROCEDURE_NAME" ' <-- changed

cmd.CommandType = adCmdStoredProc ' <-- changed this part too

' Set up a new parameter for the select.

Set prm = cmd.CreateParameter("deptno", adInteger, adParamInput, , 10)

cmd.Parameters.Append prm

' Create a recordset by executing the command.

Set rs = cmd.Execute

' Loop through the recordset and print the first field.

Do While Not rs.EOF

Debug.Print rs(0)

rs.MoveNext

Loop

' Close the recordset.

rs.Close

End Sub

In the second case the stored procedure has one in parameter which is deptno
and the out parameter (or possibly the function return) is a result set.

As I said, I havn't tried it myself, so I'd be interested to know if you get
it working on PG.

Regards
Iain

----- Original Message -----
From: Sandro Yaqub Yusuf
To: Goulet, Dick
Cc: pgsql-odbc(at)postgresql(dot)org
Sent: Tuesday, October 05, 2004 4:24 AM
Subject: Re: [ODBC] Executing SP in VB6

Hello my friend,

That is simple. I work developing programs in Visual Basic using complex
Stored Procedures in SQL-SERVER 7/2000.
I want to make STORED PROCEDURES in PostGresQL using Visual Basic 6. I
need one example to begin.

Thanks,

Sandroyy
----- Original Message -----
From: Goulet, Dick
To: Sandro Yaqub Yusuf ; pgsql-odbc(at)postgresql(dot)org
Sent: Monday, October 04, 2004 4:15 PM
Subject: RE: [ODBC] Executing SP in VB6

May I ask why you want to use a stored procedure? A simple "Select
fullname from users where user = '<value>';" should work just fine.

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

----------------------------------------------------------------------------
From: Sandro Yaqub Yusuf [mailto:sandro(at)proservvi(dot)com(dot)br]
Sent: Monday, October 04, 2004 3:13 PM
To: pgsql-odbc(at)postgresql(dot)org
Subject: [ODBC] Executing SP in VB6

Hello,

Please, I just trying search in all WEB SITES about using STORED
PROCEDURES with VISUAL BASIC, but I not found anything about this that can
help me.

I have a table: USERS with colums IDUSER, USER, PASSWORD, FULLNAME
I need get the colum FULLNAME with parameter USER.
How I do to create and execute a STORED PROCEDURE in VISUAL BASIC 6 to
resolve this litle problem ? (please, forgiven my english because I living
in BRAZIL and not speak english).

Thanks,

Sandroyy