syscs_util.syscs_import_data error

["McColley, William S" <Wi...@sun.com>]

Anyone experienced this before?

 

table definition is:

ij> create table INDIGO.ATTRIBUTE

(ATTRIBUTE_ID SMALLINT NOT NULL ,

ATTRIBUTE_PARENT_ENTITY_ID SMALLINT NOT NULL,

ATTRIBUTE_INTERNAL_NAME VARCHAR(50),

ATTRIBUTE_EXTERNAL_NAME VARCHAR(50),

ATTRIBUTE_MAX_WIDTH SMALLINT,

ATTRIBUTE_EDITABLE SMALLINT,

ATTRIBUTE_HAS_SELECTIONS SMALLINT,

ATTRIBUTE_GLOBAL_SETTINGS_ID SMALLINT,

ATTRIBUTE_LIST_ALLOWED SMALLINT,

ATTRIBUTE_ENTITY_ID SMALLINT,

ATTRIBUTE_IDENTIFIES_ENTITY SMALLINT,

ATTRIBUTE_TYPE_ID SMALLINT,

FOREIGN KEY(ATTRIBUTE_PARENT_ENTITY_ID) REFERENCES
INDIGO.ENTITY(ENTITY_ID),

FOREIGN KEY(ATTRIBUTE_GLOBAL_SETTINGS_ID)REFERENCES
INDIGO.GLOBAL_SETTINGS(GLOBAL_SETTINGS_ID),

FOREIGN KEY(ATTRIBUTE_ENTITY_ID)REFERENCES INDIGO.ENTITY(ENTITY_ID),

FOREIGN KEY(ATTRIBUTE_TYPE_ID)REFERENCES
INDIGO.ATTRIBUTE_TYPE(ATTRIBUTE_TYPE_ID),PRIMARY KEY(ATTRIBUTE_ID))

 

input file, 1 record:
/export/wsm/work_area/extract/data/05_attribute.dat2

 

2;2;'alternatereadsallowed';'alternatereadsallowed';0;0;0;NULL;0;NULL;0;
2

 

ij> call syscs_util.syscs_import_data ('INDIGO','ATTRIBUTE',null,
null,'/export/wsm/work_area/extract/data/05_attribute.dat2',';',null,nul
l,1);

ERROR 38000: The exception 'SQL Exception: Invalid character string
format for type SMALLINT.' was thrown while evaluating an expression.

ERROR 22018: Invalid character string format for type SMALLINT.

ij>

 

If I take the same string and wrap an insert statement around it:

 

ij> insert into INDIGO.ATTRIBUTE values
(2,2,'alternatereadsallowed','alternatereadsallowed',0,0,0,NULL,0,NULL,0
,2);

 

1 row inserted/updated/deleted

ij>

 

This is occurring on about 10% of the tables I try to import

 

I can't see a reason for this operation to error - the data is in fact
valid.

Any help would be greatly appreciated.

Scott McColley

Re: syscs_util.syscs_import_data error

[Suresh Thalamati <su...@gmail.com>]

McColley, William S wrote:
> Anyone experienced this before?
> 
>  
> 
> table definition is:
> 
> ij> create table INDIGO.ATTRIBUTE
> 
> (ATTRIBUTE_ID SMALLINT NOT NULL ,
> 
> ATTRIBUTE_PARENT_ENTITY_ID SMALLINT NOT NULL,
> 
> ATTRIBUTE_INTERNAL_NAME VARCHAR(50),
> 
> ATTRIBUTE_EXTERNAL_NAME VARCHAR(50),
> 
> ATTRIBUTE_MAX_WIDTH SMALLINT,
> 
> ATTRIBUTE_EDITABLE SMALLINT,
> 
> ATTRIBUTE_HAS_SELECTIONS SMALLINT,
> 
> ATTRIBUTE_GLOBAL_SETTINGS_ID SMALLINT,
> 
> ATTRIBUTE_LIST_ALLOWED SMALLINT,
> 
> ATTRIBUTE_ENTITY_ID SMALLINT,
> 
> ATTRIBUTE_IDENTIFIES_ENTITY SMALLINT,
> 
> ATTRIBUTE_TYPE_ID SMALLINT,
> 
> FOREIGN KEY(ATTRIBUTE_PARENT_ENTITY_ID) REFERENCES INDIGO.ENTITY(ENTITY_ID),
> 
> FOREIGN KEY(ATTRIBUTE_GLOBAL_SETTINGS_ID)REFERENCES 
> INDIGO.GLOBAL_SETTINGS(GLOBAL_SETTINGS_ID),
> 
> FOREIGN KEY(ATTRIBUTE_ENTITY_ID)REFERENCES INDIGO.ENTITY(ENTITY_ID),
> 
> FOREIGN KEY(ATTRIBUTE_TYPE_ID)REFERENCES 
> INDIGO.ATTRIBUTE_TYPE(ATTRIBUTE_TYPE_ID),PRIMARY KEY(ATTRIBUTE_ID))
> 
>  
> 
> input file, 1 record: /export/wsm/work_area/extract/data/05_attribute.dat2
> 
>  
> 
> 2;2;'alternatereadsallowed';'alternatereadsallowed';0;0;0;NULL;0;NULL;0;2
> 
>  
> 
> ij> call syscs_util.syscs_import_data ('INDIGO','ATTRIBUTE',null, 
> null,'/export/wsm/work_area/extract/data/05_attribute.dat2',';',null,null,1);
> 
> ERROR 38000: The exception 'SQL Exception: Invalid character string 
> format for type SMALLINT.' was thrown while evaluating an expression.
> 
> ERROR 22018: Invalid character string format for type SMALLINT.
> 
> ij>
> 
>  
> 
> If I take the same string and wrap an insert statement around it:
> 
>  
> 
> ij> insert into INDIGO.ATTRIBUTE values 
> (2,2,'alternatereadsallowed','alternatereadsallowed',0,0,0,NULL,0,NULL,0,2);
> 
>  
> 
> 1 row inserted/updated/deleted
> 
> ij>
> 
>  
> 
> This is occurring on about 10% of the tables I try to import
> 
>  
> 
> I can’t see a reason for this operation to error – the data is in fact 
> valid.
> 
> Any help would be greatly appreciated.
> 
> Scott McColley
> 

I think the problem is with NULL strings in the input data, the error 
is because import is attempting to convert NULL string to the 
SMALLINT.  An empty field is treated as NULL value by the derby import.

valid input data should be:
2;2;'alternatereadsallowed';'alternatereadsallowed';0;0;0;;0;;0;2
instead of
2,2,'alternatereadsallowed','alternatereadsallowed',0,0,0,NULL,0,NULL,0,2

See:
http://db.apache.org/derby/docs/dev/tools/ctoolsimportnulls.html


hope that helps
-suresh