ODBC & mySQL

[pierre]

I am testing the ODBC accessor with a simple MySQL database on localhost (LB5 build 351, Windows 10).

That seems to work pretty well.

Although, when retrieving data from NUMERIC or DECIMAL columns, each value comes back with the letter 's' appended to it
That reminds me of an old Run BASIC issue, the '0d' problem.
Does anyone know how to avoid this ?

[Carl Gundel]

Hmm. Sorry about that. I'm sure it'll be easy to fix.

[pierre]

Jul 7, 2020 13:41:16 GMT -5 Carl Gundel said:

Hmm. Sorry about that. I'm sure it'll be easy to fix.

Perhaps in build 352 ?

Thanks in advance.

[pierre]

following up my previous post:

LB5 alpha build 351   -   testing MySQL connection via ODBC
Windows 10 Home Edition - MySQL ODBC connector 8.0

MySQL  'DATE' & 'TIME' data types are not recognized. Trying to read the data, I get the following error:

*************** Message not understood: #asString ***************

In the SQL query we must previously convert this data to a string type, then we are able to read it.

Is this a normal behaviour, or should ODBC do this conversion automatically ?


pierre

[pierre]

Jul 25, 2020 13:32:25 GMT -5 pierre said:

LB5 alpha build 351   -   testing MySQL connection via ODBC
Windows 10 Home Edition - MySQL ODBC connector 8.0

MySQL  'DATE' & 'TIME' data types are not recognized. Trying to read the data, I get the following error:

*************** Message not understood: #asString ***************

In the SQL query we must previously convert this data to a string type, then we are able to read it.

Is this a normal behaviour, or should ODBC do this conversion automatically ?

following up my previous post:

I have investigated some code published in the database programming section of this forum, as well as in several old LB newsletters.

The code is more than 15 years old but it is still working, although perhaps not fully compatible with the latest ODBC versions.

In any case, it appears that LB 4.5.1 is not only able to connect to a MySQL database through ODBC, but also retrieves correctly the ODBC numeric/decimal and date/datetime types.

So LB5 alpha's ODBC interface should definitely be able to do that also, I suppose ?

Let's hope that this will be corrected soon.

pierre

[Carl Gundel]

Aug 6, 2020 8:39:01 GMT -5 pierre said:

Jul 25, 2020 13:32:25 GMT -5 pierre said:

LB5 alpha build 351   -   testing MySQL connection via ODBC
Windows 10 Home Edition - MySQL ODBC connector 8.0

MySQL  'DATE' & 'TIME' data types are not recognized. Trying to read the data, I get the following error:

*************** Message not understood: #asString ***************

In the SQL query we must previously convert this data to a string type, then we are able to read it.

Is this a normal behaviour, or should ODBC do this conversion automatically ?

following up my previous post:

I have investigated some code published in the database programming section of this forum, as well as in several old LB newsletters.

The code is more than 15 years old but it is still working, although perhaps not fully compatible with the latest ODBC versions.

In any case, it appears that LB 4.5.1 is not only able to connect to a MySQL database through ODBC, but also retrieves correctly the ODBC numeric/decimal and date/datetime types.

So LB5 alpha's ODBC interface should definitely be able to do that also, I suppose ?

Let's hope that this will be corrected soon.

pierre

Can you give me instructions to set up the simplest possible way to reproduce the problem on LB5 and MySQL/ODBC?

Please?  Thanks.

[pierre]

here is the setup for LB5 alpha, build 351

You must have MySQL installed on your computer (I have the free Community edition).

In the MySQL command line shell, create a new database:
mysql> create database if not exists odbctest;

in the Windows ODBC Data Source manager, create a new data source:

Driver: the driver you have available, I tested with the MySQL ODBC 8.0 Ansi Driver
Data Source Name : ODBCTest
Port : 3306
User: root
Password: your MySQL password
Database: odbctest


'****************************************************************************************
'first program
'****************************************************************************************

