MBS SQL x XOJO Native

Excepting the SQLite, the DB plugins seems not receiving much love for some time. Never tried the MBS version. Heard something about stability. Is there some clear line of advantages? What about disadvantages?

MBS Plugin is an alternative.
Website lists a few points.

Simply try it and compare.

Some people can read this as “well… no advantages”.

The website is a bit confuse. Some points aren’t clear.

Comes with examples?
Hoped that users could say good x bad points. I like to be convinced by arguments before trying things. :wink:

of course it has tons of examples.

And of course it has negative points like not being included for free in Xojo or that you have to deal with different set of documentation.

Which point is not clear? Maybe I could change website.

People wants to find key points and comparison points in a glance like:

Does it substitute the native ones transparently?
Native reports works with with this source?
Does it permit the full set of recordset navigation movements? (first, last, next, previous)
Transaction, isolation, server side recordset?
Etc.

Those kind of things organized in a table easy to read and not in a FAQ like style.

I’ll get the pack and read the examples. Maybe it makes things clear. :wink:

[quote=117083:@Rick Araujo]People wants to find key points and comparison points in a glance like:

Does it substitute the native ones transparently?[/quote]
No - it uses a different API

The built in report engine - no
You’d have to wrap results sets into one of the API’s the built in report engine can use

[quote=117083:@Rick Araujo]Does it permit the full set of recordset navigation movements? (first, last, next, previous)
Transaction, isolation, server side recordset?
[/quote]
Many of these will vary from DB to DB
MySQL 5 does not directly support server side record sets as far as I know
Some have specific syntax for creating a server side recordset - “Declare Cursor”, “Create Cursor” etc
How you access them may vary from DB engine to DB engine
But you can often do it JUST using SQLSelect or SQLExceute in the DB api

Transaction isolation is, sadly, similar where you probably have to look at each vendors reference to see how to get the isolation levels you need.

But as long as each can access the SQL the real differences will come not in the basic implementation of accessing the DML but in the behavior of the plugin in use (blocking, threading, etc)

You can use our SQL Plugin with the built in Xojo database class (SQLDatabaseMBS) or completely with plugin classes only (SQLConnectionMBS).
For native reports, our plugin can provide a RecordSet to pass to the report engine.
We have all the move commands and enable them for you by default. This would also allow scrolling with MySQL.
IsolationLevel and AutoCommit levels can be set automatically. You can of course simply start a transaction via SQL commands.

More confusion. In the examples read me at /MBS SQL Plugin/Examples/SQL/SQL Plugin Readme.rtf :

For the RecordSet class:

Properties:

BOF: Not implemented.
FieldCount: Works.
EOF: Implemented. Set to true if MoveNext fails.
RecordCount: Always -1 as we don’t know the number of records.

Methods:

Close: Works
MoveFirst: Not implemented.
ColumnType: Works
MoveLast: Not implemented.
DeleteRecord: Not implemented. Gives Error -1.
MoveNext: Works
Edit: Not implemented. Gives Error -1.
MovePrevious: Not implemented.
Field: Works
IdxField: Works
Update: Not implemented. Gives Error -1.

Outdated docs. MovieFirst, MovePrev and MoeFirst got implemented later. So they should work with current plugins.

Well, fix the docs so. :wink:

I fixed it here already.

Rick, I’m answering this because I respect you and the question.

The “plus side” of the Xojo Database API and plugins is that they are the Xojo native way. That’s not meant as any kind of a compliment, just a statement about inertia. I have written 2 such plugins from scratch more than a decade ago, and I feel like I have a good sense of why the API and plugin system continues to have ongoing issues even now.

MBS’s database classes use a very popular and modern library and API under the hood. So you get a much higher level of built in reliability. It would not surprise me in the least if lifetime investment in and sales of that API exceeded those of Xojo’s Database by a factor of 100.

I mention these because over the past 18 month, I have billed many thousand dollars total to various client on the simple basic question of MBS or “Xojo native” database connectors. The question has come up after a crash in one of the native plugins. These crashes are very expensive in time to isolate without plugin source and usually don’t have an acceptable work-around other than waiting to see if it might be fixed next release.

