Re: how do i load the data into a informix db table name with

On Tue, Jul 8, 2008 at 10:26 PM, Obnoxio The Clown <obnoxio@serendipita.com> wrote: > > Jonathan Leffler said: >> jeffhan wrote: >>> i have a database which has sensitive table name and column name (some >>> columns name even have space in it), like xtreme."Account" >>> column: "Account Heading Number", "Account Number" >>> >>> i was trying to use dbaccess to load the data into 1 table but failed, >>> here is what i did: >>> >>> $cat test.sql >>> LOAD FROM 'test.data' DELIMITER '|' INSERT INTO>>> xtreme2003."Account"("Account Number" ,"Account Heading >>> Number" ,"Account Type ID" ,"Account Class ID" ,"Account >>> Name" ,"Description" ,"Account Balance" ); >>> >>> bash-3.1$ head -3 test.data >>> 1060|1000|1|2|Chequing Bank Account||893260.5187| >>> 1200|1000|1|4|Accounts Receivable||513702.3068| >>> 1510|1500|1|5|Gloves Inventory||49078.35| >>> >>> And then i run dbaccess, database name is xtreme2003: >>> $ dbaccess xtreme2003 test.sql>>> >>> Database selected. >>> >>> >>> 404: The cursor or statement is not available.>>> >>> 404: The cursor or statement is not available. >>> Error in line 1>>> Near character position 1 >>> >>> Database closed. >>> >>> $ >>> >>> i just can't understand why i got this error, please help me thanks. >> >> Informix doesn't use delimited identifiers unless you force its hand. >> You force its hand by setting DELIMIDENT=1 in the environment. > > Far be it from me to disagree with such an august personage, but in my > limited experience, I found that DELIMIDENT was actually very picky about > what you set it to, and if my considerably addled memory serves me > correctly, it had to be set to "Y". > > This may or may not be intended behaviour. :o) If you're going to disagree with me, you should at least produce a demonstration of your point, preferably before you send your message. It saves embarassment later. :D My counter-viewpoint is illustrated by: Black JL: DELIMIDENT= sqlcmd -d stores SQL[2834]: select "abc" from dual; SQL -217: Column (abc) not found in any table in the query (or SLV is undefined). SQLSTATE: IX000 at /dev/stdin:1 SQL[2835]: q; Black JL: DELIMIDENT= dbaccess stores - <<! > select "abc" from dual; > ! Database selected. 217: Column (abc) not found in any table in the query (or SLV is undefined). Error in line 1Near character position 14 Database closed. Black JL: sqlcmd -d stores -e "select dbinfo('version','full') from dual" IBM Informix Dynamic Server Version 11.50.FC1 Black JL: dbaccess stores - <<!^Jselect "abc" from dual;^J! Database selected. (constant) abc 1 row(s) retrieved. Database closed. Black JL: sqlcmd -d stores -e 'select "abc" from dual' abc Black JL: I didn't set DELIMIDENT to a value - it just had to be in the environment. And yes, I do have a table called dual - I'd be getting a different error if I didn't. Now, I will concede that this does not test Windows, nor any other API than ESQL/C - and those factors might affect your results. Nevertheless, on Unix (IDS 11.50.FC1 on Solaris 10) with ESQL/C 3.50.FC1, you only need DELIMIDENT in the environment - the value is immaterial, and Y and N and Yes and no and pink_elephant all work equally well. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/ "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." NB: Please do not use this email for correspondence. I don't necessarily read it every week, even.