Re: Unable to store to DB using DBStorage

[Sarath]

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.jos...@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