Re: Executing SP in VB6 - Mailing list pgsql-odbc

From Iain
Subject Re: Executing SP in VB6
Date
Msg-id 009901c4aa7b$1c0d75c0$7201a8c0@mst1x5r347kymb
Whole thread Raw
In response to Re: Executing SP in VB6  ("Goulet, Dick" <DGoulet@vicr.com>)
List pgsql-odbc
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 -----
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 -----
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@proservvi.com.br]
Sent: Monday, October 04, 2004 3:13 PM
To: pgsql-odbc@postgresql.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
 
 

pgsql-odbc by date:

Previous
From: Andrew Ayers
Date:
Subject: Re: Access and PG ODBC problem
Next
From: "Iain"
Date:
Subject: Re: Executing SP in VB6