PureBasic Forums - English

Posted: **Thu Aug 30, 2018 9:47 pm**

Hi,

For a few years I have been importing database records into SQLite using the following code.

Query$ = "BEGIN: " + #CRLF$
Query$ = Query$ + "INSERT INTO `mytable` (`ID`, `Date_TS`, `HTeam`, `ATeam`, `FTG`, `FTA`, `FT`) VALUES (0, 1533340799, 'Jordan', 'Jack', 0, 0, 'Z');" + #CRLF$
Query$ = Query$ + "INSERT INTO `mytable` (`ID`, `Date_TS`, `HTeam`, `ATeam`, `FTG`, `FTA`, `FT`) VALUES (0, 1533340799, 'Freddy', 'Jon', 0, 0, 'Z');" + #CRLF$
Query$ = Query$ + = "commit: "
CheckDatabaseUpdate(DatabaseNumber.l, Query$)

Now this has been working great, however I have now decided to use MySQL instead, so I have setup MySQL and can happily CRUD all the single statements I like, but now coming to add multiple records as I did with SQLite (as above) I have found that it just does not work with MySQL via OBDC

Has any here managed to input multiple records using Begin/Commit transactions to a MySQL database?

PS, I have also tried a few other ways but still can not get 2 or more transactions to work

Thanks

Posted: **Thu Aug 30, 2018 10:12 pm**

Not an answer, but are you sure about "BEGIN:"

I use "BEGIN;".

Posted: **Fri Aug 31, 2018 6:45 am**

https://dev.mysql.com/doc/refman/8.0/en/commit.html

Posted: **Fri Aug 31, 2018 10:24 am**

skywalk wrote:Not an answer, but are you sure about "BEGIN:"
I use "BEGIN;".

Yes, I did mean ; I mistyped whilst writing this post. The actual code does have a ;

As for infratec reply, I have tried various BEGIN; or BEGIN TRANSACTION; or START TRANSACTION; but still can not get a multi INSERT to actually insert then I noticed that at the end of the first INSERT statement I had an ; not include a , so after changing this it is working again. I could blame my new keyboard but it's my fingers I need to blame.

Thanks all

Posted: **Fri Aug 31, 2018 10:28 am**

Query$ = Query$ + = "commit: "

Keyboard error

PS. Consider using DataBaseError() too

Posted: **Fri Aug 31, 2018 10:38 am**

And don't use .l
use .i instead.

Posted: **Fri Aug 31, 2018 10:43 am**

PS.

You can add multiples values in one single line
(no need to copy again 'INSERT INTO `mytable`')

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

And in PB, no need to write "Query$ = Query$ + ", just add + at end of line
Faster and much easier to read

(auto-indentation will check syntax for you)

Code: Select all

Query$ = "BEGIN; " + 
         #CRLF$ + 
         "INSERT INTO `mytable` " +
         "(`ID`, `Date_TS`, `HTeam`, `ATeam`, `FTG`, `FTA`, `FT`)" + 
         "VALUES " +
         "(0, 1533340799, 'Jordan', 'Jack', 0, 0, 'Z') , " + 
         "(0, 1533340799, 'Freddy', 'Jon', 0, 0, 'Z') ; "  + 
         #CRLF$ +
         "COMMIT;"

(No tested)

Posted: **Fri Aug 31, 2018 9:38 pm**

In the future, use the debugger to print your Query$!
Then paste it into a SQLite or MySQL DB browser.
This catches typo's, wrong column names, sql syntax errors, etc.
If your query works then you can safely parse it to pb.

Posted: **Thu Sep 06, 2018 12:20 pm**

Thanks for the replies, I got it working great a few days ago but then came across another few issues and been stuck even though I have tried quite a few permutations on writing the code.

When using SQLite there was no limitation on the size of the query string, however on MySQL (via ODBC) there is a 64k limit. OK, so I can work around this by writing few extra INSERTS in the loop.

My main stumbling block is writing the INSERT query with an "ON DUPLICATE KEY UPDATE". No matter what if I include the on dupe then I can just not do multiple inserts.
So if we take the code Marc56us wrote and include the On Dup statement then it just does not work.

Code: Select all

