mod-jdbc-persistor insert problem...

[sungkwoneom]

Hello,

I’m testing mod-jdbc-persistor.

DB connection and select is ok, BUT when I do INSERT operation, I see error message as below.

—————ERROR MESSAGE------------------

Error performing batch select

java.sql.SQLException: Wrong number of parameters: expected 4, was given 3 ==> here

at org.apache.commons.dbutils.AbstractQueryRunner.fillStatement(AbstractQueryRunner.java:201) at com.bloidonia.vertx.mods.JdbcProcessor$NonBrokenPMDStatementFiller.fill(JdbcProcessor.java:639) at com.bloidonia.vertx.mods.JdbcProcessor$5.process(JdbcProcessor.java:367) at com.bloidonia.vertx.mods.JdbcProcessor$BatchHandler.handle(JdbcProcessor.java:595) at com.bloidonia.vertx.mods.JdbcProcessor.doUpdate(JdbcProcessor.java:347) at com.bloidonia.vertx.mods.JdbcProcessor.doUpdate(JdbcProcessor.java:335) at com.bloidonia.vertx.mods.JdbcProcessor.handle(JdbcProcessor.java:213) at com.bloidonia.vertx.mods.JdbcProcessor.handle(JdbcProcessor.java:47) at org.vertx.java.core.eventbus.impl.DefaultEventBus$11.run(DefaultEventBus.java:943) at org.vertx.java.core.impl.DefaultContext$3.run(DefaultContext.java:175) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:744)

{"status":"error","message":"Error performing batch select"}

---

My test code is as below.

—————————SOURCE——————————

readyAddress = address + '.ready'

def replyHandler(msg):

print dir(msg.java_obj)

print msg.body

def readyHandler(msg): print msg.body if msg.body.get('status') == 'ok': print "Oracle is Ready " EventBus.unregister_handler(rid)

the number of ‘?’ and parameters match as 3 items. Is this wrong???

    EventBus.send(address, {'action':'insert',  'stmt':'insert into datainfo(name,age,description) values(?,?,?);',    'values':[ ['skeom',41,'abc'] ]}, replyHandler)
else:
    print "Oracle is Not Ready "
    print message.status

def loadhandler(re,msg): if re != None: print re.getMessage() return print "LOAD JDBC Modeule msg: ", msg

rid = EventBus.register_handler(readyAddress, handler=readyHandler)

vertx.deploy_module("com.bloidonia~mod-jdbc-persistor~2.1.3", config, 1, loadhandler)

——————————————————————————————————

As you can see, there is just 3 ‘?’ place holder and 3 parameters.

But the error message is "java.sql.SQLException: Wrong number of parameters: expected 4, was given 3” .

Did I miss something?

[sungkwoneom]

I thought this might be limited to python.

BUT, when I t est it using java, I saw the same result.

Below is the java code. If I did something wrong, please correct me.

I'm using vert.x 2.1.

Thanks.

-------------java code---------------- 53 System.out.print("--------doInsert--------------"); 54 JsonObject json = new JsonObject(); 55 json.putString("action", "insert"); 56 json.putString("stmt", "insert into datainfo(name,age,description) values(?,?,?)"); 57 JsonArray jarray = new JsonArray(); 58 59 jarray.addString("skeom"); 60 jarray.addNumber(100); 61 jarray.addString("description"); 62 63 json.putArray("values", new JsonArray().addArray(jarray)); 64 65 vertx.eventBus().sendWithTimeout(this.address, json, 1000, new Handler<AsyncResult<Message>>() 66 { 67 @Override 68 public void handle(AsyncResult<Message> result) { 69 if(result.succeeded()) 70 { 71 System.out.print( result.result().body()); 72 }else 73 { 74 System.out.println(result.cause().getMessage()); 75 } 76 77 } 78 79 }) ;

[timyates]

Do you have an id field?

Are you sure that's the query that's giving the error?

I'll investigate... On 23 Jun 2014 03:21, "sungkwon.eom" notifications@github.com wrote:

I thought this might be limited to python.

BUT, when I t est it using java, I saw the same result.

Below is the java code. If I did something wrong, please correct me.

I'm using vert.x 2.1.

Thanks.

