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
And in PB, no need to write "Query$ = Query$ + ", just add 
(auto-indentation will check syntax for you)
But this is a SQL question and not PureBasic