PureBasic Forums - English

http://www.purebasic.com
https://www.purebasic.fr/english/

MySQL problem | SOLVED

https://www.purebasic.fr/english/viewtopic.php?t=77052

Page 2 of 4

Re: MySQL problem

Posted: Mon Apr 12, 2021 8:45 pm
by Marc56us
Hi,

This is a small snipet, as thanos' test, ODBC works for me too and not the direct connection.
(Change 'Use' line and 'OpenDatabase' line to test the two version)

Code: Select all

UseMySQLDatabase()
; UseODBCDatabase()

Procedure CheckDatabaseUpdate(Database, Query$)
    Protected Result
    
    Result = DatabaseUpdate(Database, Query$)
    Debug "Query : " + Query$ 
    Debug "Rows  : " + AffectedDatabaseRows(Database)
    
    If Result = 0
        Debug "ERROR : >>> " + DatabaseError() + " <<<"
        End
    EndIf
    Debug ""
    
    ProcedureReturn Result
EndProcedure

; Direct: 'Commands out of sync; you can't run this command now'
hDatabase = OpenDatabase(#PB_Any, "host=localhost port=3306 dbname=PB_Test", "test", "test")

; ODBC (MariaDB ODBC 3.1)
; https://downloads.mariadb.com/Connectors/odbc/connector-odbc-3.1.11/
; hDatabase = OpenDatabase(#PB_Any, "PB_test", "test", "test")

If (hDatabase)
    Debug "Connected to MySQL"+ #CRLF$

    CheckDatabaseUpdate(hDatabase, "SET autocommit = OFF;")
    CheckDatabaseUpdate(hDatabase, "BEGIN;")

    ;     ForEach Values2Insert()
    ;         CheckDatabaseUpdate(hDatabase, Values2Insert())
    ;         i+1
    ;     Next
    
    CheckDatabaseUpdate(hDatabase, "COMMIT;")
    CloseDatabase(hDatabase)
EndIf
ODBC

Code: Select all

Connected to MySQL

Query : SET autocommit = OFF;
Rows  : 0

Query : BEGIN;
Rows  : 0

Query : COMMIT;
Rows  : 0
Direct

Code: Select all

Connected to MySQL

Query : SET autocommit = OFF;
Rows  : 0

Query : BEGIN;
Rows  : 0
ERROR : >>> Commands out of sync; you can't run this command now <<<
ODBC driver installed and connected to the database with default options.

I tried to use AffectedDatabaseRows(Database) to try to clear the buffer, but it has no effect.
According to several opinions it is the DLL that has a problem.
I'll keep on looking (even if I don't use MySQL anymore, but PostgreSQL)

8)

Re: MySQL problem

Posted: Thu Apr 15, 2021 6:53 am
by thanos
I also checked with different system configurations (XP Home, Pb 5.71, several version of libmariadb and mysqllib) with the same bad results
Regards,

Re: MySQL problem

Posted: Mon Apr 19, 2021 10:55 am
by thanos
Is it necessary to open a thread at bugs section?
Regards,

Re: MySQL problem

Posted: Mon Apr 19, 2021 1:11 pm
by Marc56us
thanos wrote: Mon Apr 19, 2021 10:55 am Is it necessary to open a thread at bugs section?
Regards,
According to what we can read on the Internet, this problem comes from the DLL, so PB team can't do anything.

Use ODBC or if you have not yet started developing the application, change the DBMS (i.e PostfreSQL is a bit more difficult to configure, but much more consistent and respects the SQL standards and now very well documented)
:wink:

Re: MySQL problem

Posted: Mon Apr 19, 2021 1:32 pm
by thanos
Thanks a lot Marc56us!
I am always using PostgreSQL (when I left Firebird) as my DBMS which I have some experience with them. But in this particular project I have to use MySQL.
According to what we can read on the Internet, this problem comes from the DLL, so PB team can't do anything.
It sounds strange. With using the same dll I wrote a small program with Harbour and the job was done.
So, we have to wait for a new version of MariaDB and hope to fix the problem?
Regards,

Re: MySQL problem

Posted: Tue Apr 20, 2021 3:29 pm
by wayne-c
I also have this problem, always get these "Commands out of sync; you can't run this command now" errors. Mostly when trying to lock tables, but also with other commands. Via ODBC the exact same PureBasic code and same MariaDB database works without any errors.

