ODBC API FOR PROGRAM CALLS

Databases
1

In visual basic there are methods to declare a database application program object (server side program) and execute it. I am using Universe by Rocket Software and its ODBC driver has this capability. Does XOJO have a way to call database subroutines?

2

Is this similar to a stored procedure? You can usually call stored procedures by name using the SQLSelect or SQLUpdate methods of the Database class.

3

I haven’t hooked up to a picklike system via ODBC since 2005 but calling a server side program does act as a stored procedure as far as the ODBC connection is concerned.

4

Paul and Bog,

Thanks for the pointers. Let me give it a try and I’ll let you know how it worked out.

5

For prosperity if anyone has this problem here are my observations and some sample code.

In Universe the ODBC connection has to be set up on the UVODBC Administrator Application. that’s easy, even a caveman could do it.

Make sure the XOJO ODBCdatabase plug in is installed.

I very quickly got the system to execute a call to a UV program called ASERVER. This program was just a shell that changed the passed parameters and announced that is was successfully called.

I was total unable to get the “bind” method to work. ODBC on UV got the bind request but it never worked and threw an error. Because of this I was forced to do the SQLSelect using literals. This gets tricky, is really ugly since I had to build a "call " command to insert the values of the call parameters into the call statement as literals. See the code snippet " setup for call statement
" below.

Next problem was getting the return data. This really sucked. Since I could not bind the call parameters, I could not get the data passed back directly. As a result, the return data was set up as a report which is treated like a print file. In the program ASERVER, I had to print the return parameters. (since these will be XML or JASON this is not really a problem. When you do this, you have to handle it like a multi-record read.

So here is the code. If anyone has any better ideas on how to do this, I would appreciate a shout out from you.

[code]DIM a As String
a = “1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890”
DIM b As String
b “abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVW”

DIM LARRY AS NEW ODBCDatabase
LARRY.DataSource = “ULARRY”

'************************* connect
IF LARRY.CONNECT THEN
listbox1.addrow(“connection sucessful”)
else
MsgBox(“Not Connected”)
END

'****************** end connect

‘****************** setup for call statement
DIM PAREN AS STRING
PAREN = "’" + a +"’,"+"’" + b +"’"
PAREN = “{CALL ASERVER(” + PAREN + “)}”
listbox1.addrow(paren)

'********************** end of call statement setup

DIM ps As ODBCPreparedStatement
ps = ODBCPreparedStatement( LARRY.Prepare(PAREN) )
Dim c as RecordSet
c = ps.SQLSelect

listbox1.addrow(“error message " + LARRY.ErrorMessage)
listbox1.AddRow(”")
listbox1.Addrow(“The value of the return values are”)

while not c.EOF
for i As Integer = 0 to c.fieldCount - 1
listbox1.addrow(c.idxField(1).StringValue)
next
c.movenext
wend

listbox1.AddRow("")
listbox1.AddRow(“end of return data”)

LARRY.Close[/code]

Edit (Paul): Added Code tags.