I’ve connected to, and tried both examples of a select against my database… and I keep getting an ‘unsupportedOperationException’ on anything I do with viewing anything from the recordset:
Tried sample 1:
[code] dim eventEntries as recordSet
if sqlDb.connect then
print “connected”
eventEntries = sqlDb.sqlSelect(“SELECT * FROM vw_pendingCommands WHERE unitId = 135790”)
sqlDb.close
if not sqlDb.error then
if eventEntries <> nil then
print eventEntries.field(“unitId”).stringValue <— get the error on this line. I’ve tried viewing other things like “print str(eventEntries.recordCount)” and still get the error
else
print “null recordset”
end if
else
print sqlDb.ErrorMessage
end if
end if[/code]
Tried sample 2:
[code] dim eventEntries as recordSet
dim test as msSqlServerPreparedStatement
if sqlDb.connect then
print “connected”
test = sqlDb.prepare(“SELECT * FROM vw_pendingCommands WHERE unitId = ?”)
test.bindType(0, msSqlServerPreparedStatement.MSSQLSERVER_TYPE_INT)
test.bind(0, 135790)
eventEntries = test.sqlSelect
sqlDb.close
if not sqlDb.error then
if eventEntries <> nil then
print eventEntries.field(“unitId”).stringValue <— get the error on this line. I’ve tried viewing other things like “print str(eventEntries.recordCount)” and still get the error
else
print “null recordset”
end if
else
print sqlDb.ErrorMessage
end if
end if[/code]
Nothing I’m trying is working. Anybody have any idea what I may be doing wrong?
Well, I found the actual culprit. Seems that a recordSet contains its information on the supported databases where it features a .recordCount (see the documentation for listed databases) and holds this data even if the database closes; however, a recordSet called against an MSSQLServerDatabase immediately loses its data once it’s closed. Matter of fact, if I byRef a database out of a method and close the database connection immediately AFTER I passed the byRef, I get the same error. Is this by intended design? I’m not sure how the internal xojo framework handles the recordSet functioning; however, there must be something going on that would only make sense on that end. For instance, recordSet.recordCount is not supported with MSSQLServerDatabase; however, a simple “SELECT count(*) as recordCount WHERE {your syntax here}” reveals this information. I’m sure they know this, but it’s not included internally. So, something else must be going on… and this is probably related to why the recordSet on an MSSQLServerDatabase needs an open database connection instance to function properly, whereas other databases don’t?
Won’t let me edit the above… so here it is with the edits to make more sense:
Well, I found the actual culprit. Seems that a recordSet contains its information on the supported databases where it features a .recordCount (see the documentation for listed databases) and holds this data even if the database closes; however, a recordSet called against an MSSQLServerDatabase immediately loses its data once the database session is closed. Matter of fact, if I byRef a recordSet out of a method and close the database connection immediately AFTER I passed the byRef, I get the same error. Is this by intended design? I’m not sure how the internal xojo framework handles the recordSet functioning; however, there must be something going on that would only make sense on that end. For instance, recordSet.recordCount is not supported with MSSQLServerDatabase; however, a simple “SELECT count(*) as recordCount WHERE {your syntax here}” reveals this information. I’m sure they know this, but it’s not included internally. So, something else must be going on… and this is probably related to why the recordSet on an MSSQLServerDatabase needs an open database connection instance to function properly, whereas other databases don’t?
RecordSet is a database cursor. It needs an open database connection. Closing the database means closing all its record sets.
RecordCount on the other side (for the databases which support it without the need to move to the last record) is - I assume - a variable which is stored within the record set object and set to its value while the SQLSelect is executing.