how do i load the data into a informix db table name with case sensitive and column name with space?

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 INTOxtreme2003."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 1Near character position 1 Database closed. $ i just can't understand why i got this error, please help me thanks.

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. Actually, the value doesn't matter; the variable just has to be in the environment. Then you have to be careful to use double-quotes around identifiers and single quotes around strings. Normally, Informix lets you use either single or double quotes around strings, and many people and (historically) many programs used double quotes. If it still doesn't work, you may need to get hold of SQLCMD from the IIUG Software Archive. On average, though, it should be OK. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2008.0229 -- http://dbi.perl.org/ publictimestamp.org/ptb/PTB-3685 tiger128 2008-07-09 03:00:05 3935AD3431CD30F1AFA70C519FD9AC38

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) -- Bye now, Obnoxio http://obotheclown.blogspot.com/

\

Christian Knappke said: >>From the keyboard of "Obnoxio The Clown" > <obnoxio@serendipita.com>: > >> >> Jonathan Leffler said: >>> >>> 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) > > It depends. > > In SAP systems indices are usually named <tabname>~<#>. Also there > are so called namespaces for repository and data dictionary > objects which follow the schema /<namespace>/<object name>. > > As the tilde and the forward slash are more or less unususal > characters in an identifier, the environment of the SAP kernel > must have set DELIMIDENT=y. IDS runs without DELIMIDENT in an SAP > environment (except on Windows). > See <http://service.sap.com/sap/support/notes/306037>; > > The server is just looking for presence of DELIMIDENT and sets it > for all sessions. > > See > <http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp? > topic=/com.ibm.sqls.doc/sqls1077.htm> This wasn't a SAP system, it was a port from Oracle. -- Bye now, Obnoxio http://obotheclown.blogspot.com/

\

Anthony Judish Software Design Architect Lextron Inc. ph 970.378.2056 fax 970.346.2356 ajudish@lextron-inc.com -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of Jonathan Leffler Sent: Tuesday, July 08, 2008 11:10 PM To: informix-list@iiug.org Subject: Re: how do i load the data into a informix db table name with casesensitive and column name with space? 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. Actually, the value doesn't matter; the variable just has to be in the environment. Then you have to be careful to use double-quotes around identifiers and single quotes around strings. Normally, Informix lets you use either single or double quotes around strings, and many people and (historically) many programs used double quotes. If it still doesn't work, you may need to get hold of SQLCMD from the IIUG Software Archive. On average, though, it should be OK. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2008.0229 -- http://dbi.perl.org/ publictimestamp.org/ptb/PTB-3685 tiger128 2008-07-09 03:00:05 3935AD3431CD30F1AFA70C519FD9AC38 _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list What's wrong with just doing Load from "test.dat" insert into xtreme2003 I have used this syntax for years in various versions (7.3x, 9.4, 10.x, currently 11.1) with no problem. We do not have DELIMIDENT set. Doe Informix not default to a pipe (|) delimiter? Anthony Judish Software Design Architect Lextron Inc. ph 970.378.2056 fax 970.346.2356 ajudish@lextron-inc.com