Re: Executing SP in VB6

Hello Sandro. Good idea to use stored procedures. I'm using MS Access, so I guess you may have to adapt the code a bit...

This code uses DAO, not ADO or whatever. I hope it will help you.

Here we are:

1) The stored procedure:
------------------------
CREATE FUNCTION public.search_your_tbl_name(varchar)
  RETURNS SETOF your_tbl_name AS
'
SELECT * FROM public.your_tbl_name
WHERE lower(id) LIKE lower($1)
OR lower(foo1) LIKE lower($1)
OR lower(foo2) LIKE lower($1)
OR lower(foo3) LIKE lower($1)
OR lower(foo4) LIKE lower($1)
ORDER BY foo2
LIMIT 50
'
  LANGUAGE 'sql' VOLATILE;

2) The VBA code:
----------------
Public Function global_dsn_name() As String
    global_dsn_name = "your_dns_name"
End Function

Sub query_run(query As String, p As String)
On Error GoTo query_runError

    Dim MyWorkspace As DAO.Workspace
    Dim MyConnection As DAO.Connection
    Dim MyRecordset As DAO.Recordset
    Dim MySQLString As String
    Dim MyODBCConnectString As String
    
    Set MyWorkspace = CreateWorkspace("ODBCWorkspace", "", "", dbUseODBC)
    MyODBCConnectString = "ODBC;DSN=" & global_dsn_name() & ";"
    Set MyConnection = MyWorkspace.OpenConnection("Connection1", dbDriverNoPrompt, , MyODBCConnectString)
    MySQLString = "SELECT * FROM public." & """" & query & """" & "('" & p & "');"
    Set MyRecordset = MyConnection.OpenRecordset(MySQLString, dbOpenDynamic)
    
    With MyRecordset
        Do While Not .EOF
        
            Debug.Print _
            MyRecordset("id") & " / " & _
            MyRecordset("foo1") & " / " & _
            MyRecordset("foo2") & " / " & _
            MyRecordset("foo3") & " / " & _
            MyRecordset("foo4")
            
            .MoveNext
        Loop
    End With
    
    MyRecordset.Close
    Set MyRecordset = Nothing
    
    MyConnection.Close
    Set MyConnection = Nothing
    
    MyWorkspace.Close
    Set MyWorkspace = Nothing

query_runExit:
    Exit Sub

query_runError:
    MsgBox "Error in query_run."
    Resume query_runExit
End Sub 

3) How you use it:
------------------
query_run("search_your_tbl_name", "test%")

4) MS Access & reports:
-----------------------
The problem with MS Access is that sometimes you may want to use the result of your stored procedure in a report, for example. In this case, you have to store the query instead of simply running it. You won't need this with VB6, but I put the code here also, it might help others.

Sub search_store(query As String, p As String)
On Error GoTo search_storeError

    Dim MyDatabase As DAO.DataBase
    Dim MyQueryDef As DAO.QueryDef
    
    Set MyDatabase = CurrentDb()
    If (QueryExists(query)) Then MyDatabase.QueryDefs.Delete query
    Set MyQueryDef = MyDatabase.CreateQueryDef(query)

    MyQueryDef.Connect = "ODBC;DSN=" & global_dsn_name() & ";"
    MyQueryDef.SQL = "SELECT * FROM public." & """" & query & """" & "('" & p & "');"
    MyQueryDef.ReturnsRecords = True
    
    MyQueryDef.Close
    Set MyQueryDef = Nothing
    
    MyDatabase.Close
    Set MyDatabase = Nothing
    
search_storeExit:
    Exit Sub

search_storeError:
    MsgBox "Error in search_store."
    Resume search_storeExit
End Sub


This routine creates a querydef called "search_your_tbl_name" you can incorporate in a report.

________________________________

De : pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] De la part de Sandro Yaqub Yusuf
Envoyé : lundi, 4. octobre 2004 21:13
À : pgsql-odbc@postgresql.org
Objet : [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