Recordsets as parameters, passing by reference

Hello,

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:

  1. 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.
  2. 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.

  1. Is passing a recordset as a parameter, by reference, to a method possible at all?
  2. What methodology do you use to separate data retrieval from data processing?

Help is much appreciated!

Thank you,
Stan

not sure you can pass a recordset as an input parameter, use a databaserecord instead.

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.

I do it all the time… it allows me to treat the recordset contents independent from its source database/table

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.

Byref is unlikely to be required since a recordset IS a reference type

Thank you Guys!

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

RS is only NIL under two conditions

  1. you never executed any SQL to instantiate it
  2. the SQL you executed was invalid (I don’t see any error checking on you SQL statement

Dave,

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?

Stan

in this case this WOULD require byref

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

I would put the RS in the return value. Or better, encapsulate the RS in the black box and mimic the necessary accessor methods.