MySQL problem | SOLVED

Just starting out? Need help? Post your questions and find answers here.
thanos
Enthusiast
Enthusiast
Posts: 422
Joined: Sat Jan 12, 2008 3:25 pm
Location: Greece
Contact:

MySQL problem | SOLVED

Post 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,
Last edited by thanos on Sun Aug 22, 2021 9:00 pm, edited 1 time in total.
» myPersonal Banker :: Because you do not need to have a master degree in economics in order to organize your finances!
User avatar
Fangbeast
PureBasic Protozoa
PureBasic Protozoa
Posts: 4747
Joined: Fri Apr 25, 2003 3:08 pm
Location: Not Sydney!!! (Bad water, no goats)

Re: MySQL problem

Post 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
Amateur Radio, D-STAR/VK3HAF
Marc56us
Addict
Addict
Posts: 1477
Joined: Sat Feb 08, 2014 3:26 pm

Re: MySQL problem

Post 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
infratec
Always Here
Always Here
Posts: 6817
Joined: Sun Sep 07, 2008 12:45 pm
Location: Germany

Re: MySQL problem

Post by infratec »

Since he don't use DatabaseQuery() he can not use FinishDatabaseQuery() :wink:

But you should show us your INSERT statement.
thanos
Enthusiast
Enthusiast
Posts: 422
Joined: Sat Jan 12, 2008 3:25 pm
Location: Greece
Contact:

Re: MySQL problem

Post 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
» myPersonal Banker :: Because you do not need to have a master degree in economics in order to organize your finances!
thanos
Enthusiast
Enthusiast
Posts: 422
Joined: Sat Jan 12, 2008 3:25 pm
Location: Greece
Contact:

Re: MySQL problem

Post 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,
» myPersonal Banker :: Because you do not need to have a master degree in economics in order to organize your finances!
thanos
Enthusiast
Enthusiast
Posts: 422
Joined: Sat Jan 12, 2008 3:25 pm
Location: Greece
Contact:

Re: MySQL problem

Post 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,
» myPersonal Banker :: Because you do not need to have a master degree in economics in order to organize your finances!
Marc56us
Addict
Addict
Posts: 1477
Joined: Sat Feb 08, 2014 3:26 pm

Re: MySQL problem

Post 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...
thanos
Enthusiast
Enthusiast
Posts: 422
Joined: Sat Jan 12, 2008 3:25 pm
Location: Greece
Contact:

Re: MySQL problem

Post 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,
» myPersonal Banker :: Because you do not need to have a master degree in economics in order to organize your finances!
thanos
Enthusiast
Enthusiast
Posts: 422
Joined: Sat Jan 12, 2008 3:25 pm
Location: Greece
Contact:

Re: MySQL problem

Post 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,
» myPersonal Banker :: Because you do not need to have a master degree in economics in order to organize your finances!
infratec
Always Here
Always Here
Posts: 6817
Joined: Sun Sep 07, 2008 12:45 pm
Location: Germany

Re: MySQL problem

Post 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.
thanos
Enthusiast
Enthusiast
Posts: 422
Joined: Sat Jan 12, 2008 3:25 pm
Location: Greece
Contact:

Re: MySQL problem

Post 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!
» myPersonal Banker :: Because you do not need to have a master degree in economics in order to organize your finances!
Marc56us
Addict
Addict
Posts: 1477
Joined: Sat Feb 08, 2014 3:26 pm

Re: MySQL problem

Post 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 ?
thanos
Enthusiast
Enthusiast
Posts: 422
Joined: Sat Jan 12, 2008 3:25 pm
Location: Greece
Contact:

Re: MySQL problem

Post 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
» myPersonal Banker :: Because you do not need to have a master degree in economics in order to organize your finances!
infratec
Always Here
Always Here
Posts: 6817
Joined: Sun Sep 07, 2008 12:45 pm
Location: Germany

Re: MySQL problem

Post by infratec »

I'll have a deeper look tomorrow, when I have access to mysql databases :wink:
Post Reply