-------------java code---------------- 53 System.out.print("--------doInsert--------------"); 54 JsonObject json = new JsonObject(); 55 json.putString("action", "insert"); 56 json.putString("stmt", "insert into datainfo(name,age,description) values(?,?,?)"); 57 JsonArray jarray = new JsonArray(); 58 59 jarray.addString("skeom"); 60 jarray.addNumber(100); 61 jarray.addString("description"); 62 63 json.putArray("values", new JsonArray().addArray(jarray)); 64 65 vertx.eventBus().sendWithTimeout(this.address, json, 1000, new Handler>>() 66 { 67 @Override https://github.com/Override 68 public void handle(AsyncResult> result) { 69 if(result.succeeded()) 70 { 71 System.out.print( result.result().body()); 72 }else 73 { 74 System.out.println(result.cause().getMessage()); 75 } 76 77 } 78 79 }) ;

— Reply to this email directly or view it on GitHub https://github.com/timyates/mod-jdbc-persistor/issues/19#issuecomment-46801144 .

[sungkwoneom]

No id field. It's oracle.

When it comes to hsqldb, it works well.

BUT when I try it using Oralce, I meets this issue.

According to the error message. It seems that it is not oracle error message.

FYI, the oralce client version is 11.2.0.4.0, mod-jdbc-persistor om.bloidonia~mod-jdbc-persistor~2.1.3 and Vert.x 2.1.

thanks

[timyates]

Found this, could you try with passing:

pmdKnownBroken: "yes"

in the initial config sent to the persistor?

[sungkwoneom]

Good! The error message has passed.

BUT I got another error.

It seems oracle ROWID cannot be converted to jason.

---

Caught error with UPDATE: Cannot have objects of class class oracle.sql.ROWID in JSON org.vertx.java.core.VertxException: Cannot have objects of class class oracle.sql.ROWID in JSON at org.vertx.java.core.json.JsonElement.convertMap(JsonElement.java:60) at org.vertx.java.core.json.JsonObject.(JsonObject.java:56) at org.vertx.java.core.json.JsonObject.(JsonObject.java:45) at com.bloidonia.vertx.mods.JsonUtils.listOfMapsToJsonArray(JsonUtils.java:61) at com.bloidonia.vertx.mods.JdbcProcessor$5.process(JdbcProcessor.java:373) at com.bloidonia.vertx.mods.JdbcProcessor$BatchHandler.handle(JdbcProcessor.java:595) at com.bloidonia.vertx.mods.JdbcProcessor.doUpdate(JdbcProcessor.java:347) at com.bloidonia.vertx.mods.JdbcProcessor.doUpdate(JdbcProcessor.java:335) at com.bloidonia.vertx.mods.JdbcProcessor.handle(JdbcProcessor.java:213) at com.bloidonia.vertx.mods.JdbcProcessor.handle(JdbcProcessor.java:47) at org.vertx.java.core.eventbus.impl.DefaultEventBus$11.run(DefaultEventBus.java:943) at org.vertx.java.core.impl.DefaultContext$3.run(DefaultContext.java:175) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:744)

[timyates]

Cool (and bah)

Right, this is because it looks like Oracle returns ROWID objects when it does an insert (I'm guessing if there's no primary key)

Can you try adding an auto-increment primary key to the table and see if it works?

I'm trying to think of a good way round this....currently, I can think of:

1. Add an extra property to be sent with the insert message to tell the persistor to skip returning generated ids
2. Catch errors when adding the result map to Json and try adding the toString() value of the objects instead

I'm leaning towards number one at this moment in time, but I will need to do some more thinking about whether this (or some other option) is the way forward...

[sungkwoneom]

I think number one is much better too.

If we can get an option on whether get ID list or not, it’s best. Actually, sometimes, just checking success count is enough.

By the way, as far as I know, there is no auto-increment primary key in Oracle. (If I’m wrong, correct me).

And… in your schedule, when can I get the patched version (number one)?

thanks

1. 6. 24., 오후 4:07, Tim Yates notifications@github.com 작성:

Cool (and bah)

Right, this is because it looks like Oracle returns ROWID objects when it does an insert (I'm guessing if there's no primary key)

Can you try adding an auto-increment primary key to the table and see if it works?

I'm trying to think of a good way round this....currently, I can think of:

Add an extra property to be sent with the insert message to tell the persistor to skip returning generated ids Catch errors when adding the result map to Json and try adding the toString() value of the objects instead I'm leaning towards number one at this moment in time, but I will need to do some more thinking about whether this (or some other option) is the way forward...

— Reply to this email directly or view it on GitHub.

[timyates]

You can use sequences in Oracle, but I just had a look and the Oracle stuff I did a decade ago came flooding back and now I feel ill ;-) hee hee