Query$ = "BEGIN; " +
         #CRLF$ +
         "INSERT INTO `mytable` " +
         "(`ID`, `Date_TS`, `HTeam`, `ATeam`, `FTG`, `FTA`, `FT`)" +
         "VALUES " +
         "(0, 1533340799, 'Jordan', 'Jack', 0, 0, 'Z') ON DUPLICATE KEY UPDATE  FTG=3,FTA=2, " +
         "(0, 1533340799, 'Freddy', 'Jon', 0, 0, 'Z') ON DUPLICATE KEY UPDATE FTG=3,FTA=2; "  +
         #CRLF$ +
         "COMMIT;"

I can do a single INSERT with the On Dup and this works. The primary key is the "ID"

Code: Select all

Query$ = "BEGIN; " +
         #CRLF$ +
         "INSERT INTO `mytable` " +
         "(`ID`, `Date_TS`, `HTeam`, `ATeam`, `FTG`, `FTA`, `FT`)" +
         "VALUES " +
         "(0, 1533340799, 'Jordan', 'Jack', 0, 0, 'Z') ON DUPLICATE KEY UPDATE  FTG=3,FTA=2; " +
         #CRLF$ +
         "COMMIT;"

So I have tried several way of seperating the 2 INSERTS
eg
ON DUPLICATE KEY UPDATE FTG=3,FTA=2, " +
ON DUPLICATE KEY UPDATE FTG=3,FTA=2; " +

or

ON DUPLICATE KEY UPDATE FTG=3,FTA=2; " +
ON DUPLICATE KEY UPDATE FTG=3,FTA=2; " +

or

ON DUPLICATE KEY UPDATE FTG=VALUES(3),FTA=VALUES(2), " +
ON DUPLICATE KEY UPDATE FTG=VALUES(3),FTA=VALUES(2); " +

or

ON DUPLICATE KEY UPDATE FTG=VALUES(3),FTA=VALUES(2); " +
ON DUPLICATE KEY UPDATE FTG=VALUES(3),FTA=VALUES(2); " +

But nothing works. Can anyone shed some light.

I can do everything as a last resort as single INSERT statements with On DUPE in a loop but with over 10,000 entries it takes quite a long time (4 hours) running as it has to do 10000+ database read/writes whereas on the old SQLite database it just took 10mins

Thankjs

Posted: **Thu Sep 06, 2018 1:36 pm**

Here is some code I have knocked up to try and understand who it all would work. It all about the code between the -------------------------------

The MySQL Database is on my local machine, there is an 64bit ODBC connection linked to MySQL

Code: Select all


MySQL_Username.s = "root"
MySQL_Password.s = "Mypassismyword"
Define CommitToTable.s
Define DatabaseMySQL.l
Define DatabaseMainDataTable.s ="crud_test"
Define OKDB.l = #False
Define NoRows.l= 0

Procedure CheckMySQLDatabaseUpdate(DatabaseMySQL, Query$)
  Result = DatabaseUpdate(DatabaseMySQL, Query$)
  If Result = 0
    Debug "CheckMySQLDatabaseUpdate: Error " + DatabaseError()
  Else
    Debug "CheckMySQLDatabaseUpdate: " + Str(Result)    
  EndIf
  
  ProcedureReturn Result
EndProcedure

Procedure CheckMySQLDatabaseQuery(DatabaseMySQL, Query$)
  Result = DatabaseQuery(DatabaseMySQL, Query$)
  If Result = 0
    Debug "CheckMySQLDatabaseQuery: Error " + DatabaseError()
  Else
    Debug "CheckMySQLDatabaseQuery: " + Str(Result)
  EndIf
  
  ProcedureReturn Result
EndProcedure

Procedure GetRandom()
  Result = Random(999999,1)
  
  ProcedureReturn Result
EndProcedure

UseODBCDatabase()


