insert/load data into char/varchar data type
Asker wanted IDS to raise an error instead of silently truncating strings longer than a char/varchar column, both for INSERT and LOAD. John Miller suggested checking the sqlwarn flag in an application, but Art Kagel tested it in ESQL/C and showed no warning or sqlcode is set on insert truncation. The real answer: truncation is only an error (-1279) in ANSI-mode databases; otherwise you must widen columns, validate input in the application, or DESCRIBE/prepare the statement and check column lengths yourself. dbaccess, sqlcmd and HPL won't flag it; Jonathan Leffler noted such a check could be added to sqlcmd.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi P'tew ,
I have a question about char/varchar data type when we insert/load data with
the data length wider than column length.
How can I trap this error if I don't want automatically trucate by IDS ?
I 've tried char/varchar . It's the same, no error.
Best Regards,
create table tmp2 (col1 char(2));
insert into tmp2 values ("111");
insert into tmp2 values ("123");
insert into tmp2 values ("321");
select * from tmp2;
col1
----
11
12
32
---------------------------------------------------------------
$ cat test.txt
111|
123|
321|
create table tmp2 (col1 char(2));
load from test.txt insert into tmp2 ;
select * from tmp2;
col1
----
11
12
32
If you write your own application you can check sqlwarn2 flag to
determine if the data has been truncated.
John F. Miller III
STSM, Support Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 03/18/2009 08:19:29 PM:
> Hi P'tew ,
>
> I have a question about char/varchar data type when we insert/load data
with
> the data length wider than column length.
> How can I trap this error if I don't want automatically trucate by IDS ?
>
> I 've tried char/varchar . It's the same, no error.
>
> Best Regards,
>
> create table tmp2 (col1 char(2));
> insert into tmp2 values ("111");
> insert into tmp2 values ("123");
> insert into tmp2 values ("321");
> select * from tmp2;>
> col1
> ----
> 11
> 12
> 32
> ---------------------------------------------------------------
>
> $ cat test.txt
> 111|
> 123|
> 321|>
> create table tmp2 (col1 char(2));
> load from test.txt insert into tmp2 ;
> select * from tmp2;>
> col1
> ----
> 11
> 12
> 32
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Thank you very much.
Can I set some parameter to raise an error when insert/load data ?
Normally , We use dbaccess,HPL,ESQL/C, Java.
My user confuse many time because their data has been adjust automatically (We
don't want this).
________________________________
From: John Miller iii <miller3@us.ibm.com>
To: ids@iiug.org
Sent: Thursday, March 19, 2009 10:36:20 AM
Subject: Re: insert/load data into char/varchar data type [15229]
If you write your own application you can check sqlwarn2 flag to
determine if the data has been truncated.
John F. Miller III
STSM, Support Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 03/18/2009 08:19:29 PM:
> Hi P'tew ,
>
> I have a question about char/varchar data type when we insert/load data
with
> the data length wider than column length.
> How can I trap this error if I don't want automatically trucate by IDS ?
>
> I 've tried char/varchar . It's the same, no error.
>
> Best Regards,
>
> create table tmp2 (col1 char(2));
> insert into tmp2 values ("111");
> insert into tmp2 values ("123");
> insert into tmp2 values ("321");
> select * from tmp2;>
> col1
> ----
> 11
> 12
> 32
> ---------------------------------------------------------------
>
> $ cat test.txt
> 111|
> 123|
> 321|>
> create table tmp2 (col1 char(2));
> load from test.txt insert into tmp2 ;
> select * from tmp2;>
> col1
> ----
> 11
> 12
> 32
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
That's because truncation on insert is not an error EXCEPT if the database
is in ANSI log mode. An ANSI database would return a -1279 error if the
inserted string is longer than the column into which it is inserted can
hold. If you PREPARE an insert with a host language program, either with
ESQL/C or ODBC (or one of its decendents like JDBC) you can DESCRIBE the
insert statement and use the information placed into descriptor area or the
sqlda structure to check whether the value you will be inserting will be
truncated. Not dbaccess, sqlcmd, or any other SQL tool that I am aware of
will perform this check for you. All will silently insert the truncated
string.
Art
On Wed, Mar 18, 2009 at 11:19 PM, jak aka <jakkritakeng@yahoo.com> wrote:
> Hi P'tew ,
>
> I have a question about char/varchar data type when we insert/load data
> with
> the data length wider than column length.
> How can I trap this error if I don't want automatically trucate by IDS ?
>
> I 've tried char/varchar . It's the same, no error.
>
> Best Regards,
>
> create table tmp2 (col1 char(2));
> insert into tmp2 values ("111");
> insert into tmp2 values ("123");
> insert into tmp2 values ("321");
> select * from tmp2;>
> col1
> ----
> 11
> 12
> 32
> ---------------------------------------------------------------
>
> $ cat test.txt
> 111|
> 123|
> 321|>
> create table tmp2 (col1 char(2));
> load from test.txt insert into tmp2 ;
> select * from tmp2;>
> col1
> ----
> 11
> 12
> 32
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
--00163646d97954fd3f046577ece8
I don't think so John. The ESQL/C manual says:
sqlwarn1:
Set to W if a column value is truncated when it is fetched into a host
variable using a FETCH or a SELECT...INTO statement. On a REVOKE ALL
statement, set to W when not all seven table-level privileges are revoked.
That doesn't mention INSERTs and on pages talking about inserting to CHAR
and VARCHAR columns it states:
If the value is longer than the size of the column the database server
truncates the value if the database is non-ANSI. No warning is generated
when this truncation occurs. If the database is ANSI and the value is longer
than the column size then the insert fails and this error is returned:
-1279: Value exceeds string column length.
Wrote this test app:
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
exec sql include sqlca;
exec sql begin declare section;
string fred[100];
exec sql end declare section;
main() {
exec sql database 'art';
exec sql drop table lentest;
exec sql create table lentest( one char(10) );
strcpy( fred, "This string is 34 characters long." );
printf( "Inserting: <%s>.\\
", fred );
exec sql insert into lentest values (:fred);
printf( "sqlcode: %d, isam: %d, sqlwarn[1]: %c.\\
",
sqlca.sqlcode,
sqlca.sqlerrd[1],
sqlca.sqlwarn.sqlwarn1 );
memset( fred, 0, 100 );
exec sql select first 1 one into :fred from lentest;
printf( "Read back: <%s>.\\
", fred );
}
Which returned the following when run:
$ ./tst
Inserting: <This string is 34 characters long.>.
sqlcode: 0, isam: 0, sqlwarn[1]: .
Read back: <This strin>.
$
Art
On Wed, Mar 18, 2009 at 11:36 PM, John Miller iii <miller3@us.ibm.com>wrote:
> If you write your own application you can check sqlwarn2 flag to
> determine if the data has been truncated.
>
> John F. Miller III
> STSM, Support Architect
> miller3@us.ibm.com
> 503-578-5645
> IBM Informix Dynamic Server (IDS)
>
> ids-bounces@iiug.org wrote on 03/18/2009 08:19:29 PM:
>
> > Hi P'tew ,
> >
> > I have a question about char/varchar data type when we insert/load data
> with
> > the data length wider than column length.
> > How can I trap this error if I don't want automatically trucate by IDS ?
> >
> > I 've tried char/varchar . It's the same, no error.
> >
> > Best Regards,
> >
> > create table tmp2 (col1 char(2));
> > insert into tmp2 values ("111");
> > insert into tmp2 values ("123");
> > insert into tmp2 values ("321");
> > select * from tmp2;> >
> > col1
> > ----
> > 11
> > 12
> > 32
> > ---------------------------------------------------------------
> >
> > $ cat test.txt
> > 111|
> > 123|
> > 321|> >
> > create table tmp2 (col1 char(2));
> > load from test.txt insert into tmp2 ;
> > select * from tmp2;> >
> > col1
> > ----
> > 11
> > 12
> > 32
> >
> >
> >
>
>
>
*******************************************************************************
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
--00163646d6a66fc16704657845d7
On Thu, Mar 19, 2009 at 12:06 AM, jak aka <jakkritakeng@yahoo.com> wrote:
> Thank you very much.
>
> Can I set some parameter to raise an error when insert/load data ?
> Normally , We use dbaccess,HPL,ESQL/C, Java.
> My user confuse many time because their data has been adjust automatically
> (We
> don't want this).
Then a) make your columns wider and b) limit the inputs to the application.
There is nothing you can do about it. John is wrong on this one, there IS
NO warning.
Art
>
>
> ________________________________
> From: John Miller iii <miller3@us.ibm.com>
> To: ids@iiug.org
> Sent: Thursday, March 19, 2009 10:36:20 AM
> Subject: Re: insert/load data into char/varchar data type [15229]
>
> If you write your own application you can check sqlwarn2 flag to
> determine if the data has been truncated.
>
> John F. Miller III
> STSM, Support Architect
> miller3@us.ibm.com
> 503-578-5645
> IBM Informix Dynamic Server (IDS)
>
> ids-bounces@iiug.org wrote on 03/18/2009 08:19:29 PM:
>
> > Hi P'tew ,
> >
> > I have a question about char/varchar data type when we insert/load data
> with
> > the data length wider than column length.
> > How can I trap this error if I don't want automatically trucate by IDS ?
> >
> > I 've tried char/varchar . It's the same, no error.
> >
> > Best Regards,
> >
> > create table tmp2 (col1 char(2));
> > insert into tmp2 values ("111");
> > insert into tmp2 values ("123");
> > insert into tmp2 values ("321");
> > select * from tmp2;> >
> > col1
> > ----
> > 11
> > 12
> > 32
> > ---------------------------------------------------------------
> >
> > $ cat test.txt
> > 111|
> > 123|
> > 321|> >
> > create table tmp2 (col1 char(2));
> > load from test.txt insert into tmp2 ;
> > select * from tmp2;> >
> > col1
> > ----
> > 11
> > 12
> > 32
> >
> >
> >
>
>
>
>
*******************************************************************************
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
--0016364edc12e9d46a04657857df
On Thu, Mar 19, 2009 at 5:27 AM, Art Kagel <art.kagel@gmail.com> wrote:
> That's because truncation on insert is not an error EXCEPT if the database
> is in ANSI log mode. An ANSI database would return a -1279 error if the
> inserted string is longer than the column into which it is inserted can
> hold. If you PREPARE an insert with a host language program, either with
> ESQL/C or ODBC (or one of its decendents like JDBC) you can DESCRIBE the
> insert statement and use the information placed into descriptor area or the
> sqlda structure to check whether the value you will be inserting will be
> truncated. Not dbaccess, sqlcmd, or any other SQL tool that I am aware of
> will perform this check for you. All will silently insert the truncated
> string.
If someone wanted to add the checking to SQLCMD, it wouldn't be
dreadfully hard to add the functionality.
--
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.