TSQLQuery.Params.ParamByName problem at fields number over 100
lazarus / 16-Databases

----------------------------------------------------------------------
skis · 2011-06-24 10:30

I have a big table with 220 fileds in PostgresSQL 8.4. I working in
FPC2.4.2 command line mode.  I made a TQuery with 'INSERT INTO table
(field1, field2, ..., field220) VALUES (:f1,:f2, ..., :f220)' and add
parameters by Params.ParamByName('f1').asInteger,
Params.ParamByName('f2').asInteger  ect.  It is works good until field
99, but it is works fail over the field 100. It is took value of 'f10'
into the fields 100..109, value of 'f11' into the fields 110..119 ect.
I tried to rename parameters to 'a1'..'a99', 'b1..b99' and 'c1..c20'.
It works fail too, so the mistake is independently to the parameter
names. I tried to get back and write  the ParamByName vaules and it
was good. I tried to omit the ParamByName functions and put the values
direct to the INSERT ... VALUES('1','2','3', ..., '220') and it works
good! I think the mistake is in the ParamByName usage in the ExecSQL
function.
Have you got any idea for it?  It is a bug or an apllication error?  

Best regards,  thank you.
Sandor Kis

----------------------------------------------------------------------
SteveF · 2011-06-24 15:42

This is a little off-topic, but relevant:

A table with 220 fields suggests that there's something deeply flawed
in the overall database design.  Relational databases (DB2, Oracle,
MySQL, PostgreSQL, Firebird, SQL Server, Sybase, even MS Access)
really work best with "tall/skinny" table designs, rather than
"short/wide" as seems to be the case here.

If at all possible, suggest that you re-visit the database design and
make sure it is as normalized as possible.  This should go a long way
toward minimizing other problems.

Steve

----------------------------------------------------------------------
skis · 2011-06-24 23:15

Thanks for your advice.
I have a normalized database with 14 tables. That is the biggest table
and holds measured values  of electronic in circuit tests.

Sandor Kis

----------------------------------------------------------------------
Lacak2 · 2011-06-27 05:33

Quote from: skis on June 24, 2011, 12:30:56 pmI have a big table with
220 fileds in PostgresSQL 8.4. ...
It is a bug or an apllication error?    
Hi
It is a bug, please fill bug report about it ...

----------------------------------------------------------------------
Lacak2 · 2011-06-28 07:05

Bug report (with patch) is here
http://bugs.freepascal.org/view.php?id=19645

----------------------------------------------------------------------
eny · 2011-06-28 19:17

Quote from: skis on June 25, 2011, 01:15:41 amThanks for your advice.
I have a normalized database with 14 tables.Are you sure?
(http://en.wikipedia.org/wiki/Database_normalization#Normal_forms)
Nevertheless, I hope the bug will be fixed (if it's classified as a
FPC bug instead of a Lazarus one then there's little chance).