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 1 of 4

MySQL problem | SOLVED

Posted: Sat Apr 10, 2021 12:10 pm
by thanos
Hello.
I converted an application based on SQLite to MySQL.
In every attempt to save records (insert or update) I faced the following error:

Code: Select all

Commands out of sync; you can't run this command now
The app is working smoothly also in SQLite and PostgreSQL.
I am running the MySQL in XAMPP v3.2.2, 32 bits, with Purebasic v5.73 x86, Windows 10 Pro, 64bits.
I also tried a minimal code and i faced the same problem:

Code: Select all

UseMySQLDatabase()

Procedure CheckDatabaseUpdate(Database, Query$)
  Protected Result
  
  Result = DatabaseUpdate(Database, Query$)
    If Result = 0
      Debug DatabaseError()
  EndIf
  
  ProcedureReturn Result
EndProcedure

  Debug FormatDate("%hh:%ii:%ss", Date()) 
  hDatabase = OpenDatabase(#PB_Any, "host=localhost port=3306 dbname=mydb", #MYSQL_DB_USER_NAME, #MYSQL_DB_USER_PASSWORD,  #PB_Database_MySQL)
  If (hDatabase)
    Debug "Connected to MySQL"
     CheckDatabaseUpdate(hDatabase, "SET autocommit = OFF;")
     ;- FinishDatabaseQuery(hDatabase)
    CheckDatabaseUpdate(hDatabase, "START TRANSACTION;")
    ForEach Values2Insert()
      CheckDatabaseUpdate(hDatabase, Values2Insert())
      i+1
    Next
    CheckDatabaseUpdate(hDatabase, "COMMIT;")
    Debug FormatDate("%hh:%ii:%ss", Date())
I also tried several versions of libmariadb.dll and libmysql.dll
e.g. UseMySQLDatabase("C:\Dev\v5.73_x86_LTS\Compilers\libmariadb.dll")
without any improvement.
In most cases it cannot execute the "START TRANSACTION;" statement.
The incomprehensible thing is that the same code runs without any problems in the same database via ODBC (MySQL ODBC 8.0 Unicode Driver).
Thanks in advance.
Regards,

Re: MySQL problem

Posted: Sat Apr 10, 2021 12:43 pm
by Fangbeast
thanos wrote:Hello.
I converted an application based on SQLite to MySQL.
In every attempt to save records (insert or update) I faced the following error:

Code: Select all

Commands out of sync; you can't run this command now
The app is working smoothly also in SQLite and PostgreSQL.
I am running the MySQL in XAMPP v3.2.2, 32 bits, with Purebasic v5.73 x86, Windows 10 Pro, 64bits.
I also tried a minimal code and i faced the same problem:

Code: Select all

UseMySQLDatabase()

Procedure CheckDatabaseUpdate(Database, Query$)
  Protected Result
  
  Result = DatabaseUpdate(Database, Query$)
    If Result = 0
      Debug DatabaseError()
  EndIf
  
  ProcedureReturn Result
EndProcedure

  Debug FormatDate("%hh:%ii:%ss", Date()) 
  hDatabase = OpenDatabase(#PB_Any, "host=localhost port=3306 dbname=mydb", #MYSQL_DB_USER_NAME, #MYSQL_DB_USER_PASSWORD,  #PB_Database_MySQL)
  If (hDatabase)
    Debug "Connected to MySQL"
     CheckDatabaseUpdate(hDatabase, "SET autocommit = OFF;")
     ;- FinishDatabaseQuery(hDatabase)
    CheckDatabaseUpdate(hDatabase, "START TRANSACTION;")
    ForEach Values2Insert()
      CheckDatabaseUpdate(hDatabase, Values2Insert())
      i+1
    Next
    CheckDatabaseUpdate(hDatabase, "COMMIT;")
    Debug FormatDate("%hh:%ii:%ss", Date())
I also tried several versions of libmariadb.dll and libmysql.dll
e.g. UseMySQLDatabase("C:\Dev\v5.73_x86_LTS\Compilers\libmariadb.dll")
without any improvement.
In most cases it cannot execute the "START TRANSACTION;" statement.
The incomprehensible thing is that the same code runs without any problems in the same database via ODBC (MySQL ODBC 8.0 Unicode Driver).
Thanks in advance.
Regards,
This may be a stupid question but isn't is supposed to be "BEGIN TRANSACTION" instead of "START TRANSACTION"? Haven't used MySql for years but I assumed the transact statement from sqlite was the same as MySQL

Re: MySQL problem

Posted: Sat Apr 10, 2021 1:25 pm
by Marc56us
Commands out of sync; you can't run this command now
'You can't have two simultaneous queries because mysqli uses unbuffered queries by default'
https://stackoverflow.com/questions/614 ... ommand-now

add FinishDatabaseQuery() after 'BEGIN' and then do the rest of query

PS: BEGIN, BEGIN WORKS, START TRANSACTION same

Re: MySQL problem

Posted: Sat Apr 10, 2021 1:57 pm
by infratec
Since he don't use DatabaseQuery() he can not use FinishDatabaseQuery() :wink:

But you should show us your INSERT statement.

Re: MySQL problem

Posted: Sat Apr 10, 2021 5:06 pm
by thanos
Fangbeast wrote:
thanos wrote: This may be a stupid question but isn't is supposed to be "BEGIN TRANSACTION" instead of "START TRANSACTION"? Haven't used MySql for years but I assumed the transact statement from sqlite was the same as MySQL
Thanks for the reponse.
There is no "BEGIN TRANSACTION" in MySQL. Is either "BEGIN" or "START TRANSACTION"
Regards

Re: MySQL problem

Posted: Sat Apr 10, 2021 5:09 pm
by thanos
Marc56us wrote:
Commands out of sync; you can't run this command now
'You can't have two simultaneous queries because mysqli uses unbuffered queries by default'
https://stackoverflow.com/questions/614 ... ommand-now

add FinishDatabaseQuery() after 'BEGIN' and then do the rest of query

PS: BEGIN, BEGIN WORKS, START TRANSACTION same
I read this article from stackoverflow before i post the issue.
I also tried the FinishDatabaseQuery() after the "START TRANSACTION;" even if there is not a DatabaseQuery() before as the infratec correctly said.
Regards,

Re: MySQL problem

Posted: Sat Apr 10, 2021 5:37 pm
by thanos
infratec wrote:Since he don't use DatabaseQuery() he can not use FinishDatabaseQuery() :wink:

But you should show us your INSERT statement.
Hi Bernd
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');
Please keep in mind that the same code, exactly the same code, works smoothly via as odbc database.
Also, I add the records with copy and paste the above insert statements via the PhpMyAdmin, without problem
Regards,

Re: MySQL problem

Posted: Sat Apr 10, 2021 5:53 pm
by Marc56us
I don't know if it comes from PB or MySQL, but you get the same error message ('Commands out of sync; you can't run this command now') if you try to write to a table that doesn't exist or even with any error in the SQL query.
MySQL/MariaDB...

Re: MySQL problem

Posted: Sat Apr 10, 2021 6:02 pm
by thanos
Marc56us wrote:I don't know if it comes from PB or MySQL, but you get the same error message ('Commands out of sync; you can't run this command now') if you try to write to a table that doesn't exist or even with any error in the SQL query.
MySQL/MariaDB...
:shock:
You are right! :shock:
I think it is not MySQL's error since I tried to insert into a non existing table with PhpMyAdmin and i received the following message:
#1146 - Table 'mydb.orders1' doesn't exist
Regards,