My advice to those connecting to databases other than SQLite… Just go with MBS from the start. My advice to those who, because of inertia or whatever, go with the Xojo plugins and run into a crashing issue with them. Just switch to MBS and avoid the hassles and uncertainties of trying to get the problem fixed. My advice to Xojo, were they to ask me… Deprecate the current API, buy Christian’s plugin, license the underlying library. You’ll make everyone happier almost immediately.

Back to lurking.

Hello Brad. Thank you for your thoughts and expertise on this subject. The respect is mutual.

I agree whole-heartedly with Brad. i have tried to use the xojo database api’s and they are ok up to a point.
the problem is that you reach that point quickly. because at first i only wanted windows,

I then tried to use oledb, which again is ok
but is awkward, so i used Alain Bailleuls’ oledb wrapper classes, which made life easier, but still windows only.

I then decided to get the data from the database and create an in-memory sqllite database, which works quite well, but its not ideal.

i’ve only just started with MBS SQL, but in about 20 mins i had a X-plat program running against MSSQL Server
that didn’t require anything other than the MBS plugin and the compiled freetds library.

it also reports the field types and precision/scale correctly, which is not available using the builtin stuff (well type is, but not precision /scale).

and you can do fetchnext/fextprior as well, which makes a massive difference. forward -only is ok, butif you are reporting to users
you will want to pre-process the data to total/subtotal etcetera

Considering the cost of the database license over the desktop licence and the cost of the MBS plugin, XOJO should
really consider doing a deal with Christian.

btw, i’m not saying the MBS plugin is fault free, because i’m not far enough in with it. but to be honest, its already better than the Xojo stuff in my eyes.

Thank you both for the comments :slight_smile:

Not that Brad’s advice needs any further underscore, but I’ll add yet another very recent incident (as in, just this morning) that makes me kick myself in the butt once again (I’m running out of fresh spots to kick there) for not having bit the bullet and gotten the MBS SQL plug-in to begin with for this large business system project that I’ve been working on for over 8 months now (I own just about every other MBS plug made … just not the one for SQL database … corollary to Murphy’s Law, I’m sure) . Besides the problem I ran into sometime back trying to use prepared statements with my MS SQL database and the native Xojo SQL DB plug, I have now found myself in a pickle once again because I need to set a field in one of the DB tables to NVARCHAR(MAX) to accomplish a required program task, and for all of you that have tried that, you know the result … CRASH!

First chance I get, I’m going to change that out to the MBS plug. I kinda get the feeling from what Brad says that even if Xojo “fixed” theirs, the MBS plug would still be equal to or better than the Xojo version. So, why wait?

Christian [quote=117116:@Rick Araujo]11:05am Well, fix the docs so. ;)[/quote]

:slight_smile:

Rather than regurgitating whats already been said, I’ll simply say MBS SQL is my preferred choice, I don’t bother with the native plugins. They just happen to be part of my Pro license rather than something I actively use.

The native plugins need some love…

Not to sway one side or the other, because I only have experience w/the native plugins… but you guys make me wonder what I’m missing. I’ve been using native plugins for 5 years now in a business setting w/o any issue (Windows/OS X/Linux). Now, 99% of that is PostgreSQL.

well, hang on to your hat…

bearing in mind i’m using MSSQL lets say you want to have a listbox subclass that you can pass a recordset
when the user enters a cell to edit it, its either a straight edit or a lookup

given that a lookup (lets say,not dates, customers, plu’s etc)
the first level of problem starts with throwing up a list of products. the user wants to type and have the list auto select.
this is painfully slow and network intensive when you need to do roundtrips to the server for every keypress
much faster (and the speed is consistent ) when the records are in memory.

the user can wait for the program to load,(lets assume its a multi-site client over a WAN) but when typing in information things must happen in a timely fashion.
movefirst,moveprior,movelast,movenext facilitate this.

then, lets say that the listbox subclass allows edits. when the user clicks in a cell, i need to have some validation
the first level is that what they type is valid to be stored in the schema. without knowing the type,scale,precision i cant do that.
automatically.

xojo tells you it a numeric, but that covers 12.99, 129.99, 0.99999999999 when you do an update/insert you have another round trip for the error. if i know already, i can validate onkeypress.

i want to have a listbox i can put on a window, pass it a recordset and have all this done for me.
i also want to do this with custom controls for a form builder.

then i’ve covered the main bases. i can then raise a validate event knowing that at the very least i’m schema safe, validation is now just business logic, which i then want to farm off to xojoscript for the site admin to write.

hope that makes sense.