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

try_and_err wrote: > 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) I sent it to the IIUG, but haven't had a confirmation that it is up. Drop me an email from the account you want me to send it too if you'd rather have it more quickly. > 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 ? Haven't had that as an issue before - I'll think of what seems like a sensible solution (transaction size zero? There's precedent; I'm not sure I like it, though) and implement it. > 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.) Now that's interesting - and the behaviour is what I'd expect (though that is not obvious at first sight). SQLCMD looks at the code in the string, sees that it does not start with LOAD, UNLOAD, RELOAD, INFO or with one of its many built-ins, and sends the whole thing to the server. The server doesn't understand LOAD statements, so it generates the syntax error. sqlcmd -d testdb -e begin -e 'load from "test.csv" format "csv" insert into testtab' -e commit Trick: if you omit the '-e' options, it would almost work - but you'd have to have 'begin work' and 'commit work' since sqlcmd uses a simple heuristic -- file names don't have spaces in them and SQL commands do. > -> Multiple statements are not allowed ? The same with option "-e". > 3) sqlcmd -d testdb -f sqlcmd.sql > SQL -255: Not in transaction. That one's a nuisance, but correct. It's one of the reasons RELOAD exists. > -> cat sqlcmd.sql > LOAD FROM "test.csv" FORMAT "csv" INSERT INTO testtab;> 4) sqlcmd -d testdb -f sqlcmd.sql > Bus Error - core dumped That is not supposed to happen. Core dumps are never supposed to happen. This isn't because of commas in the data, is it? > -> cat sqlcmd.sq > begin; > LOAD FROM "test.csv" FORMAT "csv" INSERT INTO testtab;> commit; > (The same without commit.) That should work - and without the commit, it should rollback at the end. That core dumps? I guess I should do some checking on the error handling in LOAD and RELOAD. [...I dn't see anything equivalent to DB-Load's "-e 30" option which permits 30 errors to occur; that's in SQLUPLOAD, but that's a wholly different beast...] > 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... If the transaction is started, then leaving off the commit should mean that the data is rolled out again. If that's not happening, let me know, preferably with a sample table and data file that reproduces the problem. Look at massaging the files into LOAD format before processing them? I mentioned csv2unl.pl before, I believe. > There were no errors during compiling. > Informixserver version: 9.30.UC5 on Solaris 8 > SDK version : 2.81.UC2 on Solaris 8 I use a newer server (and CSDK) these days, but the o/s is the same. I would not expect to see any errors there - I don't on any platform, but especially not on Solaris. > Suggestions ? Maybe look at Marco Greco's code? I'm very disappointed that you're having so much trouble - but I've never really stressed CSV format; it is very much a minority concern in the Informix world, and was added to SQLCMD more as a challenge than because of a big perceived need. That said, it is most certainly intended to work. Now, if the trouble is commas within quoted strings or other ghastly CSV nonsense, I feel less concern about it - v77.03 is known not to handle that correctly (and I think v77.05 does handle them correctly, though I may be being too optimistic). -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.01 -- http://dbi.perl.org/

Jonathan Leffler <jleffler@earthlink.net> wrote in message news:<424A4DD6.9060106@earthlink.net>... > I sent it to the IIUG, but haven't had a confirmation that it is up. > Drop me an email from the account you want me to send it too if you'd > rather have it more quickly. I get dozens "important Windows update" and other "very important" emails and you don't know my email adress ??? ;-) OK, i have received your email(s) and and I will try it out. > That is not supposed to happen. Core dumps are never supposed to > happen. This isn't because of commas in the data, is it? No (sorry). For example: sqlcmd -d testdb -e begin -e 'select count(*) from systables' Bus Error - core dumped (This is also with sqlcmd 77.05) It seems there is a problem with 'begin'/'begin work' (?). I will try this tomorrow on Linux and on an other Solaris machine. > Maybe look at Marco Greco's code? I'm very disappointed that you're > having so much trouble - but I've never really stressed CSV format; it > is very much a minority concern in the Informix world, and was added to > SQLCMD more as a challenge than because of a big perceived need. That > said, it is most certainly intended to work. Now, if the trouble is > commas within quoted strings or other ghastly CSV nonsense, I feel less > concern about it - v77.03 is known not to handle that correctly (and I > think v77.05 does handle them correctly, though I may be being too > optimistic). Should i know what you mean with "Marco Greco's code" ? 'vertmenu' ? I am very pleased over your assistance, i did not want that you are disappointed. 'sqlcmd' is a nice tool, when you could not test it on Solaris i try to test it for you. ;-) Thanks again. Regards, try_and_err

try_and_err wrote: > Jonathan Leffler <jleffler@earthlink.net> wrote in message news:<424A4DD6.9060106@earthlink.net>... >>That is not supposed to happen. Core dumps are never supposed to >>happen. This isn't because of commas in the data, is it? > > No (sorry). For example: > sqlcmd -d testdb -e begin -e 'select count(*) from systables' > Bus Error - core dumped > (This is also with sqlcmd 77.05) > It seems there is a problem with 'begin'/'begin work' (?). > I will try this tomorrow on Linux and on an other Solaris machine. I believe we're making progress - v77.05 doesn't seem to crash after all. I've provided some more fixes off-line; in due course, there will be a new release to the IIUG web site. (Peter - thanks for getting v77.05 up onto the IIUG Software Repository!) -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.01 -- http://dbi.perl.org/