I am using Altova Mapforce (www.altova.com) as a tool to map some csv files
to database files. This tool generates C# source so I can build an exe or
dll so I can implement that into my Delphi project.
I map several CSV fields of type decimal or integer to corresponding
database fields. The generated C# source translates this into an ODBC Insert
statement with parameter fields. When a field in the original CSV file is
empty, the value DBNull.Value is assigned to the corresponding parameter.
So far everything looks ok, but when the SQL is executed I get an SQL error
on invalid types:
ERROR: INSERT INTO `eoedibp` (`EOEONUMM`,`EOEOSTAT`,`EOOGNUMM`,`EOEOREFE`,
`EOEOGEWI`,`EOEOCOLL`,`EOEOPALL`,`EOEONAAM21`,`EOEOSTRA21`,`EOEOHSNR21`,`EOEOADRS21`,
`EOQPCODE21`,`EOEOPLAA21`,`EOEODATA`,`EOEOTDVA`,`EOEOTDTA`,`EOEOBEDR33`,`EOQVCODE33`,
`EOEOOPME`,`EOEOOMS1`,`EOEBNUMM`) VALUES ( '6', '10', '411607', '123458',
'', '12', '', 'F.', 'P 220', '', '', 'AE', 'S', '2006-12-01', '08:00:00',
'17:00:00', '', 'EUR', '', 'Allerlei Dingen', '20' )
ERROR [07006] [MySQL][ODBC 3.51
Driver][mysqld-5.0.24-community-nt]Restricted data type attribute
violation(SQL_C_NUMERIC)
For example the fifth field (EOEOGEWI) is decimal, but as value '' is being
passed instead of NULL.
So now for my question. Where do I lay the blame. Is it the db (MySQL in
this case), is it the MySQL ODBC driver, is it somewhere in the used
dbCommand.ExecuteReader() where things might go wrong or is it, despite
assigning DBNull.Value, an issue of filling the parameters.
Thanks for any pointers in the right direction.