Re: MySQL problem

Posted: Sat Apr 10, 2021 7:35 pm
by thanos
I export the insert statements to a text file and i tried to insert them to the same MySQL database with Harbour https://harbour.github.io/
Everything was correctly without any problem.
Might it be a PB wrapper's problem?
Regards,

Re: MySQL problem

Posted: Sun Apr 11, 2021 1:38 pm
by infratec
Please do me a favour and post also your database structure.

I think it's a problem of not fitting ' ' text to a field.
especially for the date field at the end. But as mentioned, I need the structure of the database to check this.

Re: MySQL problem

Posted: Mon Apr 12, 2021 1:54 pm
by thanos
infratec wrote:Please do me a favour and post also your database structure.

I think it's a problem of not fitting ' ' text to a field.
especially for the date field at the end. But as mentioned, I need the structure of the database to check this.
Here is the database structure:
https://www.adouros.gr/tmp/mydb.sql
Thanks!

Re: MySQL problem

Posted: Mon Apr 12, 2021 3:43 pm
by Marc56us
Thanks, it's much easier with the structure and some data. :)

So, let's go

Code: Select all

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.5.9-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [test]> CREATE DATABASE PB_Test;
Query OK, 1 row affected (0.001 sec)

MariaDB [test]> USE PB_Test;
Database changed
MariaDB [PB_Test]> CREATE TABLE `orders` (
    ->   `aa` int(11) NOT NULL,
    ->   `hmeromhnia` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
    ->   `wra` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
    ->   `seconds` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
    ->   `order_id` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
    ->   `items` double DEFAULT NULL,
    ->   `amount` double DEFAULT NULL,
    ->   `discount` double DEFAULT NULL,
    ->   `vat` double DEFAULT NULL,
    ->   `amount_vat` double DEFAULT NULL,
    ->   `amount_net` double DEFAULT NULL,
    ->   `amount_total` double DEFAULT NULL,
    ->   `amount_paid` double DEFAULT NULL,
    ->   `amount_canceled` double DEFAULT NULL,
    ->   `user_id` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
    ->   `transfer_from_user` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
    ->   `kwd_prom_pel` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
    ->   `table_id` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
    ->   `table_comments` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
    ->   `transfer_from_table` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
    ->   `order_printed_id` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
    ->   `additional_order` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
    ->   `comments` blob,
    ->   `remarks` blob,
    ->   `user_comments` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
    ->   `assign_to` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
    ->   `status` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
    ->   `pay_with_cash` double DEFAULT NULL,
    ->   `pay_with_card` double DEFAULT NULL,
    ->   `pay_with_cheque` double DEFAULT NULL,
    ->   `pay_with_exhange` double DEFAULT NULL,
    ->   `pay_with_credit` double DEFAULT NULL,
    ->   `rec_stamp` bigint(20) DEFAULT NULL,
    ->   `authorised_by` varchar(25) COLLATE utf8_unicode_ci DEFAULT NULL,
    ->   `sxolia` blob,
    ->   `order_no` bigint(20) DEFAULT NULL,
    ->   `unique_id` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
    ->   `updated` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.007 sec)