But with the UseMySQLDatabase("libmariadb.dll") not.

It's very frustrating to have to install an ODBC driver to access a local MariaDB database :cry:

Re: MySQL problem

Posted: Tue Apr 20, 2021 4:22 pm
by infratec
You can try the former solution to access MySQL:

https://www.purebasic.fr/english/viewto ... 12&t=56390

If the fault is still there, than it is a dll problem, else a PB problem.

Re: MySQL problem

Posted: Tue Apr 20, 2021 5:46 pm
by the.weavster
thanos wrote: Sat Apr 10, 2021 5:37 pm The INSERT statements isn't something special.
Here are the first five rows:

Code: Select all

INSERT INTO orders (AA, HMEROMHNIA, WRA, ORDER_ID, ITEMS, AMOUNT, DISCOUNT, AMOUNT_TOTAL, AMOUNT_PAID, AMOUNT_CANCELED, USER_ID, KWD_PROM_PEL, TABLE_ID, ADDITIONAL_ORDER, COMMENTS, REMARKS, USER_COMMENTS, ASSIGN_TO, STATUS, REC_STAMP, AUTHORISED_BY, SXOLIA, ORDER_NO, UNIQUE_ID, UPDATED) VALUES (1, '2020-04-30', '06:10:15', '20200430_061015_025579062-00001-1', 1.0, 1.6, 0.0, 1.6, 0.0, 0.0, '00001-1', '1100-000-021-3', '', '', 'ΜΕ|ΜΕΤΡΙΟ|', '', '', '00008-1', '01', 1588227015, '', '', 1, '78a13aee-7e3e-11ea-9347-806e6f6e6963}202004300610150255790621303', '2020-04-30, 06:10:15.25579062');
INSERT INTO orders (AA, HMEROMHNIA, WRA, ORDER_ID, ITEMS, AMOUNT, DISCOUNT, AMOUNT_TOTAL, AMOUNT_PAID, AMOUNT_CANCELED, USER_ID, KWD_PROM_PEL, TABLE_ID, ADDITIONAL_ORDER, COMMENTS, REMARKS, USER_COMMENTS, ASSIGN_TO, STATUS, REC_STAMP, AUTHORISED_BY, SXOLIA, ORDER_NO, UNIQUE_ID, UPDATED) VALUES (2, '2020-04-30', '06:21:19', '20200430_062119_026243812-00001-1', 1.0, 1.6, 0.0, 1.6, 0.0, 0.0, '00001-1', '1300-000-070-4', '', '', 'ΜΕΤΡΙΟ|', '', '', '00008-1', '01', 1588227679, '', '', 2, '78a13aee-7e3e-11ea-9347-806e6f6e6963}202004300621190262438123782', '2020-04-30, 06:21:19.26243812');
INSERT INTO orders (AA, HMEROMHNIA, WRA, ORDER_ID, ITEMS, AMOUNT, DISCOUNT, AMOUNT_TOTAL, AMOUNT_PAID, AMOUNT_CANCELED, USER_ID, KWD_PROM_PEL, TABLE_ID, ADDITIONAL_ORDER, COMMENTS, REMARKS, USER_COMMENTS, ASSIGN_TO, STATUS, REC_STAMP, AUTHORISED_BY, SXOLIA, ORDER_NO, UNIQUE_ID, UPDATED) VALUES (3, '2020-04-30', '06:25:36', '20200430_062536_026500796-00001-1', 2.0, 3.2, 0.0, 3.2, 0.0, 0.0, '00001-1', '1300-000-070-4', '', '', 'ΜΕΤΡΙΟ|', '', '', '00008-1', '01', 1588227936, '', '', 3, '78a13aee-7e3e-11ea-9347-806e6f6e6963}202004300625360265007963007', '2020-04-30, 06:25:36.26500796');
INSERT INTO orders (AA, HMEROMHNIA, WRA, ORDER_ID, ITEMS, AMOUNT, DISCOUNT, AMOUNT_TOTAL, AMOUNT_PAID, AMOUNT_CANCELED, USER_ID, KWD_PROM_PEL, TABLE_ID, ADDITIONAL_ORDER, COMMENTS, REMARKS, USER_COMMENTS, ASSIGN_TO, STATUS, REC_STAMP, AUTHORISED_BY, SXOLIA, ORDER_NO, UNIQUE_ID, UPDATED) VALUES (4, '2020-04-30', '06:26:56', '20200430_062656_026580312-00001-1', 1.0, 1.9, 0.0, 1.9, 0.0, 0.0, '00001-1', '1300-000-105-9', '', '', 'ΓΛΥΚΟΣ|', '', '', '00008-1', '01', 1588228016, '', '', 4, '78a13aee-7e3e-11ea-9347-806e6f6e6963}202004300626560265803128479', '2020-04-30, 06:26:56.26580312');
INSERT INTO orders (AA, HMEROMHNIA, WRA, ORDER_ID, ITEMS, AMOUNT, DISCOUNT, AMOUNT_TOTAL, AMOUNT_PAID, AMOUNT_CANCELED, USER_ID, KWD_PROM_PEL, TABLE_ID, ADDITIONAL_ORDER, COMMENTS, REMARKS, USER_COMMENTS, ASSIGN_TO, STATUS, REC_STAMP, AUTHORISED_BY, SXOLIA, ORDER_NO, UNIQUE_ID, UPDATED) VALUES (5, '2020-04-30', '06:31:48', '20200430_063148_026872718-00001-1', 2.0, 3.2, 0.0, 3.2, 0.0, 0.0, '00001-1', '1300-000-168-3', '', '', '', '', '', '00008-1', '01', 1588228308, '', '', 5, '78a13aee-7e3e-11ea-9347-806e6f6e6963}202004300631480268727183471', '2020-04-30, 06:31:48.26872718');
I saw this on stackoverflow and would be interested to know if this makes a difference to the problem you're encountering:

"Commands out of sync; you can't run this command now"

"This error results when you terminate your query with a semicolon delimiter inside the application. While it is required to terminate a query with a semicolon delimiter when executing it from the command line or in the query browser, remove the delimiter from the query inside your application."

Re: MySQL problem

Posted: Wed Apr 21, 2021 7:32 am
by wayne-c
infratec wrote: Tue Apr 20, 2021 4:22 pm You can try the former solution to access MySQL:

https://www.purebasic.fr/english/viewto ... 12&t=56390

If the fault is still there, than it is a dll problem, else a PB problem.
The first short test was running fine, without any errors - does this mean there's a bug in PureBasic's MySQL Library?

Re: MySQL problem

Posted: Wed Apr 21, 2021 7:42 am
by thanos
the.weavster wrote: Tue Apr 20, 2021 5:46 pm
thanos wrote: Sat Apr 10, 2021 5:37 pm The INSERT statements isn't something special.
Here are the first five rows:

Code: Select all

INSERT INTO orders (AA, HMEROMHNIA, WRA, ORDER_ID, ITEMS, AMOUNT, DISCOUNT, AMOUNT_TOTAL, AMOUNT_PAID, AMOUNT_CANCELED, USER_ID, KWD_PROM_PEL, TABLE_ID, ADDITIONAL_ORDER, COMMENTS, REMARKS, USER_COMMENTS, ASSIGN_TO, STATUS, REC_STAMP, AUTHORISED_BY, SXOLIA, ORDER_NO, UNIQUE_ID, UPDATED) VALUES (1, '2020-04-30', '06:10:15', '20200430_061015_025579062-00001-1', 1.0, 1.6, 0.0, 1.6, 0.0, 0.0, '00001-1', '1100-000-021-3', '', '', 'ΜΕ|ΜΕΤΡΙΟ|', '', '', '00008-1', '01', 1588227015, '', '', 1, '78a13aee-7e3e-11ea-9347-806e6f6e6963}202004300610150255790621303', '2020-04-30, 06:10:15.25579062');
INSERT INTO orders (AA, HMEROMHNIA, WRA, ORDER_ID, ITEMS, AMOUNT, DISCOUNT, AMOUNT_TOTAL, AMOUNT_PAID, AMOUNT_CANCELED, USER_ID, KWD_PROM_PEL, TABLE_ID, ADDITIONAL_ORDER, COMMENTS, REMARKS, USER_COMMENTS, ASSIGN_TO, STATUS, REC_STAMP, AUTHORISED_BY, SXOLIA, ORDER_NO, UNIQUE_ID, UPDATED) VALUES (2, '2020-04-30', '06:21:19', '20200430_062119_026243812-00001-1', 1.0, 1.6, 0.0, 1.6, 0.0, 0.0, '00001-1', '1300-000-070-4', '', '', 'ΜΕΤΡΙΟ|', '', '', '00008-1', '01', 1588227679, '', '', 2, '78a13aee-7e3e-11ea-9347-806e6f6e6963}202004300621190262438123782', '2020-04-30, 06:21:19.26243812');
INSERT INTO orders (AA, HMEROMHNIA, WRA, ORDER_ID, ITEMS, AMOUNT, DISCOUNT, AMOUNT_TOTAL, AMOUNT_PAID, AMOUNT_CANCELED, USER_ID, KWD_PROM_PEL, TABLE_ID, ADDITIONAL_ORDER, COMMENTS, REMARKS, USER_COMMENTS, ASSIGN_TO, STATUS, REC_STAMP, AUTHORISED_BY, SXOLIA, ORDER_NO, UNIQUE_ID, UPDATED) VALUES (3, '2020-04-30', '06:25:36', '20200430_062536_026500796-00001-1', 2.0, 3.2, 0.0, 3.2, 0.0, 0.0, '00001-1', '1300-000-070-4', '', '', 'ΜΕΤΡΙΟ|', '', '', '00008-1', '01', 1588227936, '', '', 3, '78a13aee-7e3e-11ea-9347-806e6f6e6963}202004300625360265007963007', '2020-04-30, 06:25:36.26500796');
INSERT INTO orders (AA, HMEROMHNIA, WRA, ORDER_ID, ITEMS, AMOUNT, DISCOUNT, AMOUNT_TOTAL, AMOUNT_PAID, AMOUNT_CANCELED, USER_ID, KWD_PROM_PEL, TABLE_ID, ADDITIONAL_ORDER, COMMENTS, REMARKS, USER_COMMENTS, ASSIGN_TO, STATUS, REC_STAMP, AUTHORISED_BY, SXOLIA, ORDER_NO, UNIQUE_ID, UPDATED) VALUES (4, '2020-04-30', '06:26:56', '20200430_062656_026580312-00001-1', 1.0, 1.9, 0.0, 1.9, 0.0, 0.0, '00001-1', '1300-000-105-9', '', '', 'ΓΛΥΚΟΣ|', '', '', '00008-1', '01', 1588228016, '', '', 4, '78a13aee-7e3e-11ea-9347-806e6f6e6963}202004300626560265803128479', '2020-04-30, 06:26:56.26580312');
INSERT INTO orders (AA, HMEROMHNIA, WRA, ORDER_ID, ITEMS, AMOUNT, DISCOUNT, AMOUNT_TOTAL, AMOUNT_PAID, AMOUNT_CANCELED, USER_ID, KWD_PROM_PEL, TABLE_ID, ADDITIONAL_ORDER, COMMENTS, REMARKS, USER_COMMENTS, ASSIGN_TO, STATUS, REC_STAMP, AUTHORISED_BY, SXOLIA, ORDER_NO, UNIQUE_ID, UPDATED) VALUES (5, '2020-04-30', '06:31:48', '20200430_063148_026872718-00001-1', 2.0, 3.2, 0.0, 3.2, 0.0, 0.0, '00001-1', '1300-000-168-3', '', '', '', '', '', '00008-1', '01', 1588228308, '', '', 5, '78a13aee-7e3e-11ea-9347-806e6f6e6963}202004300631480268727183471', '2020-04-30, 06:31:48.26872718');
I saw this on stackoverflow and would be interested to know if this makes a difference to the problem you're encountering:

