You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user@pig.apache.org by Sarath <sa...@algofusiontech.com> on 2012/04/02 15:58:01 UTC

Unable to store to DB using DBStorage

Hi,

I have a table on Oracle 11g as below -
"TEST"
     name | varchar2(20)
     age | number(3)
     designation | varchar2(20)

I have 2 input files as -
"1.txt"
     tom|33
     michael|34
     harry|37
     sam|33
     peter|36
"2.txt"
     tom|TL
     dick|PL
     harry|PM
     sam|TL
     peter|HR

I have a sample pig script as -
"sample.pig"
     register '/home/sarath/piggybank.jar';
     a = load '1.txt' using PigStorage('|') as (name: chararray, age: int);
     b = load '2.txt' using PigStorage('|') as (name: chararray, desg: 
chararray);
     c = cogroup a by name, b by name;
     d = foreach c generate flatten(a), flatten(b);
     e = foreach d generate $0,$1,$3;
     store e into 'dummy' using 
org.apache.pig.piggybank.storage.DBStorage('oracle.jdbc.driver.OracleDriver','jdbc:oracle:thin:@192.168.1.10:1521:ORCL','system','system','insert 
into TEST');

When I run the sample pig script, it fails with an error message -
Unable to insert record:michael    34    PL
...
Caused by: java.lang.RuntimeException: JDBC error
...
Caused by: java.sql.SQLException: Invalid column index

Can someone help me and let me know what is going wrong? What am I missing?

Regards,
Sarath

Re: Unable to store to DB using DBStorage

Posted by Sarath <sa...@algofusiontech.com>.

Thanks Rakesh. It is working now.
I knew that this has got something to do with the prepared statement. 
But was not getting it correctly.

Thanks & Regards,
Sarath.

On Tuesday 03 April 2012 12:19 AM, rakesh sharma wrote:
> Hi Sarath,
> Try to use prepared statement "Insert into TEST (col1, col2, col3) Values (?, ?, ?)". I am using it for a while and it is working fine for me.
> Thanks,Rakesh
>
>> Date: Mon, 2 Apr 2012 19:28:01 +0530
>> From: sarathchandra.josyam@algofusiontech.com
>> To: user@pig.apache.org
>> Subject: Unable to store to DB using DBStorage
>>
>> Hi,
>>
>> I have a table on Oracle 11g as below -
>> "TEST"
>>       name | varchar2(20)
>>       age | number(3)
>>       designation | varchar2(20)
>>
>> I have 2 input files as -
>> "1.txt"
>>       tom|33
>>       michael|34
>>       harry|37
>>       sam|33
>>       peter|36
>> "2.txt"
>>       tom|TL
>>       dick|PL
>>       harry|PM
>>       sam|TL
>>       peter|HR
>>
>> I have a sample pig script as -
>> "sample.pig"
>>       register '/home/sarath/piggybank.jar';
>>       a = load '1.txt' using PigStorage('|') as (name: chararray, age: int);
>>       b = load '2.txt' using PigStorage('|') as (name: chararray, desg:
>> chararray);
>>       c = cogroup a by name, b by name;
>>       d = foreach c generate flatten(a), flatten(b);
>>       e = foreach d generate $0,$1,$3;
>>       store e into 'dummy' using
>> org.apache.pig.piggybank.storage.DBStorage('oracle.jdbc.driver.OracleDriver','jdbc:oracle:thin:@192.168.1.10:1521:ORCL','system','system','insert
>> into TEST');
>>
>> When I run the sample pig script, it fails with an error message -
>> Unable to insert record:michael    34    PL
>> ...
>> Caused by: java.lang.RuntimeException: JDBC error
>> ...
>> Caused by: java.sql.SQLException: Invalid column index
>>
>> Can someone help me and let me know what is going wrong? What am I missing?
>>
>> Regards,
>> Sarath
>

RE: Unable to store to DB using DBStorage

Posted by rakesh sharma <ra...@hotmail.com>.

Hi Sarath,
Try to use prepared statement "Insert into TEST (col1, col2, col3) Values (?, ?, ?)". I am using it for a while and it is working fine for me.
Thanks,Rakesh

> Date: Mon, 2 Apr 2012 19:28:01 +0530
> From: sarathchandra.josyam@algofusiontech.com
> To: user@pig.apache.org
> Subject: Unable to store to DB using DBStorage
> 
> Hi,
> 
> I have a table on Oracle 11g as below -
> "TEST"
>      name | varchar2(20)
>      age | number(3)
>      designation | varchar2(20)
> 
> I have 2 input files as -
> "1.txt"
>      tom|33
>      michael|34
>      harry|37
>      sam|33
>      peter|36
> "2.txt"
>      tom|TL
>      dick|PL
>      harry|PM
>      sam|TL
>      peter|HR
> 
> I have a sample pig script as -
> "sample.pig"
>      register '/home/sarath/piggybank.jar';
>      a = load '1.txt' using PigStorage('|') as (name: chararray, age: int);
>      b = load '2.txt' using PigStorage('|') as (name: chararray, desg: 
> chararray);
>      c = cogroup a by name, b by name;
>      d = foreach c generate flatten(a), flatten(b);
>      e = foreach d generate $0,$1,$3;
>      store e into 'dummy' using 
> org.apache.pig.piggybank.storage.DBStorage('oracle.jdbc.driver.OracleDriver','jdbc:oracle:thin:@192.168.1.10:1521:ORCL','system','system','insert 
> into TEST');
> 
> When I run the sample pig script, it fails with an error message -
> Unable to insert record:michael    34    PL
> ...
> Caused by: java.lang.RuntimeException: JDBC error
> ...
> Caused by: java.sql.SQLException: Invalid column index
> 
> Can someone help me and let me know what is going wrong? What am I missing?
> 
> Regards,
> Sarath