MariaDB [PB_Test]> DESC orders;
+---------------------+--------------+------+-----+---------+-------+
| Field               | Type         | Null | Key | Default | Extra |
+---------------------+--------------+------+-----+---------+-------+
| aa                  | int(11)      | NO   |     | NULL    |       |
| hmeromhnia          | varchar(25)  | YES  |     | NULL    |       |
| wra                 | varchar(25)  | YES  |     | NULL    |       |
| seconds             | varchar(25)  | YES  |     | NULL    |       |
| order_id            | varchar(50)  | YES  |     | NULL    |       |
| items               | double       | YES  |     | NULL    |       |
| amount              | double       | YES  |     | NULL    |       |
| discount            | double       | YES  |     | NULL    |       |
| vat                 | double       | YES  |     | NULL    |       |
| amount_vat          | double       | YES  |     | NULL    |       |
| amount_net          | double       | YES  |     | NULL    |       |
| amount_total        | double       | YES  |     | NULL    |       |
| amount_paid         | double       | YES  |     | NULL    |       |
| amount_canceled     | double       | YES  |     | NULL    |       |
| user_id             | varchar(25)  | YES  |     | NULL    |       |
| transfer_from_user  | varchar(25)  | YES  |     | NULL    |       |
| kwd_prom_pel        | varchar(25)  | YES  |     | NULL    |       |
| table_id            | varchar(25)  | YES  |     | NULL    |       |
| table_comments      | varchar(25)  | YES  |     | NULL    |       |
| transfer_from_table | varchar(25)  | YES  |     | NULL    |       |
| order_printed_id    | varchar(50)  | YES  |     | NULL    |       |
| additional_order    | varchar(50)  | YES  |     | NULL    |       |
| comments            | blob         | YES  |     | NULL    |       |
| remarks             | blob         | YES  |     | NULL    |       |
| user_comments       | varchar(255) | YES  |     | NULL    |       |
| assign_to           | varchar(25)  | YES  |     | NULL    |       |
| status              | varchar(25)  | YES  |     | NULL    |       |
| pay_with_cash       | double       | YES  |     | NULL    |       |
| pay_with_card       | double       | YES  |     | NULL    |       |
| pay_with_cheque     | double       | YES  |     | NULL    |       |
| pay_with_exhange    | double       | YES  |     | NULL    |       |
| pay_with_credit     | double       | YES  |     | NULL    |       |
| rec_stamp           | bigint(20)   | YES  |     | NULL    |       |
| authorised_by       | varchar(25)  | YES  |     | NULL    |       |
| sxolia              | blob         | YES  |     | NULL    |       |
| order_no            | bigint(20)   | YES  |     | NULL    |       |
| unique_id           | varchar(100) | YES  |     | NULL    |       |
| updated             | varchar(50)  | YES  |     | NULL    |       |
+---------------------+--------------+------+-----+---------+-------+
38 rows in set (0.005 sec)

