By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,736 Members | 1,982 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,736 IT Pros & Developers. It's quick & easy.

ORA-01008: not all variables bound

P: 5
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 lconnPhonebookError = null;
Statement lstmPhonebookError = null;
ResultSet lrsMobile = null;
ResultSet lrsPhonebookError = null;
PreparedStatement lprepstmPhonebookError = null;


try {

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

Class.forName(is_dbmsDriverDest);

logger.info("Connecting to " + is_dbmsURLDest);
lconnPhonebookError = DriverManager.getConnection(is_dbmsURLDest, is_dbmsUserDest, is_dbmsPassDest);
lstmPhonebookError = lconnPhonebookError.createStatement();

FileInputStream fileInputStream = new FileInputStream(is_XMLFile);
XMLStreamReader xmlStreamReader = XMLInputFactory.newInstance().createXMLStreamReade r(fileInputStream);

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

lprepstmPhonebookError = lconnPhonebookError.prepareStatement(ls_sql);

while (xmlStreamReader.hasNext()) {

li_count++;
int eventCode = xmlStreamReader.next();
switch (eventCode) {
case XMLStreamConstants.START_ELEMENT :
if ((xmlStreamReader.getLocalName()=="Error") && (xmlStreamReader.getAttributeCount()>0))
ls_description = xmlStreamReader.getAttributeValue(0);
else if ((xmlStreamReader.getLocalName()=="ZAPIS") && (xmlStreamReader.getAttributeCount()>0))
for (int i=0; i < xmlStreamReader.getAttributeCount(); i++)
{if (xmlStreamReader.getAttributeLocalName(i).equals(" ID"))
li_phonebook_id = Integer.parseInt(xmlStreamReader.getAttributeValue (i));
}
break;

case XMLStreamConstants.END_ELEMENT :

lprepstmPhonebookError.setInt (1, li_phonebook_id);
lprepstmPhonebookError.setString (2, ls_description);

break;
}

if (li_count > 0)
logger.info("Transfered " + li_count + " PhonebookError(s)");
logger.info("Finished");

xmlStreamReader.close();
}
lprepstmPhonebookError.executeUpdate(ls_sql);
lprepstmPhonebookError.close();
lprepstmPhonebookError = null;
}
Jan 31 '07 #1
Share this Question
Share on Google+
7 Replies


dmjpro
100+
P: 2,476
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 ....

lprepstmPhonebookError.setInt (1, li_phonebook_id);
lprepstmPhonebookError.setString (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 .....
Jan 31 '07 #2

P: 5
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 ....

lprepstmPhonebookError.setInt (1, li_phonebook_id);
lprepstmPhonebookError.setString (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.
Jan 31 '07 #3

P: 5
I think so.
Even If put lprepstmPhonebookError.executeUpdate(ls_sql); after
...
case XMLStreamConstants.END_ELEMENT :

lprepstmPhonebookError.setInt (1, li_phone_book_id);
lprepstmPhonebookError.setString (2, ls_description);
lprepstmPhonebookError.executeUpdate(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
Jan 31 '07 #4

dmjpro
100+
P: 2,476
Even If put lprepstmPhonebookError.executeUpdate(ls_sql); after
...
case XMLStreamConstants.END_ELEMENT :

lprepstmPhonebookError.setInt (1, li_phone_book_id);
lprepstmPhonebookError.setString (2, ls_description);
lprepstmPhonebookError.executeUpdate(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 lprepstmPhonebookError.executeUpdate(ls_sql);
write.........
lconnPhonebookError.commit();
I think it will work and see the effect and immediately reply me ..... i am online
Jan 31 '07 #5

P: 5
Even If put lprepstmPhonebookError.executeUpdate(ls_sql); after
...
case XMLStreamConstants.END_ELEMENT :

lprepstmPhonebookError.setInt (1, li_phone_book_id);
lprepstmPhonebookError.setString (2, ls_description);
lprepstmPhonebookError.executeUpdate(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 (xmlStreamReader.hasNext()) {

li_count++;
int eventCode = xmlStreamReader.next();
switch (eventCode) {
case XMLStreamConstants.START_ELEMENT :
if ((xmlStreamReader.getLocalName()=="Error") && (xmlStreamReader.getAttributeCount()>0))
ls_description = xmlStreamReader.getAttributeValue(0);
else if ((xmlStreamReader.getLocalName()=="ZAPIS") && (xmlStreamReader.getAttributeCount()>0))
for (int i=0; i < xmlStreamReader.getAttributeCount(); i++)
{if (xmlStreamReader.getAttributeLocalName(i).equals(" ID"))
li_phone_book_id = Integer.parseInt(xmlStreamReader.getAttributeValue (i));
}

break;

case XMLStreamConstants.END_ELEMENT :

lprepstmPhonebookError.setInt (1, li_phone_book_id);
lprepstmPhonebookError.setString (2, ls_description);
lprepstmPhonebookError.executeUpdate();

break;

}

if (li_count > 0)
logger.info("Transfered " + li_count + " PhonebookError(s)");
logger.info("Finished");

}
xmlStreamReader.close();
lprepstmPhonebookError.close();
lprepstmPhonebookError = null;

}

There was two mistakes(bold in code):
1.) In CASE .. END ELEMENT instead of lprepstmPhonebookError.executeUpdate(ls_sql) must be lprepstmPhonebookError.executeUpdate() without (ls_sql)

2.) xmlStreamReader.close(); must be outside while ()....
Jan 31 '07 #6

dmjpro
100+
P: 2,476
Now I figure out what was wrong:

while (xmlStreamReader.hasNext()) {

li_count++;
int eventCode = xmlStreamReader.next();
switch (eventCode) {
case XMLStreamConstants.START_ELEMENT :
if ((xmlStreamReader.getLocalName()=="Error") && (xmlStreamReader.getAttributeCount()>0))
ls_description = xmlStreamReader.getAttributeValue(0);
else if ((xmlStreamReader.getLocalName()=="ZAPIS") && (xmlStreamReader.getAttributeCount()>0))
for (int i=0; i < xmlStreamReader.getAttributeCount(); i++)
{if (xmlStreamReader.getAttributeLocalName(i).equals(" ID"))
li_phone_book_id = Integer.parseInt(xmlStreamReader.getAttributeValue (i));
}

break;

case XMLStreamConstants.END_ELEMENT :

lprepstmPhonebookError.setInt (1, li_phone_book_id);
lprepstmPhonebookError.setString (2, ls_description);
lprepstmPhonebookError.executeUpdate();

break;

}

if (li_count > 0)
logger.info("Transfered " + li_count + " PhonebookError(s)");
logger.info("Finished");

}
xmlStreamReader.close();
lprepstmPhonebookError.close();
lprepstmPhonebookError = null;

}

There was two mistakes(bold in code):
1.) In CASE .. END ELEMENT instead of lprepstmPhonebookError.executeUpdate(ls_sql) must be lprepstmPhonebookError.executeUpdate() without (ls_sql)

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

No commit is required for preparestatement.......
Plz inform me .........
Thanks...........
Jan 31 '07 #7

P: 5
No commit is required for preparestatement.......
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.
Jan 31 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.