ORA-01008: not all variables bound

[izy25]

I'm trying to INSERT datas from XML file into table. I'dont know what is wrong with code below that I don't insert datas into the table. Please help


String ls_sql;
Integer li_phonebook_id = null;
String ls_description = null;
int li_count = 0;
Connection lconnPhonebookE rror = null;
Statement lstmPhonebookEr ror = null;
ResultSet lrsMobile = null;
ResultSet lrsPhonebookErr or = null;
PreparedStateme nt lprepstmPhonebo okError = null;


try {

logger.info("-----------------------------------------------");

Class.forName(i s_dbmsDriverDes t);

logger.info("Co nnecting to " + is_dbmsURLDest) ;
lconnPhonebookE rror = DriverManager.g etConnection(is _dbmsURLDest, is_dbmsUserDest , is_dbmsPassDest );
lstmPhonebookEr ror = lconnPhonebookE rror.createStat ement();

FileInputStream fileInputStream = new FileInputStream (is_XMLFile);
XMLStreamReader xmlStreamReader = XMLInputFactory .newInstance(). createXMLStream Reader(fileInpu tStream);

ls_sql =
" INSERT INTO t_phonebook_err or (" +
" phone_book_id," +
" description)" +
" VALUES (?,?)";

lprepstmPhonebo okError = lconnPhonebookE rror.prepareSta tement(ls_sql);

while (xmlStreamReade r.hasNext()) {

li_count++;
int eventCode = xmlStreamReader .next();
switch (eventCode) {
case XMLStreamConsta nts.START_ELEME NT :
if ((xmlStreamRead er.getLocalName ()=="Error") && (xmlStreamReade r.getAttributeC ount()>0))
ls_description = xmlStreamReader .getAttributeVa lue(0);
else if ((xmlStreamRead er.getLocalName ()=="ZAPIS") && (xmlStreamReade r.getAttributeC ount()>0))
for (int i=0; i < xmlStreamReader .getAttributeCo unt(); i++)
{if (xmlStreamReade r.getAttributeL ocalName(i).equ als("ID"))
li_phonebook_id = Integer.parseIn t(xmlStreamRead er.getAttribute Value(i));
}
break;

case XMLStreamConsta nts.END_ELEMENT :

lprepstmPhonebo okError.setInt (1, li_phonebook_id );
lprepstmPhonebo okError.setStri ng (2, ls_description) ;

break;
}

if (li_count > 0)
logger.info("Tr ansfered " + li_count + " PhonebookError( s)");
logger.info("Fi nished");

xmlStreamReader .close();
}
lprepstmPhonebo okError.execute Update(ls_sql);
lprepstmPhonebo okError.close() ;
lprepstmPhonebo okError = null;
}

[dmjpro]

SQL> begin
2 execute immediate 'select * from emp where empno=:empid';
3 end;
4 /
begin
*
ERROR at line 1:
ORA-01008: not all variables bound
ORA-06512: at line 2


SQL> begin
2 execute immediate 'select * from emp where empno=:empid' using 1;
3 end;
4 /

PL/SQL procedure successfully completed.

This error occured when the sufficient bind variables are not provided....
r u sure that these two statements ....

lprepstmPhonebo okError.setInt (1, li_phonebook_id );
lprepstmPhonebo okError.setStri ng (2, ls_description) ;

executed always before executing the query ....
At first make sure that.....
After checking immediate post a reply.........
i am waiting for ur reply .....

[izy25]

SQL> begin
2 execute immediate 'select * from emp where empno=:empid';
3 end;
4 /
begin
*
ERROR at line 1:
ORA-01008: not all variables bound
ORA-06512: at line 2


SQL> begin
2 execute immediate 'select * from emp where empno=:empid' using 1;
3 end;
4 /

PL/SQL procedure successfully completed.

This error occured when the sufficient bind variables are not provided....
r u sure that these two statements ....

lprepstmPhonebo okError.setInt (1, li_phonebook_id );
lprepstmPhonebo okError.setStri ng (2, ls_description) ;

executed always before executing the query ....
At first make sure that.....
After checking immediate post a reply.........
i am waiting for ur reply .....

I think so.

[izy25]

I think so.

Even If put lprepstmPhonebo okError.execute Update(ls_sql); after
...
case XMLStreamConsta nts.END_ELEMENT :

lprepstmPhonebo okError.setInt (1, li_phone_book_i d);
lprepstmPhonebo okError.setStri ng (2, ls_description) ;
lprepstmPhonebo okError.execute Update(ls_sql);

is stil not working. Result of this code:
2007-01-31 11:49:17,857 INFO -----------------------------------------------
2007-01-31 11:49:17,951 INFO Connecting to jdbc...
2007-01-31 11:49:18,357 INFO Transfered 1 PhonebookError( s)
2007-01-31 11:49:18,357 INFO Finished

BUT STILL NO INSERTED DATAS IN TABLE

[dmjpro]

Even If put lprepstmPhonebo okError.execute Update(ls_sql); after
...
case XMLStreamConsta nts.END_ELEMENT :

