java - 用odbc往oracle插入记录，excuteUpdate返回1，代码无报错，但是数据库里记录没有增加。

Question

往用odbc插入数据库，从oracle里能查到最近执行的sql,但是数据库记录没增加。而且最近执行的那条SQL:INSERT into "wchong_test" ("name","age")values(:1,:2),单独在客户端执行这条SQL会报错：[Err] ORA-01008: not all variables bound。

Answer 1

It may be that the automatic submission of the connection is disabled, such as

 conn.setAutoCommit(false);

You can add the following statement to executeUpdate, and then check whether there are records in the database.

conn.commit();

In addition, the statement with :1:2 seen by Oracle obviously uses PreparedStatement on the Java side, so this statement cannot be executed directly in Oracle. You can use the sql_id condition from v$sql_bind_capture to find the specific binding. determine the value of the variable.

Answer 2

IfexecuteUpdate返回1数据库没有记录，那我只能想到事务, confirm that the transaction was successfully submitted.

INSERT into "wchong_test" ("name","age")values(:1,:2)

:1/:2 感觉是命名参数啊，类似于spring jdbc中的Named Parameter，很显示你直接执行这条SQL是有问题的:1这不是一个正确数据类型，要使用':1'Enclosed in quotation marks.

Answer 3

Consider two questions

1. Your SQL uses PreparedStatement. Does the server directly use this SQL to perform database operations instead of using it as a PreparedStatement?

2. If there is no problem with your server, can the query be run normally? Consider whether transaction management has not been added. I used to forget to add @Transaction when writing services, so all insertion, deletion and modification operations were executed without being submitted, no errors were reported, and the database data did not change. Finally, I discovered that I had forgotten to add spring's transaction management. You can also check whether this is the problem. Of course, you may not necessarily use spring’s transaction management, but the principle is similar

Finally, if you execute this sentence directly in Navicat, it will definitely have no effect. They are all placeholders. What do you want to check? Insert what? Obviously this is wrong. If you want to test SQL directly, change the placeholder to test data enclosed in single quotes.

Answer 4

There is no need to add "" to the tableName of insert into, right?