"Commands out of sync; you can't run this command now"

"This error results when you terminate your query with a semicolon delimiter inside the application. While it is required to terminate a query with a semicolon delimiter when executing it from the command line or in the query browser, remove the delimiter from the query inside your application."
I strip the semicolon (;) from every statement and the records inserted correctly.
Thanks a lot!
I could not imagine such a thing!
I started the statements with:

Code: Select all

SET autocommit=0
BEGIN
...
...
...
COMMIT
Does MySQL understands the $LF as the end of statement instead of semicolon?
Another quirk of MySQL?
Regards,

Re: MySQL problem

Posted: Wed Apr 21, 2021 7:48 am
by wayne-c
the.weavster wrote: Tue Apr 20, 2021 5:46 pm
I saw this on stackoverflow and would be interested to know if this makes a difference to the problem you're encountering:

"Commands out of sync; you can't run this command now"

"This error results when you terminate your query with a semicolon delimiter inside the application. While it is required to terminate a query with a semicolon delimiter when executing it from the command line or in the query browser, remove the delimiter from the query inside your application."
Thank you for this hint. I am aware of this, and no, I do not have any semicolons at the end of my statements.

Re: MySQL problem

Posted: Wed Apr 21, 2021 8:42 am
by Marc56us
Just tested, works, but this is the start transaction still not work. (whatever keyword BEGIN, START TRANSACTION...)
Create table and insert works fine.

