Updates and Inserts in SQL-Editor

[TSB]

Hi all,

I have a BI JDBC connection to a SQL 2005 database. Normal select statements via SQL Editor are working, but updates and inserts do not respond although the SQL Editor´s validation says they are ok. Isn´t it possible to do a normal SQL update with the Visual Composer? What is the best way to write back data to a SQL database? Calling stored procedures via portal JDBC connections?

Thx & regards, Tobias

[Former Member · Accepted solution]

Should work, though we never tested it. We intended the BI JDBC connector for read purposes only, but do not restrict it from write purposes.

Did you check the log file, what the error is? Do you pass the values in the right formats?

[TSB · Accepted solution]

Hi Mario,

thanks for your reply.

No, I don´t think we have a field format problem. I checked that and tried different syntaxes.

The Server log says something like "... com.sap.portal.vc.connector#Java###Command handler had an exception##".

How would you write back data to an external database?

Tobias

[Former Member · Accepted solution]

You would have been trying to push data from a FORM to BI table. In this case you have to select the line which connects the FORM out port and BI table input port. When you select this line and click configure you get a table in right bottom side of the screen. Select SQL_STAMENT and write your SQL as formula.

This should help you.

[TSB · Accepted solution]

Hi Jan,

thanks, but - no, I just want to commit an update statement via the VC SQL Editor to a SQL Server which has been defined as BI Connection.

[Former Member · Accepted solution]

You cannot make dynamic updates from SQL editor , but through SQL_STATEMENT you can make it.

E.g "update table_name set name = ' & VC_VARIABLE&' where id =&VC_VARIABLE&;" -

---

not possible through SQL editor

Does this solve the problem ?

[TSB · Accepted solution]

Hi Jan,

thanks again. Performance and behaviour of the SQL Editor are a little bit disappointing, having in mind that one of the major features of the VC is interaction between different systems... Ok, we´ll get along with it and use your workaround or the stored procedures solution.

Regards, Tobias