'This example presumes that there is an ODBC source named 'ODBCTest'  (database = odbctest)

 dbEnvironment$ = "ODBCTest"
 dbUser$ = "root"
 dbPassword$ = "--your MySQL password --"
 sqlserverconnect #test, dbEnvironment$, dbUser$, dbPassword$
 print #test
'-----------------------------------------------------------------------------------------------

on error goto [err]

'create a table with a decimal data field

#test execute("CREATE TABLE IF NOT EXISTS products (
 id INT AUTO_INCREMENT PRIMARY KEY,
 description VARCHAR(50),
 price DECIMAL(8,2)
)")

'insert some value into the products table

#test execute("INSERT INTO products VALUES (
   null,
   'my Product',
   27.50
)")

'show the table's content   --> here we see the additional 's' printed to the price

#test execute("SELECT * FROM products")

 while #test hasanswer()
 count = #test rowcount()
 print #test columnnames$()
 for x = 1 to count
   pageText$ = #test nextrow$(",")
   print pageText$            
 next x
 wend


#test disconnect()
print "Done."
end

'---------------------------------------------------
[err]
#test execute("rollback")
print "Database Error - Transaction rolled back"
print Err$
print
#test disconnect()
print "Done."
end

'****************************************************************************************
'second program
'****************************************************************************************

'This example presumes that there is an ODBC source named 'ODBCTest'  (database = odbctest)

 dbEnvironment$ = "ODBCTest"
 dbUser$ = "root"
 dbPassword$ = "--your MySQL password --"
 sqlserverconnect #test, dbEnvironment$, dbUser$, dbPassword$
 print #test
'-----------------------------------------------------------------------------------------------

on error goto [err]

'create a table with a numeric and a datetime data field

#test execute("CREATE TABLE IF NOT EXISTS sales(
   id INT AUTO_INCREMENT PRIMARY KEY,
   product VARCHAR(50),
   quantity NUMERIC(3),
   date DATE
)")

'insert some value into the sales table with null in the date field

#test execute("INSERT INTO sales VALUES (
   null,
   'my Product',
   1,
   null
)")

'quote the preceeding lines and unquote the following lines to enter a valid date value
'#test execute("INSERT INTO sales VALUES (
'    null,
'    'my Product',
'    1,
'    '2020-08-06'
')")

'show the table's content   --> here we see the additional 's' printed to the quantity
'the second time we have the following error:
'Database Error - Transaction rolled back
'Message not understood: #asString        *) see: EDIT below

#test execute("SELECT * FROM sales")


 while #test hasanswer()
 count = #test rowcount()
 print #test columnnames$()
 for x = 1 to count
   pageText$ = #test nextrow$(",")
   print pageText$            
 next x
 wend


#test disconnect()
print "Done."
end

'--------------------------------------------------------------------------------------
[err]
#test execute("rollback")
print "Database Error - Transaction rolled back"
print Err$
print
#test disconnect()
print "Done."
end

'***************************************************************************************

EDIT: via the MySQL command line shell we can verify that the correct date value has been entered in the corresponding field.

So the problem is that LB5 cannot retrieve it.

[pierre]

Thanks, Brandon for having correctly formatted my code examples.

I don't know how to do this.

pierre

[Carl Gundel]

Aug 6, 2020 15:53:41 GMT -5 pierre said:

Thanks, Brandon for having correctly formatted my code examples.

I don't know how to do this.

pierre

You click on the C button (for code) and then paste your code in for one program.  Then click below that to move the cursor out of the code and click the C button again for another program, etc.

Or you can switch to the BBCode tab and use code tags.

[Brandon Parker]

If you edit your post above and then click on "BBCode" tab you will see that the code section is started by "[ code ]" and it ends with "[ /code ]" ... no spaces.

Along with Carl's suggestion, you can highlight all of the text that you want to be inside the code brackets and then hit the "C" button. This will automatically place the code brackets for you.


{:0)

Brandon Parker

[Rod]

Re posting code. The code tags should look like the Quick Reply code tags, they should not look like the insert code here box.

[pierre]

Thank you all for your help !!

I am always kind of struggling with the forum.

Should be better now.

pierre