|
Post by pierre on Jul 7, 2020 12:26:56 GMT -5
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 ?
|
|
|
Post by Carl Gundel on Jul 7, 2020 13:41:16 GMT -5
Hmm. Sorry about that. I'm sure it'll be easy to fix.
|
|
|
Post by pierre on Jul 7, 2020 14:48:26 GMT -5
Hmm. Sorry about that. I'm sure it'll be easy to fix. Perhaps in build 352 ?
Thanks in advance.
|
|
|
Post by pierre on Jul 25, 2020 13:32:25 GMT -5
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
|
|
|
Post by pierre on Aug 6, 2020 8:39:01 GMT -5
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
|
|
|
Post by Carl Gundel on Aug 6, 2020 9:36:38 GMT -5
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.
|
|
|
Post by pierre on Aug 6, 2020 12:00:09 GMT -5
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.
|
|
|
Post by pierre on Aug 6, 2020 15:53:41 GMT -5
Thanks, Brandon for having correctly formatted my code examples. I don't know how to do this.
pierre
|
|
|
Post by Carl Gundel on Aug 6, 2020 17:35:19 GMT -5
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.
|
|
|
Post by Brandon Parker on Aug 6, 2020 18:38:28 GMT -5
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
|
|
|
Post by Rod on Aug 7, 2020 3:07:42 GMT -5
Re posting code. The code tags should look like the Quick Reply code tags, they should not look like the insert code here box.
|
|
|
Post by pierre on Aug 7, 2020 3:46:46 GMT -5
Thank you all for your help !! I am always kind of struggling with the forum. Should be better now.
pierre
|
|