cancel
Showing results for 
Search instead for 
Did you mean: 

Date Format SQL Insert problem - BLS Transaction

Former Member
0 Kudos

Hi,

I apologyze if this question is trivial but I´ve been trying to find out how to solve it for a couple of hours.

My env:

SAP NW 7.0

xMII 12.0

JDK 1.4

Oracle 9.x

I have a BLS transaction that performs the following steps:

1. A tag query retrieves the tag value and timestamp.

2. A SQL query containing an Insert statement (fixed query) inserts some data into an Oracle table. One of the parameters expected by the INSERT statement is the timestamp retrieved from the Tag Query.

My INSERT statement is similar to this:


INSERT INTO SOME_TABLE(
  TAG_TIMESTAMP
  ...
)
VALUES(
  TO_DATE('[Param.1]', 'YYYYMMDD HH24:MI:SS')  ...
)

The link from the tag timestamp and Param.1 is based on the following expression:


datefromxmlformat( Tag_Query_Fan1.Results{/Rowsets/Rowset/Row/DateTime}, "yyyyMMdd hh:mm:ss" ) 

When the INSERT Command is executed the data is inserted into DB but the XMII displays the error:


com.sap.xmii.Illuminator.logging.LHException: com.sap.xmii.Illuminator.logging.LHException: com.sap.xmii.Illuminator.logging.LHException: SQL error has occurred on query ORA-00900: invalid SQL statement  

The exception being raised and the data being updated is the first problem. The second problem is the DATE field is updated with no TIME data like 12/16/2009 00:00:00.

What is the right way to UPDATE oracle date fields using fixed SQL queries from BLS transactions?

Thank you.

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

I´ve found the cause for the Oracle error. I didn´t know the UPDATE/INSERT queries must be commad. I was using fixed queries.

Here is a thread about this problem:

ploconsole
Discoverer
0 Kudos

have you an example?

Former Member
0 Kudos

Thank you for your suggestion Chanti.

I´ve just figured out the time info was being updated but the iGrid was showing the Date with zeroed time. I´ve have no idea why this is happening and I will probably change the grid query to return date values using TO_CHAR()

After peforming some tests I figured out that any non-query command like UPDATE and INSERT makes MII to throw (SQL error has occurred on query ORA-00900: invalid SQL statement) from the BLS transaction.

Any idea of how to avoid this error?

Thank you.

Former Member
0 Kudos

I have inserted to and updated Oracle tables with BLS data without any errors. I believe, you get that error if there is an inconsistency between your Oracle and BLS datatypes. I believe pretty much everything but the datetime is outputted as 'Char' from a BLS transaction. So, if you have something defined as 'numeric' in Oracle, check for consistency with the corresponding BLS data. What kind of data are you trying to insert/update?

Chanti.

Former Member
0 Kudos

Also, check the 'Data and Time Format' in your iGrid.

Chanti.

Former Member
0 Kudos

Try " to_timestamp('[Param.1]','MM/DD/YYYY HH24:MI:SS') "

Chanti.