Bug - using the Query template, by selecting Modes as 'Fixed Query'

[former_member192939]

Hi I am new to xMII.

I was trying to insert data in the table using Query template by selecting Modes as 'Fixed Query'. Once the query was inserted into the relative field, I have clicked the 'Test' button' and obtained the following error message.

"Fatal Error

A SQL Error has occurred on query, ORA-00900: invalid SQL statement."

But, when I observed the database the data was inserted into the table.

Is this a bug?

When I saw this error message I thought that the database wasnt updated, but it was.

I want to know if there are any options to avoid such an error.

Regards,

Ajay.

[abesh · Accepted solution]

Hi Ajay,

This is not a bug.

Quoting from the SAP xMII Help Docs :

-

---

<b>FixedQuery Mode</b>

FixedQuery mode requires that the entire SQL statement be passed via the Query and QueryParams parameters. For standard SQL queries, only the Query parameter would typically be used. A simple SQL query string using these parameters could look like this:

<b>

Mode=FixedQuery&Query=SELECT * FROM MyTable</b>

FixedQuery mode is also used when executing stored procedures in the database to return data. In those cases, the Query and QueryParams might be used. For example, if the database has a stored procedure called "sp_InventoryStatus" which takes a single parameter, the material code, a query might look like:

<b>Mode=FixedQuery&Query=exec sp_InventoryStatus&QueryParams='ALUM001A'</b>

The actual syntax for executing a stored procedure may vary from one database to another.

-

---

For insert/update please use the <b>Command Mode</b> for <b>SQLQuery</b>.

[former_member192939 · Accepted solution]

Hi Abesh,

Thank you for your quick response.

I understand the way it is described in the help file.

But by mistake if you use insert in 'Fixed Query' and proceed then we might face problems. For example, in BLS if we use the output from the query as true or false and proceed the output is an error message and the database will be updated, which is not desired.

Regards,

Ajay.

[Former Member]

Insert, Updates, and Deletes in a SQL Query require the "Command" Mode, not the "FixedQuery" or "Query" Modes.

The FixedQuery and Query mode expect a result set from the database. The Command mode does not. This is what is causing your error.

[former_member192939]

Hi Ryan,

Thank you for the reply.

When the FixedQuery and Query mode expect a result set from the database, why does the insert work with the FixedQuery mode? May be it should be disabled in the the FixedQuery mode.

Regards.

Ajay.

[Former Member]

Ajay,

I'm not the developer of the SQL Connector or the SQL Mode, so I do not know for sure, but I know the answer is to use the Command Mode.

There is no "rollback" in this process. The Query initiates the query to insert/update/delete and then the DB does it. The Query then waits for a response, and when none is returned by the DB, it throws up. Why, I don't know.

[former_member192939]

Ryan,

Thanks for the reply and keeping the forum active

I totally agree that "command" mode must be used to insert data.

May be a developer can rectify this problem. But as a beginner, in my view its a bug.

Regards,

Ajay.

[salvatore_castro]

Ajay,

This is not a bug with the product, it is there to control the format of the response information from the underlying system. You can control any parameter that you select/click/type in a template from JavaScript or through business logic. If you need to change the query template mode on the fly then when you set the new query/command set the mode as well. Hope this helps to explain.

-Sam

[former_member192939]

Hi Sam,

Your answer is helpful.

I further have a doubt.

For example: Suppose i develop a query template/mode: FixedQuery to insert data (normally, this mode shouldnt be used, i understand) and use this query in BLS and check for the condition in the following sequence. It should return a message unsuccessful, but the DB will be updated which is controversial to the message obtained from the BLS. Can you through some light on this, appreciated.

Thanks,

Ajay.

[salvatore_castro]

Ajay,

The reason the application indicates unsuccessful is because the FixedQuery that was performed did not return any valid data so the Query is unsuccessful. For further detail on what each mode means please see their explaination in the xMII help documentation under:

Connectors/Datasources -> IDBC Connector User's Guide -> Capabilities

Hope this helps.

-Sam

[former_member192939]

Hi Sam,

I went through the help file

// The reason the application indicates unsuccessful is because the FixedQuery that was performed did not return any valid data so the Query is unsuccessful.

The output is "the Query is unsuccessful", but the DB is updated. When you get a message stating "the Query is unsuccessful", then the DB shouldn't be updated right?

[Former Member]

Hi,

This may be a fault from developer side. It works fine in some situations.

Consider the situation.

Through query one table is updated or a row is inserted in the row and you want to retrieve the value from that row and do some calculations.

The condition

After it is successfully inserted only calculations should be done.

Write both commands in the fixed query and it updates the DB and return the updated value.

This can't be done that in the command mode.

I hope it throws some light.....

Regards,

Kishore kumar P.S.

[former_member192939]

Hi Kishore,

Nice to know that.

Thanks for your reply.

Ajay.