Session Database Connection

In my web app I have a session based database connection that I use for each session that is opened.

If I have a loop that reads data from the DB and in the look I have the following:

DoMyMethod(data.field("myField1").StringValue)

If in the DoMyMethod I what to use the session database connection to collect other information is this going to screw up the data connection in the calling loop or is each sql select request ring-fenced by Xojo and/or the DB?

The only think you might have to worry about is nesting your transactions. Meaning that if you have to balance your Begin and Commit/Rollback statements. Otherwise it’s pretty safe AFAIK. But generally speaking, you can do as many SQL Selects and Executes as you want on a single connection without them walking all over each other.

I’ve never had an issue with it. Are you seeing something different?

Yes I am getting an object nil error when the sql select is called within the method. Move the place where I call the method to after I have close the recordset and then call the method works fine so it would appear that the child is walking over the parent recordset.

I am wondering if this is an issue with MS SQL or the MS SQL plugin.

Now THAT I would believe. My experience with it has been less than stellar.

Sadly the DB I have to use is MS SQL as that is what the clients whole systems work on (yack!). I did try and use the MBS SQL plugin over the weekend but it seemed to slow things down a lot. I am sure this is probably something I am doing rather than the plugin but I think I need to go and relook at it again.

Do you use the RealSQLdatabase? If so have you ever seen a situation where a web app that uses a session database connection appears to start return NIL for SELECTs even though the SQL select is perfect and works fine if I restart the app? I am not getting any DB errors returned.

Just a thought about the session SQL being overwritten. I refer to database connections in the session directly using the session.myDB.??? could this be the issues? Should I do something like whenever I am going to use the session database?

dim myDB as database = Session.myDB

That code wouldn’t make a difference as it just assigns a reference to the same connection to a different variable.

I did think that would be the case. Its a very odd issue.

No. Almost all of our web apps are using SQLite and we’re not having any issues with the connections - even with dozens of simultaneous users. I suspect that you’re doing something funky but it’s hard to diagnose from afar.

See if Christian’s database plugin has MS SQL support. His stuff is built on a much better exercised code base than the Xojo plugins. That’s not a slam at anyone, just how things are circe 2014.

Yeah I will have to go back to the MBS SQL plugin, it does support MS SQL and I tried it at the weekend but it slowed down my project but I think this is probably down to me and not the plugin.

What I recall from a conversation with Jonathan Monroe (http://www.actualtech.com), who developed and/or contributed to one or more of the Xojo database plugins, is that depending on how the database plugin was developed, it may require a unique database connection for each recordset and potentially each SQLExecute. The ODBC plugin is one where a unique database connection per recordset was highly recommended.

Per Bob’s replies, the SQLite plugin works well with just one database connection and multiple recordsets.

Although we have decades of experience with MS SQL Server, very little is with the Xojo MS SQL Server plugin and where it’s used we have a unique database connection for each recordset. It would be nice to have the author(s) of the MS SQL Server plugin chime in to let us all know how best to use it.

Regarding anything that Christian (http://www.monkeybreadsoftware.de) produces, including his database plugins, they’re highly recommended for the quality of the software and exceptional support.

I think I am going to use the MBS plugin as you say, good range of products and great support. The only reason why I havent is that something went weird and slow when I switched over but I am sure this is down to me and not the plugin. If their is an issue I know Christian will look at it quickly but need to do some digging first to see what is happening in my app that is giving the odd results. Also the thing with the MBS SQL plugin is that it is one big beast and the manual is massive, the demo provided for the MSSQL side is a bit weak and I was using the straight replacement for the Xojo connection with a MBS connection to the database which may not be ideal but what I cant seem to get from anyone is what the real-world benefits are of the MBS connector over the MBS patch to the Xojo connector. I know it gives more options but to be honest “so what”, I am not being rude just I dont really know if I need the options or not because their isnt (as far as I can find) a comparison list between the two ways of connecting.

I’ve posted my perspective on this a few times. First, the MBS database plugin is not a patch. It’s a different API and a different base. That base is a library called SQLAPI and is used by a plethora of mainstream projects. It is supported by a company whose product is the library and API. Christian licenses the library on his customers’ behalf. Xojo’s Database model is its own, going clear back to the beginning. It has roots and assumptions built in with the intention of making relational database servers “work just like 4D”, as Geoff explained to me 13 years ago. A problem is that RDBMSs don’t work just like 4D, as transactions and locking quickly become problematic when implementing a plugin for the Xojo API. There used to be some benefit for trying, with control binding to result sets. But that’s been removed. There seems to be a general trend with the Xojo plugins to get simpler and not try to support all the stuff needed for control bindings, etc. But still, the bridges between vendor libraries and the Xojo model are written by Xojo and used only in Xojo.

Obviously, the database connectivity is of strategic importance to Xojo and is a distinct part of their licensing structure. In practice right now, it is proving to be problematic. Judging from fixed issues every release, they seem to get a lot of ongoing attention, but still quickly prove problematic with serious use. On the other hand, Xojo developers are comfortable with, and often expect, the Xojo database API. It’s a really sticky problem for Xojo users.

  1. If you’re getting a nil recordset, that means you’re getting a database error.
  2. With MS SQL, that error is probably something like “there is already an active result set”.
  3. MS SQL requires a separate connection for each parallel request.

I don’t know if this is due to MS SQL itself (I suspect it is) or if it is due to Xojo. I know that a long time ago, I baked code into my database classes that automatically create a separate connection for each instance if the database is MS SQL. I haven’t revisited this code in quite some time, so this info could be outdated.

I have now converted my web app to use the MBS SQL plugin for connecting to a MS SQL 2012 server with the following observations:

  • Dont use the OLEDB method, use the ODBC as recommended by SQLAPI. The OLEDB and the internal DB-Library are a lot slower than the ODBC and also a lot slower than the Xojo SQL connector.

  • Use the SQLConnectionMBS rather than the SQLDatabaseMBS as you can then use the SQLSelectAsRecordSetMT function, the MT I think stands for multi-task or multi-thread, whichever it is, it makes a big difference in the UI refreshing in web apps especially with listbox’s. I havent tied the SQLExecuteMT yet but I expect to see the same sort of results.

  • On first look the SQLConnectionMBS looks complex and to be honest I kept away from it but then when you look at the example and read the docs and look at the SQLAPI website it becomes clear that it is actually really easy to use and the changes to the Xojo code needed is not huge.

  • Its worth noting that you do lose the InsertRecord method when using the SQLConnectionMBS but you get the advantage of using the SQLExecuteMT or the straight SQLExecute.

  • Appears to work with all MS SQL data types without causing Xojo to hard crash especially with NVARCHAR(MAX) and VARCHAR(MAX).

If anyone is considering using the MBS SQL plugin then I would recommend it, I am now going to change all my projects to use it.

  • I prefer OLEDB as I don’t need drivers.
  • SQLDatabaseMBS also has MT functions.
  • Plugin sets recordsets to be scrollable. if you don’t need that you can set Option(“Scrollable”) = “false” for the SQLCommandMBS object.

Thanks for pointing out the MT fucntion in SQLDatabaseMBS, did know that. My understanding is that OLEDB is deprecated under Windows.

What does that actually mean? When would you have a situation where you would not want scrollable?

it’s a waste of resources if you don’t do a select.
Some people had trouble with it.