Re: how do i load the data into a informix db table name with casesensitive and column name with space?

On Wed, Jul 9, 2008 at 8:02 AM, Anthony Judish <ajudish@lextron-inc.com> wrote: > From: Jonathan Leffler > > 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. And Anthony Judish responded: > 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? It depends on the scenario - that works when you don't need to name the columns, so maybe the original case would be OK, though it is far from guaranteed, for a simple LOAD FROM "file" INSERT INTO Table (with no spaces needed in the table name). But with spaces in the table name, I don't think things are going to work. Take this superbly extreme table: Black JL: cat quotedtable.sql CREATE TABLE """""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""" . """""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""" ( " " CHAR(1) NOT NULL, "/" DATE NOT NULL, "He said, ""Don't do it!""" DATETIME YEAR TO SECOND NOT NULL, "@(#)$Id$" VARCHAR(32) NOT NULL ); LOAD FROM "quotedtable.unl" INSERT INTO"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""" . """"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""; INSERT INTO """""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""" . """"""""""""""""""""""""""""""""""""""""@@DQ