Debug "PB CRUD MySQL Example"
Debug "Connecting to MySQL"
DatabaseMySQL.l = OpenDatabase(#PB_Any, "MySQL", MySQL_Username.s, MySQL_Password,#PB_Database_ODBC )
If DatabaseMySQL.l 
  Debug "Found Connection... Connecting" 
  If IsDatabase(DatabaseMySQL.l)
    Debug "Connected" 
    Debug "Checking to see if table '"+DatabaseMainDataTable.s+"' exists" 
    Query$ =  "Select count(*) FROM information_schema.TABLES WHERE (TABLE_SCHEMA = 'coredb') And (TABLE_NAME = '"+DatabaseMainDataTable.s + "')";
    CheckMySQLDatabaseQuery(DatabaseMySQL, Query$)
    While NextDatabaseRow(DatabaseMySQL)
      NoRows.l= GetDatabaseLong(DatabaseMySQL, 0) 
    Wend
    FinishDatabaseQuery(DatabaseMySQL)
    If NoRows.l > 0
      Debug "Table `" + DatabaseMainDataTable.s + "` Exists"
      CommitToTable.s = CommitToTable.s + "DROP TABLE IF EXISTS `" + DatabaseMainDataTable.s + "`;"      
      Query$ = CommitToTable.s
      If CheckMySQLDatabaseUpdate(DatabaseMySQL, Query$)           
        Debug "Table `" + DatabaseMainDataTable.s + "` Dropped"
      Else 
        Debug "Error dropping Table `" + DatabaseMainDataTable.s + "`"
      EndIf
    Else 
      Debug "Table `" + DatabaseMainDataTable.s + "` does NOT exist" 
    EndIf
    
    
    Debug "CRUD: Ready To CREATE Table `" + DatabaseMainDataTable.s + "`" 
    CommitToTable.s = ""
    CommitToTable.s = CommitToTable.s + "CREATE TABLE `" + DatabaseMainDataTable.s + "` ("
    CommitToTable.s = CommitToTable.s + "`ID` int(11) NOT NULL,"
    CommitToTable.s = CommitToTable.s + "`Date_TS` int(11) NOT NULL,"
    CommitToTable.s = CommitToTable.s + "`HTeam` varchar(75) NOT NULL,"
    CommitToTable.s = CommitToTable.s + "`ATeam` varchar(75) NOT NULL,"
    CommitToTable.s = CommitToTable.s + "`FTG` int(11) DEFAULT '0',"
    CommitToTable.s = CommitToTable.s + "`FTA` int(11) DEFAULT '0',"
    CommitToTable.s = CommitToTable.s + "`FT` varchar(1) DEFAULT '-'"
    CommitToTable.s = CommitToTable.s + ") ENGINE=InnoDB DEFAULT CHARSET=utf8;"
    Query$ = CommitToTable.s
    If CheckMySQLDatabaseUpdate(DatabaseMySQL, Query$)           
      Debug "Table `" + DatabaseMainDataTable.s + "` Created, now creating Indexes"
      CommitToTable.s =""
      CommitToTable.s = CommitToTable.s + "ALTER TABLE `" + DatabaseMainDataTable.s + "`"
      CommitToTable.s = CommitToTable.s + "ADD PRIMARY KEY (`ID`),"
      CommitToTable.s = CommitToTable.s + "ADD KEY `Date_TS` (`Date_TS`),"
      CommitToTable.s = CommitToTable.s + "ADD KEY `HTeam` (`HTeam`),"
      CommitToTable.s = CommitToTable.s + "ADD KEY `ATeam` (`ATeam`),"
      CommitToTable.s = CommitToTable.s + "ADD KEY `FT` (`FT`);"
      Query$ = CommitToTable.s
      If CheckMySQLDatabaseUpdate(DatabaseMySQL, Query$)           
        Debug "Indexes Created for Table `" + DatabaseMainDataTable.s + "`"
        CommitToTable.s ="INSERT INTO `" + DatabaseMainDataTable.s + "` (`ID`, `Date_TS`, `HTeam`, `ATeam`, `FTG`, `FTA`, `FT`) VALUES (1, 1533340799, 'Jordan', 'Jack', 0, 0, 'Z');"
        Query$ = CommitToTable.s
        If CheckMySQLDatabaseUpdate(DatabaseMySQL, Query$)           
          Debug "Record Added to Table `" + DatabaseMainDataTable.s + "`"
          OKDB.l = #True
        Else
          Debug "Record Failed"
        EndIf
      Else
        Debug "Indexes Failed"
      EndIf
    Else
      Debug "Error Table `" + DatabaseMainDataTable.s + "` NOT Created"
      
    EndIf
    If OKDB.l = #True
      If DatabaseQuery(DatabaseMySQL,"SELECT * FROM `" + DatabaseMainDataTable.s + "` WHERE HTeam ='Jordan'")
        Debug "CRUD Success:  READ from Table `" + DatabaseMainDataTable.s + "`"
        While NextDatabaseRow(DatabaseMySQL)
          Debug "Read: " + GetDatabaseString(DatabaseMySQL, 1) 
        Wend
      EndIf
      
      Query$ =  "INSERT INTO `" + DatabaseMainDataTable.s + "` (`ID`, `Date_TS`, `HTeam`, `ATeam`, `FTG`, `FTA`, `FT`) VALUES (2, 1533340799, 'Jordan', 'Jack', 0, 0, 'Z') ON DUPLICATE KEY UPDATE FTG=3,FTA=2;"
      If CheckMySQLDatabaseUpdate(DatabaseMySQL, Query$)
        Debug "CRUD Success:  INSERT ON DUPE record from Table `" + DatabaseMainDataTable.s + "` using Query: " + Query$
      Else
        Debug "CRUD Failure: Record Insert/Update Failed: " + Query$
      EndIf
      
      Debug "--- Adding 3 Records, 2 of which are duplicates, the other is a new record---"
      
      RowsArray.l = 3
      Dim VeryBigValueArray$(RowsArray.l)
      Header$ = "INSERT INTO `" + DatabaseMainDataTable.s + "` (`ID`, `Date_TS`, `HTeam`, `ATeam`, `FTG`, `FTA`, `FT`) VALUES "
      For RowOfArray.l=1 To RowsArray.l 
        VeryBigValueArray$(RowOfArray.l) = "(" + RowOfArray.l + ", 1533340799, 'Jordan', 'Jack', 0, 0, 'Z') ON DUPLICATE KEY UPDATE FTG=3,FTA=2" 
      Next
      Query$ = ""
      For RowOfArray.l = 1 To RowsArray.l-1
        Query$= Query$ + VeryBigValueArray$(RowOfArray.l) + "," + #CRLF$
      Next
      Query$= Query$ + VeryBigValueArray$(RowsOfArray.l+1) + ";"
      If CheckMySQLDatabaseUpdate(DatabaseMySQL, Query$)
        Debug "CRUD Success: MULTI INSERT record from Table `" + DatabaseMainDataTable.s + "` using Query: " + Query$
      Else
        Debug "CRUD Failure: MULTI Record Insert/Update Failed: " + Query$
      EndIf
      Debug "-----------------------------------------------------"
    EndIf
  Else
    Debug "Can not find valid Database"
  EndIf
Else
  Debug "Connection failed: "+DatabaseError()
EndIf

CloseDatabase(DatabaseMySQL.l)

Posted: **Thu Sep 06, 2018 5:46 pm**

I can do everything as a last resort as single INSERT statements with On DUPE in a loop but with over 10,000 entries it takes quite a long time (4 hours) running as it has to do 10000+ database read/writes whereas on the old SQLite database it just took 10mins

It is not normal that inserting 10,000 records takes several hours and even minutes. It doesn't matter what the database is.

If you have access to the MySQL server, you should use LOAD DATA INFILE and a CSV file, it is considerably faster than successive INSERTs. (in SQLite .import)
With 10,000 records it should take a few seconds.

You insert your data into a temporary table, then update the duplicates in the main table.
(And there are other ways than ON DUPLICATE KEY UPDATE)

But this is a SQL question and not PureBasic

Posted: **Thu Sep 06, 2018 8:46 pm**

Marc56us wrote:
If you have access to the MySQL server, you should use LOAD DATA INFILE and a CSV file, it is considerably faster than successive INSERTs. (in SQLite .import)
With 10,000 records it should take a few seconds.

Thanks for the reply. I am not sable to go down the LOAD DATA route as the CSV I am using requires some amending and additional fields being added during the import process. I have no control over the CSV file, it comes from a different company.

As I said previously, currently the existing setup (6 years old now) is using PB & SQLite. Now I am needing to put the data onto our front facing webserver. I initially tried using PHP to read the SQLIte file, which was ok'ish but not ideal in the medium-long term as it is slow. I tried to run a daily php importation process via the shedular from SQLite into MySQL but this was not really successful and I still can not find out why but there are issues in not picking up all the records in the SQLite table. ie at least 25% would be missing records and each time it is run it would be different.

So the only real solution was to get the PB based application to do the importation and amendments and put into MySQL, that way it would be quicker and less troublesome. So I am back to bulking multiple INSERTS together

PureBasic Forums - English

Problem converting SQLite to MySQL

Problem converting SQLite to MySQL

Re: Problem converting SQLite to MySQL

Re: Problem converting SQLite to MySQL

Re: Problem converting SQLite to MySQL

Re: Problem converting SQLite to MySQL

Re: Problem converting SQLite to MySQL

Re: Problem converting SQLite to MySQL

Re: Problem converting SQLite to MySQL

Re: Problem converting SQLite to MySQL

Re: Problem converting SQLite to MySQL

Re: Problem converting SQLite to MySQL

Re: Problem converting SQLite to MySQL