Sign in Join

Live chat for Free Pascal, Lazarus, Delphi and every Pascal programmer.

Mailing lists / fpc-pascal / 2008-02

MySQL insert, load data

4 messages · 3 people · –

Reply by email Subscribe to this list Your subscription

Read-only archive. Use j and k to move between messages.

Wolfram Kläger

Along Joost's examples I managed to get connected, create a table, put a query, get the result, and insert a single row. But when I try to insert several rows at once, I get an EDatabaseError "near" to first char of second row.

...
q.SQL.Clear;
q.Add('insert into MYTABLE(c1, c2, ..) values ');
q.Add('(r1v1, r1v2, ..),');
q.Add('(r2v1, r2v1, ..),');
..
q.Add(';');

What am I missing or is multiple insert not supported?

Alternatively, MySQL provides the LOAD DATA [LOCAL] INFILE statement. So far I was not able to find a special sqldb wrapper for it. Is it done via ExecuteDirect?

Wolfram

Wolfram Kläger
to Wolfram Kläger

Ok. First one was simple. Keyword and opening bracket must be separated by a blank, e.g.

'.. MYTABLE(..' // wrong
'.. MYTABLE (..' // great

'..)values(..' // wrong
'..) values (..' // great

All field contents have to be bracketed by double apostrophes, e.g.

q.sql.add('''Florian Klaempfl'',');

except it's an integer value, e.g.

q.sql.add('1,');

Date values seem to be put as formatted strings. Unfortunately, my MySQL does not know Joost. His example, '1-jan-1975' works here as '1975.1.1' only. Guess this is a config issue.

Now what about LOAD DATA?

I still have no idea where to look for it. I learned TSQLQuery is descending Tbufdataset <- TDBDataSet <- TDataset <- TComponent and assume there is somewhere an option to load from a CSV text file or stream.

But how to push it forward to MySQL?
Wolfram

// Wolfram Kläger, 14.02.2008 22:49
Show quoted text 18 lines
Along Joost's examples I managed to get connected, create a table, put a query, get the result, and insert a single row. But when I try to insert several rows at once, I get an EDatabaseError "near" to first char of second row. ... q.SQL.Clear; q.Add('insert into MYTABLE(c1, c2, ..) values '); q.Add('(r1v1, r1v2, ..),'); q.Add('(r2v1, r2v1, ..),'); .. q.Add(';'); What am I missing or is multiple insert not supported? Alternatively, MySQL provides the LOAD DATA [LOCAL] INFILE statement. So far I was not able to find a special sqldb wrapper for it. Is it done via ExecuteDirect?
Leonardo M. Ram�
to Wolfram Kläger

Try this:

q.SQL.Clear;
q.Add('LOAD DATA INFILE ''data.txt'' INTO TABLE database.my_table;');
q.Execute; // <-- I don't remember the correct command right now

Leonardo

--- Wolfram Kläger wrote 56 lines
Ok. First one was simple. Keyword and opening bracket must be separated by a blank, e.g. '.. MYTABLE(..' // wrong '.. MYTABLE (..' // great '..)values(..' // wrong '..) values (..' // great All field contents have to be bracketed by double apostrophes, e.g. q.sql.add('''Florian Klaempfl'','); except it's an integer value, e.g. q.sql.add('1,'); Date values seem to be put as formatted strings. Unfortunately, my MySQL does not know Joost. His example, '1-jan-1975' works here as '1975.1.1' only. Guess this is a config issue. Now what about LOAD DATA? I still have no idea where to look for it. I learned TSQLQuery is descending Tbufdataset <- TDBDataSet <- TDataset <- TComponent and assume there is somewhere an option to load from a CSV text file or stream. But how to push it forward to MySQL? Wolfram // Wolfram Kläger, 14.02.2008 22:49 > Along Joost's examples I managed to get connected, create a table, put a > query, get the result, and insert a single row. But when I try to insert > several rows at once, I get an EDatabaseError "near" to first char of > second row. > > ... > q.SQL.Clear; > q.Add('insert into MYTABLE(c1, c2, ..) values '); > q.Add('(r1v1, r1v2, ..),'); > q.Add('(r2v1, r2v1, ..),'); > .. > q.Add(';'); > > What am I missing or is multiple insert not supported? > > Alternatively, MySQL provides the LOAD DATA [LOCAL] INFILE statement. So > far I was not able to find a special sqldb wrapper for it. Is it done > via ExecuteDirect? _______________________________________________ fpc-pascal maillist - http://lists.freepascal.org/mailman/listinfo/fpc-pascal

____________________________________________________________________________________ Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ

Michael Van Canneyt
to Leonardo M. Ram�
Show quoted text 6 lines
Try this: q.SQL.Clear; q.Add('LOAD DATA INFILE ''data.txt'' INTO TABLE database.my_table;'); q.Execute; // <-- I don't remember the correct command right now

Normally it is:

 Q.ExecSQL;

Michael.