Problems using JDBC with INSERT statement

[Former Member]

Hi all,

i'am currently developing a web dynpro application and want to store some information in the local database using jdbc.

The select-statements are no problems and giving me the data i want.

But with insert-statements i got some problems:

I tryed the following:

String query =	"insert into Z_BASEACT_SYSTEM (SYSINT_ID, SID, ICMDOCBSSERVICE, SLD_ID, ACTIVE) VALUES (Z_BASEACT_SYSTEM_SEQ.NEXTVAL, '"+sid[0]+"', '-', "+sid[1]+", 1)";
int affRows = stmt.executeUpdate(query);

stmt is a valid Statement object. Also in the database is everything fine, but within the logging system i get the following error:

BaseAct: SQLError - Statement original query:
insert into Z_BASEACT_SYSTEM (SYSINT_ID, SID, ICMDOCBSSERVICE, SLD_ID, ACTIVE) VALUES (Z_BASEACT_SYSTEM_SEQ.NEXTVAL, 'Z00 on lsyz00', '-', 102, 1) 
Exception Outputmessage: 
The SQL statement "INSERT INTO "Z_BASEACT_SYSTEM" ("SYSINT_ID","SID","ICMDOCBSSERVICE","SLD_ID","ACTIVE") VALUES ("Z_BASEACT_SYSTEM_SEQ"."NEXTVAL",'Z00 on lsyz00','-',102,1)" contains the semantics error[s]: - 1:88 - the table or view >>Z_BASEACT_SYSTEM_SEQ<< used in the column reference >>"Z_BASEACT_SYSTEM_SEQ"."NEXTVAL"<< does not exist

But the thing is: When i copy the generated Statement ( beyond Exception Output Message) to the SQLStudio, the line will be inserted into the database.

The schemata of the database is the same i used with the jdbc connection (SAP<SID>DB).

Any ideas what's my mistake?

Thanks to all and best regards

Holger

[Former Member · Accepted solution]

Hi Holger,

By looking in to the exception it seems that it is not able to identify the Sequence Z_BASEACT_SYSTEM_SEQ.

So instead of using sequence try inserting your own value in field SYSINT_ID. You can execute some code which extract maximum from field SYSINT_ID and then adding 1 to the value insert the updated value. So suppose this updated value is stored in variable max_SYSINT_ID, you can use query as follows:

String query =	"insert into Z_BASEACT_SYSTEM (SYSINT_ID, SID, ICMDOCBSSERVICE, SLD_ID, ACTIVE) VALUES (" + max_SYSINT_ID + ", '"+sid[0]+"', '-', "+sid[1]+", 1)";

Regards,

Gopal

[Former Member · Accepted solution]

Thanks for your answer!

But that's not a real solution, because why not to use sequences, because the exists in SAPDB and Oracle ...

I don't understand why the Insert works with the SQL-Studio but not with the JDBC

[Former Member · Accepted solution]

Hi Holger,

the problem is, that in WD application you probably use OpenSQL layer through default datasource SAP<SID>DB and sequence is not defined in this layer (also, when you create tables through script in SQL Studio, not using NWDS Dictionary perspective, you don't will be able to lookup these tables)

I didn't find way, how to 'register' sequence in OpenSQL. Solution used by me is create another DataSource (through Visual Admin) and use Native SQL Engine in it. You will be able lookup all database objects in JDBC connections created by this Datasource.

For all operations (SELECT, INSERT, ..) you will use classic OpenSQL layer (portability and performance issues), for reading sequence Native SQL layer and result of these read you can use in your INSERT statement.

SQL Studio use these native connections, not Open SQL, that's the reason why your INSERT statement was successfull.

Regards,

Juraj

[Former Member · Accepted solution]

Hi All!

Thanks for your well support!

I found according to the things Juraj said a solving line.

It's possibile to use the following line to create a Native-SQL-Statement.

Statement theStatement = NativeSQLAccess.createNativeStatement(theConnection);

With this Native-SQL-Statement you have access to the quite same methods than the nomal Statement-Object, but in native-mode with the sequence support!

So for SELECT-Query i will use the normal Statement and for INSERT INTO with sequences i will use the native-sql-statement.

Thanks to all

Holger