UDF JDBC -- want to insert into a database

[Former Member]

Hi experts,

I need to insert into a DB using a UDF. I have written the UDF but the problem is it works fine for a select query and when i replace the query with insert, it fails.

the JDBC CC says invalid SQL query.

The 2 queries are :

//insertQuery= "insert into TABLENAME (COLUMNNAME) values ('"VAR"')";

//insertQuery= "select count(*) from TABLENAME";

accessor = LookupService.getDataBaseAccessor(channel);

resultSet = accessor.execute(insertQuery);

Also if I execute the insert query in DB it works fine.

Please advise.

[Former Member]

Hi,

//insertQuery= "insert into TABLENAME (COLUMNNAME) values ('"VAR"')";

Can you chage your insert query as follows and try:

INSERT INTO phone_book (name, number) VALUES ('John Doe', '555-1212');

Regards,

Sreenivas

[Former Member]

Hi Tushar,

For lookup we user DatabaseAccessor class which only supports Select query, no transactional statements like insert/update/stored procedure etc. But even then you can do this. How? Check my blog

[Workaround for JDBC scenarios|https://www.sdn.sap.com/irj/sdn/weblogs?blog=/pub/wlg/11749] [original link is broken] [original link is broken] [original link is broken];

However this approach has some flaws, which i didnt realized earlier. They are that if your receiver side goes down. So Outbound queue will retry the message again and again. and each time mapping will get executed, and for same message multiple insert will get performed. For more flaws, you can look at the comments of this blog ). However, if you still want to go by this method, you can enhance your java code and can use MappingTrace.

Regards

Arpil

[dharamveer_gaur2]

Hi

Why you r using resultSet in insert, are you getting response back?

[justin_santhanam]

Tushar,

Are you trying to execute insert during your lookup process?

raj.

[Former Member]

It is a very simple thing i am doing..........................

i have a source structure in which the first field of the source is the input to the first field of the target.

there is a UDF in between which inserts the first source field. the UDF has the channel specified which is JDBC.

I was just running it thru the display queue (in MM) at the and checking the communication channel. It gets errored out......... saying invalid SQL statement. But for select query it works fine

[Former Member]

might be authorization issue you many not have access to change/insert the database table values, check it.

Regards,

sukarna.ch

[Former Member]

Cant be authorisation cuz the same insert query works when i run it through the database.....

WIth same username I am able to insert in the same table in different scenario which does not involve UDF.

[Former Member]

try removing the column names in the insert statement. just give the values in sequence w.r.t columns.

like "insert into tablename values(abac, u2018xyzu2019)u201D

[Former Member]

i need to insert only 1 column........ plus the query works fine in the SQL.

I think there is something more required to insert than what is required in select in UDF.

[VijayKonam]

I do not think lookup api supports insertion operation. Insertion must be carried out through receiver jdbc adapater.

VJ