Alternative to SELECT 1 to check MariaDB Server online status ( using DO 1 generates an error )

[dylim]

Hi Guys,

With MySQL, I use "DO 1" to check for the server's online status. Works like a charm. But MySQL 8.x has caused me so much pain by way of unknown errors I cannot seem to understand and correct.

So, I tried MariaDb, installed the latest GA todate, 11.7.2 and used the latest ODBC 3.2.5 32 bit.

None of the horrific problems came out! What a joy! And so far so good. It's even way way faster!

I have since been testing MariaDb, hoping to use this as my new go to database server.

So far, my only issue now is that MariaDb returns an error when I use "DO 1" in checking for online status. I have to use "SELECT 1" in lieu of this. What I don't like about this is it returns a cursor which I need to erase each and everytime I check for online status.

Any other commands like DO 1 that do not return a cursor?

Thanks in advance! Long live the Fox!

 

[atlopes]

Dylim,

A simple SQL comment, even an empty string, will do. Both do not return any cursor:

--

 

[Chriss Miller]

First of all, thanks for pointing out, that way I don't have to come up with that topic to state that for I'd generally now recommend MariaDB over MySQL.

DO is still a MariaDB command (https://mariadb.com/kb/en/do/) so the detail question just becomes why you can't execute DO 1, specifically with 1. I guess it's an expression Maria DB doesn't consider as valid. Which poses the question why it still can be done by MariaDB, when connecting with a MySQL driver. I actually don't really care and am pragmatic about this and suggest you DO something else, like a variable assignment:

SQLEXEC(h,'Do @dummy=0')

I would also say you could also stick to DO 1, as it doesn't stop the connection working when an error occurs within MariaDB server, nor does it harm MariaDB server, nor does it actually trigger a VFP error and it also proves the command did arrive and was executed, it just triggers an error.

You already have the confirmation the connection is established by SQLCONNECT or SQLSTRINGCONNECT returning a handle number>0. And to actually see, whether the connection works fine is mainly checking whether the driver works fine with VFP. You'll need to create a test suite that covers receiving all data types you need to handle to cover the aspect of possible problems in data type conversions, which is the most common detail problem of ODBC driver mismatch. Something that goes one step in that direction is what I already suggested in your lengthy thread, even though it requires one work area, temporarily:

SQLEXEC(h,'Select Now() as servertime','crsServertime'))

With the benefit of enabling to check whether clients cheat with local time setting.

Besides that, you would only need to establish once, whether it works. You seem to be after verifying whether the returned handle number actually works for this session. Well, if it doesn't that will be apparent with any other first query, when this is a permanent recurring issue, it would point out something is quirky with the driver or the network, but it wouldn't also necessarily proof the driver is working nor that it is the culprit, as the reasons and sources of problems in a complex system like a client/server with driver, network and server involvement can mean any component can temporarily fail.

 

[Chriss Miller]

Atlopes, our posts crossed. I checked executing nothing or a comment. Most variations of that return -1, just one version works: SQLEXEC(h,'/* */') While, accoring to https://mariadb.com/kb/en/comment-syntax/ comments could also be --, as you suggest, and #.

Besides SELECT Now(), which may not be your cup of tea, dylim, you could make the first thing a verification of database consistency of some level, like checking whether all expected databases and tables exist with execution of SQLEXEC(h,'Show Databases','crsDBs'), SQLTABLES(), and SQLCOLUMNS(). I just would warn to go into the level of all columns, as you'd need to maintain the test with every DB change and you gain more control about data consistency by defining referential integrity and other constraints that the db server maintains and checks, anyway. You could also establish your own version table and verify to be connected to the right database version with a query like SELECT * from dbversion.

 

[atlopes]

You're right, Chriss, MariaDB is a bit more restrictive regarding comment syntax and minimal SQL statements, and I should have tested before.

Your proposal

/* */

or a more precise -- based comment

--

(hard to see, but equivalent to "-- " + 0h0d0a) will check ODBC connectivity with the server without generating a cursor at the client side.

 

[Chriss Miller]

Interestingly, an empty comment /**/ also does not work. To get # to work it can't be the first character, you have to precede it with a space, and like -- it only works with a CR LF.

However, I stick to what I said earlier, such empty non queries working doesn't prove the connection is actually allowing to query a database, that you want to prevent a workarea to be used has to come from legacy VFP only providing a few workareas. Whatever, I would personally only consider having a result of any type to be verifying the connection to work. If you'd like to verify the conection actually allows running a batch of commands and returning multiple results, you'll need to even allow multiple workareas to be used temporarily. So if you do this to verify you got the connection with the features asked for, like option flag 2^26=67108864 - allows to send multiple statements in one query, then you'd even need to allow two wokareas to be populated.

I understand you want to verify the connection only, find out whether it's still alive on the server. Look at AUTO_RECONNECT to not worry about that. Specifically right after the SQLSTRINGCONNNECT a check like executing a comment won't tell you anything you didn't know. When you do this as a kind of ping, also to assure the server you're still there and prevent the connection to become idle, look into https://mariadb.com/kb/en/transaction-timeouts/.

idle_transaction_timeout​

• Description: Time in seconds that the server waits for idle transactions before killing the connection. If set to 0, the default, connections are never killed. See also idle_readonly_transaction_timeout, idle_write_transaction_timeout and Transaction Timeouts.
• Scope: Global, Session
• Dynamic: Yes
• Data Type: numeric
• Default Value: 0
• Range: 0 to 31536000