how do i load the data into a informix db table name with case sensitive and column name with space?
jeffhan
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.
DL Redden
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
Jonathan Leffler
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: jlef...@earthlink.net, jlef...@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
Obnoxio The Clown
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
<obn...@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>
Best regards
Christian
--
#include <std_disclaimer.h>
/* The opinions stated above are my own and not
necessarily those of my employer. */
Obnoxio The Clown
This wasn't a SAP system, it was a port from Oracle.
Christian Knappke
> This wasn't a SAP system, it was a port from Oracle.
Yes, otherwise it woudn't be a problem ;-)
I used the SAP thing only as an illustration for the use of
DELIMDENT in a more or less widely known application.
Anthony Judish
Anthony Judish
Software Design Architect
Lextron Inc.
ph 970.378.2056
fax 970.346.2356
aju...@lextron-inc.com
_______________________________________________
Informix-list mailing list
Inform...@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
aju...@lextron-inc.com
Jonathan Leffler
<obn...@serendipita.com> wrote:
> 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 1
Near 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: jlef...@earthlink.net, jlef...@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.
Jonathan Leffler
> From: Jonathan Leffler
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
"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""" .
""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
(" ", "/", "He said, ""Don't do it!""", "@(#)$Id$") VALUES ('Y',
TODAY, CURRENT YEAR TO SECOND, 'How
thrilling!');
DROP TABLE """""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""" .
"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""";
Black JL:
That is, the owner's name is 32 double quotes (represented by 66
double quotes in the CREATE TABLE statement), and the table's name is
128 double quotes (represented by 258 double quotes) all on a single
line in the original. The column names are interesting too. Both
DB-Access and SQLCMD have problems with the load statement - for the
same basic reason - and both handle the other statements fine, as long
as DELIMIDENT is set.
Don't bother to try exporting that table with DB-Export (or importing
it with DB-Import).
I also have a script to create a table with 32,767 columns (all
CHAR(1), of course) in it. Beware, it takes a long time to execute
and needs a lot of logical log space too. And DB-Export and DB-Import
can't handle that either. Actually, neither can SQLCMD; the
statements needed are generally longer than 64 KB (think about it -
INSERT INTO VALUES(?,?,..32760 times,?,?,?,?,?) - and you'll see what
I mean; SELECT * does work, of course), and that limit is imposed by
the server.
--
Jonathan Leffler #include <disclaimer.h>
Email: jlef...@earthlink.net, jlef...@us.ibm.com