Test: MariaDB 10.5.9 x64 - Windows 10 20H2 - Pb 5.73 LTS x64

Code: Select all

UseMySQLDatabase() 

#DB_Name = "test"
#DB_User = "test"
#DB_Pass = "test"

If OpenDatabase(0, "host=localhost port=3306 dbname=" + #Db_Name, #DB_User, #DB_Pass)
    Debug "Connected to MySQL"
Else
    Debug "Connection failed: " + DatabaseError()
EndIf

Procedure Query(Query$)
    Debug #CRLF$ + "Query : " + Query$
    If DatabaseUpdate(0, Query$)
        Debug "Rows  : " + AffectedDatabaseRows(0)
    Else
        Debug "Rows  : " + AffectedDatabaseRows(0)
        Debug "Error : " + DatabaseError()
        FinishDatabaseQuery(0)
    EndIf
EndProcedure

Query("CREATE TABLE IF NOT EXISTS PB_Test (Col1 TEXT)")

Query("SET autocommit = 0")

Query("BEGIN") ; Query("START TRANSACTION")

Query("INSERT INTO PB_test (col1) VALUES ('AAA')") 
Query("INSERT INTO PB_test (col1) VALUES ('BBB')") 
Query("INSERT INTO PB_test (col1) VALUES ('CCC')") 

Query("COMMIT")

FinishDatabaseQuery(0)

If IsDatabase(0) : Debug #CRLF$ + "Close Database": CloseDatabase(0) : EndIf

End

Code: Select all

Connected to MySQL

Query : CREATE TABLE IF NOT EXISTS PB_Test (Col1 TEXT)
Rows  : 0

Query : SET autocommit = 0
Rows  : 0

Query : BEGIN
Rows  : 0
Error : Commands out of sync; you can't run this command now

Query : INSERT INTO PB_test (col1) VALUES ('AAA')
Rows  : 1

Query : INSERT INTO PB_test (col1) VALUES ('BBB')
Rows  : 1

Query : INSERT INTO PB_test (col1) VALUES ('CCC')
Rows  : 1

Query : COMMIT
Rows  : 0

Close Database
:|

Re: MySQL problem

Posted: Wed Apr 21, 2021 8:52 am
by captain_skank
I've tried this in the past and never got transactions to work at all.

Are you using ODBC and windows ?? does turning on 'execution of 'allow mulitple statements' in ODBC Data Sources help ?

Re: MySQL problem

Posted: Wed Apr 21, 2021 9:28 am
by wayne-c
I think we should not mix up things here. There are apparently two problems discussed in this thread:
  • UseMySQLDatabase() seems not to work correctly in general (via ODBC or direct access to the dll works, see above)
and
  • Some SQL's executed do not work (Transactions)
Maybe the two are related, maybe not.

I think we should first ensure UseMySQLDatabase() gets the bug fix, then see for the other problems.

Re: MySQL problem

Posted: Wed Apr 21, 2021 10:01 am
by thanos
@Marc56us
It worked for me also with a transaction (started with BEGIN).
I inserted 165.000 rows.
I had some errors with some rows but it worked.
Regards,
All times are UTC+01:00
Page 2 of 4

Powered by phpBB® Forum Software © phpBB Limited