my application is going to be database heavy. I want to implement interactions with ODBC/SQL Server in ‘black box’ methods.
Here is how I envision it:
From MainMethod, I am passing an SQL statement and a RS reference to a ‘black box’ SQL method that retrieves the data and puts them in RS.
I process RS data in MainMethod.
Basically, I want the repeatable, standard code (ODBC/SQL connection, authentication, error handling etc) in ‘black box’ methods. And, to process unique data in many unique, dedicated methods.
Is passing a recordset as a parameter, by reference, to a method possible at all?
What methodology do you use to separate data retrieval from data processing?
Yes, you can pass a RecordSet as a parameter. Since it is an object and not an intrinsic data type, it is passed by reference anyway (since it’s just a reference or “pointer” to the actual object), so ByRef isn’t required.
with a recordset, how do you add a field/fieldvalue manually ?
you’re forced to use a sqlselect, so you can only return one request ?
how do you merge recordsets ?
with a databaserecord it’s easy to do.
Something doesn’t work here, or I am doing a mistake.
ODBC connection - verified and works,
SQL Statement - verified and works.
The code was mostly copied from the Xojo documentation, except splitting it between 2 methods. If I access rs in the App.GetData, rs has the data inside and it works ok. But if I pass rs as a parameter, it doesn’t work (rs works in App.GetData, but it doesn’t work in the outer/calling Shown event).
(ODBC connection or statement are not an issue here, because I see expected results when I access rs directly from the App.GetData method.)
App.GetData:
parameters: sql As String,rs As RecordSet
Dim db As New ODBCDatabase
db.DataSource = "NorthWind2"
If db.Connect Then
//proceed with database operations
MsgBox("Database OK")
rs=db.SQLSelect(sql)
Else
MsgBox("Error: " + db.ErrorMessage)
End If
db.Close
WebPage Shown Event:
Dim rs As RecordSet
App.GetData("SELECT CAST(GETDATE() AS varchar) mydate",rs)
If rs Is Nil Then Return // !!!PROBLEM HERE!!! rs is Nil here
While Not rs.EOF
MsgBox(rs.Field("mydate"))
MsgBox(rs.IDXField(1))
rs.MoveNext
Wend
I am sure there is data in rs immediately after calling ODBC database connection (rs=db.SQLSelect(sql)) in App.GetData. It tested it and I saw expected data. So, 1) and 2) are not the problems here.
But, the data in rs disappear when I access it from outside of App.GetData, even though this is where rs is actually declared.
BTW, I tested it on different computers, different servers, different databases, projects.
Could the problem be related to ODBC connection? Does rs data disappear when the ODBC connection is closed?
Second do NOT close the database
That pretty much assures that the recordset WILL not work
Some recordsets may rely on data still cached on the server and only fetch small amounts as you step through the recordset
Closing the db can make that break because, well the underlying db connection is closed.
Thirdly why no just return a recordset (or nil) ?
App.GetData( sql As String) As RecordSet
Dim db As New ODBCDatabase
db.DataSource = "NorthWind2"
If db.Connect Then
//proceed with database operations
MsgBox("Database OK")
rs=db.SQLSelect(sql)
Else
MsgBox("Error: " + db.ErrorMessage)
return nil
End If
and the caller can deal with the recordset being <> nil or nil accordingly