MariaDB [PB_Test]> 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');
Query OK, 1 row affected (0.001 sec)

MariaDB [PB_Test]> 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');
Query OK, 1 row affected (0.002 sec)

MariaDB [PB_Test]> 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');
Query OK, 1 row affected (0.001 sec)

MariaDB [PB_Test]> 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');
Query OK, 1 row affected (0.001 sec)

MariaDB [PB_Test]> 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');
Query OK, 1 row affected (0.002 sec)

MariaDB [PB_Test]> SELECT COUNT(*) FROM orders;
+----------+
| COUNT(*) |
+----------+
|        5 |
+----------+
1 row in set (0.000 sec)

MariaDB [PB_Test]> SELECT unique_id, updated FROM orders;
+------------------------------------------------------------------+-------------------------------+
| unique_id                                                        | updated                       |
+------------------------------------------------------------------+-------------------------------+
| 78a13aee-7e3e-11ea-9347-806e6f6e6963}202004300610150255790621303 | 2020-04-30, 06:10:15.25579062 |
| 78a13aee-7e3e-11ea-9347-806e6f6e6963}202004300621190262438123782 | 2020-04-30, 06:21:19.26243812 |
| 78a13aee-7e3e-11ea-9347-806e6f6e6963}202004300625360265007963007 | 2020-04-30, 06:25:36.26500796 |
| 78a13aee-7e3e-11ea-9347-806e6f6e6963}202004300626560265803128479 | 2020-04-30, 06:26:56.26580312 |
| 78a13aee-7e3e-11ea-9347-806e6f6e6963}202004300631480268727183471 | 2020-04-30, 06:31:48.26872718 |
+------------------------------------------------------------------+-------------------------------+
5 rows in set (0.000 sec)

MariaDB [PB_Test]>
First observation: the structure and the data insertion are OK
So we can deduce that the problem is most probably the PB functions ?

Re: MySQL problem

Posted: Mon Apr 12, 2021 8:20 pm
by thanos
Marc56us wrote:First observation: the structure and the data insertion are OK
So we can deduce that the problem is most probably the PB functions ?
I suppose that Marc56us has right.
The data insertion procedure with PhpMyAdmin (copy+paste) and Harbour executable (inserts more than 75000 rows) are correct.
The remarkable thing is that the PureBasic code works smoothly if i just change the UseMySQLDatabase() to UseODBCDatabase() and use the MySQL 8.0 ODBC Unicode Driver.
Regards

Re: MySQL problem

Posted: Mon Apr 12, 2021 8:25 pm
by infratec
I'll have a deeper look tomorrow, when I have access to mysql databases :wink:
All times are UTC+01:00
Page 1 of 4

Powered by phpBB® Forum Software © phpBB Limited