Problem with SQL Statement in Visual Composer !

[Former Member]

Hi all,

I use <b>BI JDBC System</b> & connect to MaxDB Database. The connection is Ok. According to the documents in help.sap.com, data services for BI JDBC connector are <b>Tables with a default relational query and custom SQL queries or Relational queries created with the BI Integration Wizard. </b>

- When I click on Find Data Button & select system, I just see all the tables in my database, but now, if I want to use my store procedures defined in my database, how can I do ?

- When using BI Wizard, I just can select data of table with a static condition (ex: select ... where ID = '1'), but how can I do with dynamic variable which is got from the input form ?

- When I drag & drop a table into storyboards, drag out input port of this data service & add an input form. I configure SQL Statement of the Submit line between Input Form & dataservice as:

<b>"Select DBADMIN.Class.ClassID, DBADMIN.Class.ClassName from DBADMIN.Class where DBADMIN.Class.ClassID == @CLASSID"</b>

The formula is correct, but when running the iView, I have error :<b>Query Execution failed.</b>

Could you please help me to solve this problem ?

Thank you very much & Kindly regards,

Tweety.

[Former Member]

Thanks all

Kindly regards,

Tweety.

[Former Member]

Hi,

Thank you very much for your replys. I've tested if the dynamic value of the condition is integer, it's OK

But if the ClassID type is not integer, it's string, I write a SQL Statement like:

"Select DBADMIN.Class.ClassName from DBADMIN.Class where DBADMIN.Class.ClassID = '1' "

or with dynamic condition:

"Select DBADMIN.Class.ClassName from DBADMIN.Class where DBADMIN.Class.ClassID = '"&@ClassID&"'"

or I write the SQL Statement for insert/update/delete data,

I always have errors.

I've tested if the dynamic value of the condition is integer, it's OK

Do you know this problem ?

Thank you very much & kindly regards,

Tweety

[Former Member]

Are you sure that your variable @ClassID returns the proper value? Can you check in the log file what the SQL statement looks like?

insert/update/delete is not tested with the BI JDBC connector, but it theoretically should also work.

[Former Member]

Did you execute the statement in the sql editor of vc? Sometimes, the editor throws an error, but the deployed application works fine. You might want to try that.

As Mario said, you should take a look at the log file, go to http://<yourhost>:<yourport>/nwa/

Select "Monitoring" -> "Protocols and Traces", select "default trace". While VC only reports "Query execution failed", you can read more info from the default trace.

[Former Member]

Ad stored procedures:

https://wiki.sdn.sap.com/wiki/display/VC/Cannotseetables

[Former Member]

Hi Benjamin Leunig,

But when I use static value for the query condition, I still have error:

"Select DBADMIN.Class.ClassName from DBADMIN.Class where DBADMIN.Class.ClassID == '1' "

When I write th query without condition, It's ok.

"Select * from DBADMIN.Class"

Do you know how to resolve it ?

Thank you very much.

Tweety.

[Former Member]

Hi Tweety,

you should change your SQL Statement. Use instead:

"Select DBADMIN.Class.ClassName from DBADMIN.Class where DBADMIN.Class.ClassID == '1' "

"Select DBADMIN.Class.ClassName from DBADMIN.Class where DBADMIN.Class.ClassID = 1"

because if you use Integer you don't need to use ' '. Also you should do a comparison with = and not with ==

- When using BI Wizard, I just can select data of table with a static condition (ex: select ... where ID = '1'), but how can I do with dynamic variable which is got from the input form ?

--> It makes normally no sense to use dynamic variables in the BI wizard, because you can not change such a SQL statement later. Therefore you should use in the data service only statements like "select * from table" and put the right statement via input port due to the data service.

Kind regards,

Thomas

[Former Member]

Hi Tweety,

I think it has to be

"Select DBADMIN.Class.ClassID, DBADMIN.Class.ClassName from DBADMIN.Class where DBADMIN.Class.ClassID == "& @CLASSID

You might still have problems with that, as SQL-Statements in VC (especially string comparisons) are sometimes tricky. I cannot check the statement myself at the moment, I'm sorry.

Best regards,

Benni