lprepstmPhonebo okError.setInt (1, li_phone_book_i d);
lprepstmPhonebo okError.setStri ng (2, ls_description) ;
lprepstmPhonebo okError.execute Update(ls_sql);

is stil not working. Result of this code:
2007-01-31 11:49:17,857 INFO -----------------------------------------------
2007-01-31 11:49:17,951 INFO Connecting to jdbc...
2007-01-31 11:49:18,357 INFO Transfered 1 PhonebookError( s)
2007-01-31 11:49:18,357 INFO Finished

BUT STILL NO INSERTED DATAS IN TABLE

Thanks for responding....
Add the code after lprepstmPhonebo okError.execute Update(ls_sql);
write.........
lconnPhonebookE rror.commit();
I think it will work and see the effect and immediately reply me ..... i am online

[izy25]

Even If put lprepstmPhonebo okError.execute Update(ls_sql); after
...
case XMLStreamConsta nts.END_ELEMENT :

lprepstmPhonebo okError.setInt (1, li_phone_book_i d);
lprepstmPhonebo okError.setStri ng (2, ls_description) ;
lprepstmPhonebo okError.execute Update(ls_sql);

is stil not working. Result of this code:
2007-01-31 11:49:17,857 INFO -----------------------------------------------
2007-01-31 11:49:17,951 INFO Connecting to jdbc...
2007-01-31 11:49:18,357 INFO Transfered 1 PhonebookError( s)
2007-01-31 11:49:18,357 INFO Finished

BUT STILL NO INSERTED DATAS IN TABLE

Now I figure out what was wrong:

while (xmlStreamReade r.hasNext()) {

li_count++;
int eventCode = xmlStreamReader .next();
switch (eventCode) {
case XMLStreamConsta nts.START_ELEME NT :
if ((xmlStreamRead er.getLocalName ()=="Error") && (xmlStreamReade r.getAttributeC ount()>0))
ls_description = xmlStreamReader .getAttributeVa lue(0);
else if ((xmlStreamRead er.getLocalName ()=="ZAPIS") && (xmlStreamReade r.getAttributeC ount()>0))
for (int i=0; i < xmlStreamReader .getAttributeCo unt(); i++)
{if (xmlStreamReade r.getAttributeL ocalName(i).equ als("ID"))
li_phone_book_i d = Integer.parseIn t(xmlStreamRead er.getAttribute Value(i));
}

break;

case XMLStreamConsta nts.END_ELEMENT :

lprepstmPhonebo okError.setInt (1, li_phone_book_i d);
lprepstmPhonebo okError.setStri ng (2, ls_description) ;
lprepstmPhonebo okError.execute Update();

break;

}

if (li_count > 0)
logger.info("Tr ansfered " + li_count + " PhonebookError( s)");
logger.info("Fi nished");

}
xmlStreamReader .close();
lprepstmPhonebo okError.close() ;
lprepstmPhonebo okError = null;

}

There was two mistakes(bold in code):
1.) In CASE .. END ELEMENT instead of lprepstmPhonebo okError.execute Update(ls_sql) must be lprepstmPhonebo okError.execute Update() without (ls_sql)

2.) xmlStreamReader .close(); must be outside while ()....

[dmjpro]

Now I figure out what was wrong:

while (xmlStreamReade r.hasNext()) {

li_count++;
int eventCode = xmlStreamReader .next();
switch (eventCode) {
case XMLStreamConsta nts.START_ELEME NT :
if ((xmlStreamRead er.getLocalName ()=="Error") && (xmlStreamReade r.getAttributeC ount()>0))
ls_description = xmlStreamReader .getAttributeVa lue(0);
else if ((xmlStreamRead er.getLocalName ()=="ZAPIS") && (xmlStreamReade r.getAttributeC ount()>0))
for (int i=0; i < xmlStreamReader .getAttributeCo unt(); i++)
{if (xmlStreamReade r.getAttributeL ocalName(i).equ als("ID"))
li_phone_book_i d = Integer.parseIn t(xmlStreamRead er.getAttribute Value(i));
}

break;

case XMLStreamConsta nts.END_ELEMENT :

lprepstmPhonebo okError.setInt (1, li_phone_book_i d);
lprepstmPhonebo okError.setStri ng (2, ls_description) ;
lprepstmPhonebo okError.execute Update();

break;

}

if (li_count > 0)
logger.info("Tr ansfered " + li_count + " PhonebookError( s)");
logger.info("Fi nished");

}
xmlStreamReader .close();
lprepstmPhonebo okError.close() ;
lprepstmPhonebo okError = null;

}

There was two mistakes(bold in code):
1.) In CASE .. END ELEMENT instead of lprepstmPhonebo okError.execute Update(ls_sql) must be lprepstmPhonebo okError.execute Update() without (ls_sql)

2.) xmlStreamReader .close(); must be outside while ()....

No commit is required for preparestatemen t.......
Plz inform me .........
Thanks......... ..

[izy25]

No commit is required for preparestatemen t.......
Plz inform me .........
Thanks......... ..

Commit is not required because we got only one sql statment. If we have more sql INSERT, DELETE,.., a method commit confirm the whole transaction.