SQLDatabaseMBS Issue

Add-Ons
1

Greetings,

I’m having a little bit of an issue getting the SQLCommandMBS class to return a rowset. It keeps throwing a nil object exception “SQLDatabaseMBS not initialized” even though I’m still using the same instantiation and connection string code in other modules.

I set a breakpoint and poked around and near as I can tell the SQLConnectionMBS does get connected, the SQLCommandMBS params are getting passed through as a dictionary just fine and the execute function does seem to return an isResultSet flag set to true…so I’m not sure what I’m doing wrong.

Dev Platform: Windows Server 2016
Xojo: 2019 R3.1
Database: 10.3.20-MariaDB-0ubuntu0.19.04.1
MBS SQL Plugin: Latest.

Code:

[code]Var tmpDbConn As SQLConnectionMBS
Var tmpDbCmd As SQLCommandMBS

Try
tmpDbConn = New SQLConnectionMBS
tmpDbCmd = New SQLCommandMBS

tmpDbConn.SetFileOption tmpDbConn.kOptionLibraryMySQL, SpecialFolder.UserHome.Child(“libmariadb.dll”)
tmpDbConn.Connect(“xxxxxxxxxxxxxxxxxxxxxxx,3306@xxxxxxxxxxxxx”, “xxxxxxxxxxxxxxxx”, “xxxxxxxxxxxxxxx”, SQLConnectionMBS.kMySQLClient)

tmpDbCmd.Connection = tmpDbConn

tmpDbCmd.setCommandText(“SELECT id, last_name, first_name, middle_initial, member_id, city_township, state_province_region FROM members WHERE last_name LIKE :1 AND first_name LIKE :2 ORDER BY last_name ASC, first_name ASC LIMIT 25”)
tmpDbCmd.Param(1).setAsString("’%" + txt_last_name + “%’”)
tmpDbCmd.Param(2).setAsString("’%" + txt_first_name + “%’”)

tmpDbCmd.Execute
//tmpDbConn.Commit

Return tmpDbCmd.AsRowSet

Catch tmpSQLErr As SQLErrorExceptionMBS

Try
// On error rollback changes
If tmpDbConn <> Nil Then
tmpDbConn.rollback
End If
Catch x As SQLErrorExceptionMBS
// Ignore
End Try

// Show error message
MsgBox tmpSQLErr.message

End Try[/code]

Any help would be appreciated.

Thanks!

-Justin-

Link to screenshots
https://imgur.com/a/WXhszRU

2

Did some more poking around…

I narrowed my particular issue down to the function SQLCommandMBS.AsRowSet as RowSet

According to the documentation:

[quote]Function: Returns a RowSet using the command to query fields.
Notes:
You can use normal RowSet functions to walk through fields and they simply control the command object.
This is for convenience like passing RowSet to other functions in Xojo.

For this method to work, you need to have somewhere a property with SQLDatabaseMBS so Xojo includes our SQLDatabase plugin which provides the RowSet functionality.

The RowSet may not have a valid RecordCount or have working movefirst/movelast/moveprev methods unless the underlaying database supports those and Scrollable result sets is enabled/supported.

Requires Xojo 2019r2 or newer.[/quote]

Either I’m using it incorrectly or it’s not implemented for MySQL / Mariadb. Doing a .FetchFirst will bring in “Young” and “Justin”
based on the wildcard search…so the query and command parameters seem to be working correctly.

Not sure if this is a bug or not but will probably just code around it.

-Justin-

[code]sql = “SELECT id, last_name, first_name, middle_initial, member_id, city_township, state_province_region FROM members WHERE last_name LIKE :1 AND first_name LIKE :2 ORDER BY last_name ASC, first_name ASC LIMIT 25”

tmpDbCmd.setCommandText(sql)

tmpDbCmd.Param(1).setAsString("%" + txt_last_name + “%”)
tmpDbCmd.Param(2).setAsString("%" + txt_first_name + “%”)

tmpDbCmd.Execute
//tmpDbConn.Commit

If tmpDbCmd.FetchFirst <> False Then
MessageBox tmpDbCmd.Field(“last_name”).asStringValue
MessageBox tmpDbCmd.Field(“first_name”).asStringValue

Return tmpDbCmd.AsRowSet
End If[/code]

3

Did you add a property with data type SQLDatabaseMBS, so the compiler does include the SQLDatabaseMBS class which is needed for RowSet?

4

Hi,

Thanks for answering my post.

Yes sir, in my app.open I have the following taken from the “sqite fetch values with rowset” example. Is this correct?

// MBS SQL License SQLGlobalsMBS.setLicenseCode "xxxxxxx", xxxxxxx, xxxxxxx, xxxxxxx dim trash_db as new SQLDatabaseMBS

5

So do you get an error or exception now?

Ah, maybe remove FetchFirst and put in a isResultSet call.

6

Yes, the exception stops getting thrown with the include.

The FetchFirst was just a test to see if my query, command set and parameters were formatted and getting passed properly.
They are pulling the proper values into the message box pop ups. Good to go for Last Name: Young First Name: Justin

For a quick test, I added…and it pops true.

messagebox tmpDbCmd.isResultSet.ToString

So, the SQLCommandMBS class seems to be indicating that there’s a result set in there, it’s just that the .AsRowSet method never seems to make the translation and always returns nil / null.

Return tmpDbCmd.AsRowSet

^^^^ Always seems to return nil.

I just double checked to make sure and yes, my shared method return type is definitely RowSet. Is that native type correct or does the return type need to be something else?

7

Ok, looks I got it solved.

Order of operations is important here.
If you do any kind of Fetch command on the SQLCommandMBS instantiation it screws up the ability pull out a RowSet.

This works:

messagebox tmpDbCmd.isResultSet.ToString Return tmpDbCmd.AsRowSet

This fails:

If tmpDbCmd.FetchFirst <> False  Then
MessageBox tmpDbCmd.Field("last_name").asStringValue
MessageBox tmpDbCmd.Field("first_name").asStringValue
Return tmpDbCmd.AsRowSet
End If

So yes, you’re correct.

  1. Check .isResultSet for true
  2. Return RowSet

Thanks for the assist!

-Justin-

8

Welcome. Enjoy!