How to insert multiple lines in lvarchar column
Informix Version : V12.FC1
Platform : Linux Susue 11
Table structure:
create table abc (
id varchar(36) not null,
desc varchar(50),
java_script lvarchar(4000),
other_script lvarchar(4000),
have_script smallint
)
I have following sample flat file (migrating from oracle) and trying to insert
into above table. The sample file only consist of 1 record for testing purpose
and delimiter by "#" sign.
4028832a3b25aa34013b268d4d550609#InHouseTransfer#import java.util.*;
import java.math.*;
List nl=(List)ctx.etVar("NotificationList");
BiDecimal totalChares=new BiDecimal(0);
BiDecimal rateTemp=new BiDecimal(0);
Map currData=new HashMap();
for(int i=0;i<nl.size();i++) {
List detailList=(List)nl.et(i);
currData=(HashMap)detailList.et(0);
currData.put("toFAX",(Strin)detailList.et(1));
Strin phone=(Strin)currData.et("toFAX");
if(phone.indexOf(";") > -1) {
phone = phone.replaceAll(";",",");
}
int totals = 0;
StrinTokenizer st1 = new StrinTokenizer(phone, ",", true);
while(st1.hasMoreElements()){
Strin value = st1.nextElement().toStrin();
if(value.equals(",") == false){
totals = totals + 1;
}
}
BiDecimal totalCount = new BiDecimal(totals);
BiDecimal totalEquivalent = new BiDecimal(0);
BiDecimal chareType1Equivalent =
(BiDecimal)currData.et("chareNotiType1Equivalent");
if(chareType1Equivalent.compareTo(new BiDecimal(0))!=0) {
totalEquivalent = totalEquivalent.add(chareType1Equivalent);
rateTemp=(BiDecimal)currData.et("chareNotiType1ExRate");
}
###
When run the load command with syntax
echo "load from flat_file delimiter "#" insert into abc" | dbaccess dbname
I got the following error:
Database selected.
846: Number of values in load file is not equal to number of columns.
847: Error in load file row 1.
Error in line 2Near character position 0
Database closed.
I did the checking of number of column and it is tally.
Questions
---------
1/ I would like to know if there is any where that I can force column with
data type lvarchar to stored a script (with multiple lines)?
2/ If I were to replace the lvarchar(4000) above with text data type, the
loading is successful. So, does these means that lvarchar datatype is not able
to stored multiple lines?
Thanks, Patrick
it will work if you add a "\\\\" in the end of each line where the next one
belongs to the same record:
As an example:
4028832a3b25aa34013b268d4d550609#InHouseTransfer#import java.util.*;\\\\
import java.math.*;\\\\
}\\\\
###
castelo@base-00:informix-> cat test.unl;dbaccess stores7 test.sql;
4028832a3b25aa34013b268d4d550609#InHouseTransfer#import java.util.*;\\\\
import java.math.*;\\\\
}\\\\
###
Database selected.
1 row(s) loaded.
id 4028832a3b25aa34013b268d4d550609
desc InHouseTransfer
java_script import java.util.*;
import java.math.*;
}
other_script
have_script
1 row(s) retrieved.
Database closed.
castelo@base-00:informix->
Regards
On Mon, Oct 28, 2013 at 2:32 PM, LEY PATRICK <patrickley@gmail.com> wrote:
> Informix Version : V12.FC1
> Platform : Linux Susue 11
>
> Table structure:
>
> create table abc (>
> id varchar(36) not null,
>
> desc varchar(50),
>
> java_script lvarchar(4000),
>
> other_script lvarchar(4000),
>
> have_script smallint
> )
>
> I have following sample flat file (migrating from oracle) and trying to
> insert
> into above table. The sample file only consist of 1 record for testing
> purpose
> and delimiter by "#" sign.
>
> 4028832a3b25aa34013b268d4d550609#InHouseTransfer#import java.util.*;
> import java.math.*;
>
> List nl=(List)ctx.etVar("NotificationList");
>
> BiDecimal totalChares=new BiDecimal(0);
> BiDecimal rateTemp=new BiDecimal(0);
> Map currData=new HashMap();
>
> for(int i=0;i<nl.size();i++) {
>
> List detailList=(List)nl.et(i);
>
> currData=(HashMap)detailList.et(0);
>
> currData.put("toFAX",(Strin)detailList.et(1));
>
> Strin phone=(Strin)currData.et("toFAX");
>
> if(phone.indexOf(";") > -1) {
>
> phone = phone.replaceAll(";",",");
>
> }
>
> int totals = 0;
>
> StrinTokenizer st1 = new StrinTokenizer(phone, ",", true);
>
> while(st1.hasMoreElements()){
>
> Strin value = st1.nextElement().toStrin();
>
> if(value.equals(",") == false){
>
> totals = totals + 1;
>
> }
>
> }
>
> BiDecimal totalCount = new BiDecimal(totals);
>
> BiDecimal totalEquivalent = new BiDecimal(0);
>
> BiDecimal chareType1Equivalent =
> (BiDecimal)currData.et("chareNotiType1Equivalent");
>
> if(chareType1Equivalent.compareTo(new BiDecimal(0))!=0) {
>
> totalEquivalent = totalEquivalent.add(chareType1Equivalent);
>
> rateTemp=(BiDecimal)currData.et("chareNotiType1ExRate");
>
> }
> ###
>
> When run the load command with syntax
> echo "load from flat_file delimiter "#" insert into abc" | dbaccess dbname
>
> I got the following error:
>
> Database selected.
>
> 846: Number of values in load file is not equal to number of columns.>
> 847: Error in load file row 1.
> Error in line 2> Near character position 0
>
> Database closed.
>
> I did the checking of number of column and it is tally.
>
> Questions
> ---------
> 1/ I would like to know if there is any where that I can force column with
> data type lvarchar to stored a script (with multiple lines)?
> 2/ If I were to replace the lvarchar(4000) above with text data type, the
> loading is successful. So, does these means that lvarchar datatype is not
> able
> to stored multiple lines?
>
> Thanks, Patrick
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--001a11c1ff44a509d404e9ff55b6