[fpc-pascal] Database parameter trouble in insert query

Reinier Olislagers reinierolislagers at gmail.com
Sat Sep 7 09:22:07 CEST 2013


An application needs to dump output into a (file-based for now) database
(like MS Access, Firebird embedded, sqlite), Excel, CSV file etc.
FPC trunk, x86 and x64, Windows (for now), Linux,...

I've got the create database/create table functionality working but am
hitting some issues copying dataset fields to the resulting table.
The source and target field names are the same.

I'm using a loop to build a parametrized insert query
InsertSQL := 'INSERT INTO ' + TableName + '(';
// Field names; assumes target field name is same as source displayname
for i := 0 to Source.Fields.Count - 1 do
begin
	InsertSQL := InsertSQL + Source.Fields[i].DisplayName;
	if (i < Source.Fields.Count - 1) then
		InsertSQL := InsertSQL + ', ';
end;
InsertSQL := InsertSQL + ') VALUES (';
//Parameters
for i := 0 to Source.Fields.Count - 1 do
begin
	InsertSQL := InsertSQL + ':' + Source.Fields[i].DisplayName;
	if (i < Source.Fields.Count - 1) then
		InsertSQL := InsertSQL + ', ';
end;
InsertSQL := InsertSQL + ') ';

Then looping over the set, I execute it. I thought AssignFromField may
be appropriate:
for i := 0 to Source.Fields.Count - 1 do
begin
  InsertQuery.Params[i].Clear;
  InsertQuery.Params[i].AssignFromField(Source.Fields[i]);
end;
InsertQuery.ExecSQL;

The code works mostly, but:
1. With Firebird embedded (and BLOB fields) the output seems truncated
after 1024 bytes
2. With ODBC+Access, I get error messages like
HY104; NativeError: 98; Message: [Microsoft][ODBC Microsoft Access
Driver]Invalid precision value;

Doing InsertQuery.Params[i].Value:=Source.Fields[i].Value instead
doesn't help.
What am I doing wrong? Am I missing something obvious again?

Thanks,
Reinier

Unit:
http://www.pastebin.ca/2444884
Complete program:
https://bitbucket.org/reiniero/db2securityscript/src
directory OutputParser
demo file is db2secout.txt



More information about the fpc-pascal mailing list