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

Reply by email

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?

Reply by email

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

Reply by email

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.

Reply by email