I'll try and add Option 1 when I can (if I don't think of anything better in the mean time)... Unfortunately, I'm currently stuck on a Windows machine behind a proxy so am struggling to get the tests up and running... All this will change soon as a new apple laptop is en-route.

I would estimate that it will hopefully be next week sometime, but everything is a bit crazy here atm, so this may deviate by a week

[sungkwoneom]

FYI,

Below is the table creation script.

CREATE TABLE datainfo ( id NUMBER primary key , name VARCHAR2 (100 BYTE), age NUMBER , description VARCHAR2 (1000 BYTE) )

And I tested Insert operation using mod-jdbc-persisitor.

The result is same.

————error———— Caught error with UPDATE: Cannot have objects of class class oracle.sql.ROWID in JSON org.vertx.java.core.VertxException: Cannot have objects of class class oracle.sql.ROWID in JSON at org.vertx.java.core.json.JsonElement.convertMap(JsonElement.java:60) at org.vertx.java.core.json.JsonObject.(JsonObject.java:56) at org.vertx.java.core.json.JsonObject.(JsonObject.java:45) at com.bloidonia.vertx.mods.JsonUtils.listOfMapsToJsonArray(JsonUtils.java:61) at com.bloidonia.vertx.mods.JdbcProcessor$5.process(JdbcProcessor.java:373) at com.bloidonia.vertx.mods.JdbcProcessor$BatchHandler.handle(JdbcProcessor.java:595) at com.bloidonia.vertx.mods.JdbcProcessor.doUpdate(JdbcProcessor.java:347) at com.bloidonia.vertx.mods.JdbcProcessor.doUpdate(JdbcProcessor.java:335) at com.bloidonia.vertx.mods.JdbcProcessor.handle(JdbcProcessor.java:213) at com.bloidonia.vertx.mods.JdbcProcessor.handle(JdbcProcessor.java:47) at org.vertx.java.core.eventbus.impl.DefaultEventBus$11.run(DefaultEventBus.java:943) at org.vertx.java.core.impl.DefaultContext$3.run(DefaultContext.java:175) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:744)

1. 6. 24., 오후 4:07, Tim Yates notifications@github.com 작성:

Cool (and bah)

Right, this is because it looks like Oracle returns ROWID objects when it does an insert (I'm guessing if there's no primary key)

Can you try adding an auto-increment primary key to the table and see if it works?

I'm trying to think of a good way round this....currently, I can think of:

Add an extra property to be sent with the insert message to tell the persistor to skip returning generated ids Catch errors when adding the result map to Json and try adding the toString() value of the objects instead I'm leaning towards number one at this moment in time, but I will need to do some more thinking about whether this (or some other option) is the way forward...

— Reply to this email directly or view it on GitHub.

[timyates]

As a workaround (maybe) can you try changing:

{'action':'insert',  'stmt':'insert into datainfo(name,age,description) values(?,?,?);',    'values':[ ['skeom',41,'abc'] ]}

To

{'action':'update',  'stmt':'insert into datainfo(name,age,description) values(?,?,?);',    'values':[ ['skeom',41,'abc'] ]}

[sungkwoneom]

Ha Ha :-) It’s working well.

1. 6. 24., 오후 5:18, Tim Yates notifications@github.com 작성:

As a workaround (maybe) can you try changing:

{'action':'insert', 'stmt':'insert into datainfo(name,age,description) values(?,?,?);', 'values':[ ['skeom',41,'abc'] ]} To

{'action':'update', 'stmt':'insert into datainfo(name,age,description) values(?,?,?);', 'values':[ ['skeom',41,'abc'] ]} — Reply to this email directly or view it on GitHub.

[timyates]

Cool!

Maybe that's the solution instead of changing anything ;-)

The update code path is basically the route that would be taken if the new parameter were to be added...