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