Re: loadfile with quotation marks -> csv-file
Jonathan Leffler <jleffler@earthlink.net> wrote
> Well, 'sqlcmd -R' is equivalent to 'sqlreload'; you can only directly
> specify '-i' on the command line with one of those two options.
> However, there's nothing to stop you using a LOAD or RELOAD statement.
> If you have v77.03 of SQLCMD, you can specify the format in the LOAD
> statement (but not RELOAD - that's a simple oversight bug fixed in 77.05
> which should be available on Monday):
>
> LOAD FROM "input.file" FORMAT "csv" INSERT INTO TargetTable;>
> Or:
>
> sqlcmd -R -d database -t targtetable -F csv -i input.file
Thanks for your explanation.
I have now installed sqlcmd-77.03 (and look from time to time on
www.iiug.org for a newer version because double quotes) and have the
next problem:
How can i insert only a complete file ? That means, when no error
occurs, all rows are inserted, when a error occurs, no rows were
inserted.
But:
1) sqlcmd -R -N 1000000 -d testdb -t testtab -F CSV -i test.csv
(Without option -N there is a default commit after 1024 rows.)
Up to 1234 rows committed successfully
SQL -846: Number of values in load file is not equal to number of
columns.
ISAM -746: Too many values in record
(The error is intention, only for commit/rollback test.)
-> Exists a solution for this syntax ?
2) sqlcmd -d testdb 'begin; LOAD FROM "test.csv" FORMAT "csv" INSERT
INTO testtab; commit;'
SQL -201: A syntax error has occurred.
SQLSTATE: 42000
(The same without commit.)
-> Multiple statements are not allowed ? The same with option "-e".
3) sqlcmd -d testdb -f sqlcmd.sql
SQL -255: Not in transaction.
-> cat sqlcmd.sql
LOAD FROM "test.csv" FORMAT "csv" INSERT INTO testtab;4) sqlcmd -d testdb -f sqlcmd.sql
Bus Error - core dumped
-> cat sqlcmd.sq
begin;
LOAD FROM "test.csv" FORMAT "csv" INSERT INTO testtab;commit;
(The same without commit.)
Hmm, how can i do a rollback in a error scenario ? No, I do not have a
control of the files, because the files comes from extern...
There were no errors during compiling.
Informixserver version: 9.30.UC5 on Solaris 8
SDK version : 2.81.UC2 on Solaris 8
Suggestions ?
Regards,
try_and_err