JDBC Error

[Former Member]

Hi,

I'm using a JDBC receiver but I can't get the message through due to this error. I guess it's something wrong with the structure but what?

JDBC Message processing failed, due to Error 'com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata Database] [TeraJDBC 13.10.00.10] [Error 3706] [SQLState 42000] Syntax error: expected something between the beginning of the request and '<'.' executing service raw2sql with SQL statement '<?xml version="1.0" encoding="UTF-8"?> <ns0:MT_XXX_v1 xmlns:ns0="http://xxx.com/xxx/xxx"><Insert_Load_Stg_Customer><Load_Stg_Customer><action>INSERT</action>

<table>Load_Stg_Customer</table><access><SequenceNumber>0000000000388146</SequenceNumber>

<ContentType>MDI029</ContentType><SubContentType>0</SubContentType>

<TransactionDate_Time>20110321105407</TransactionDate_Time><IEReceivedDate_Time>201103211003858</IEReceivedDate_Time><SourceVersion>v1</SourceVersion></access></Load_Stg_Customer>

</Insert_Load_Stg_Customer></ns0:MT_XXX_v1>'

Best regards

[Former Member · Accepted solution]

Hay ..

It should be like this ..

Change your receiver data type accordingly for StoreProcedure ..

<?xml version="1.0" encoding="UTF-8"?>

<ns0:MT_XXX_v1 mlns:ns0="http://xxx.com/xxx/xxx">

<Load_Stg_Customer_Statement>

<Load_Stg_Customer action=EXECUTE>

<SequenceNumber>0000000000388146</SequenceNumber>

<ContentType>MDI029</ContentType>

<SubContentType>0</SubContentType>

<TransactionDate_Time>20110321105407</TransactionDate_Time>

<IEReceivedDate_Time>201103211003858</IEReceivedDate_Time>

<SourceVersion>v1</SourceVersion>

</Load_Stg_Customer>

</Load_Stg_Customer_Statement>

</ns0:MT_XXX_v1>

I am expecting below given name would be your Parameters name in stored procedure.

<SequenceNumber>0000000000388146</SequenceNumber>

<ContentType>MDI029</ContentType>

<SubContentType>0</SubContentType>

<TransactionDate_Time>20110321105407</TransactionDate_Time>

<IEReceivedDate_Time>201103211003858</IEReceivedDate_Time>

<SourceVersion>v1</SourceVersion>

Now for each parameter there would be two attributes:

ISINPUT/ISOUTPUT

TYPE

INPUT for IN TYPE and OUTPUT for OUT type parameter

eg.

ROOT

STATEMENT

STOREDPROCEDURENAME ACTION

-SUBELEMENT

--ISINPUT

--TYPE

[Former Member · Accepted solution]

Hi, Yes you're right about the parameters. I have changed my structure accordingly to stored procedure. But still get Load_Stg_Customer is not a stored procedure. But now the receiving end tells me that they don't have a stored procedure. they have 'Macro' (?).

Do you know anything about 'Macro' in the database instead of Stored Procedure? And how can I do an EXECUTE to call a Macro? Because it seems that it is different when called upon.

I appreciate your help!

Best regards

[Former Member · Accepted solution]

Hi, Do you know how to solve this message? "A column or character expression is larger than the max size"

could it be because I use Type= CHAR? in my structure?

Best regards

[stefan_grube]

> <Insert_Load_Stg_Customer><Load_Stg_Customer><action>INSERT</action>

This should be: <Load_Stg_Customer action="INSERT">

[Former Member]

Do you mean that this should solve the error or was it just a correction among everything else?

[stefan_grube]

> Do you mean that this should solve the error or was it just a correction among everything else?

This is the most obvious error. I do not know whether there are any other errors.

[stefan_grube]

"action" should be an attribute instead of an element.

Change this in your data type.

[Former Member]

Hi,

I've changed it to attribute but still get the same error.

what does this mean? Syntax error: expected something between the beginning of the request and '<'.'

The guys from the receiving end said that the SQL syntax should be before the XML tag?

How is that done?

[stefan_grube]

> I've changed it to attribute but still get the same error.

Have you checked the payload, that it is an attribute now?

> what does this mean? Syntax error: expected something between the beginning of the request and '<'.'

I have never found any relation between the error message and the actual issue in JDBC adapter

> The guys from the receiving end said that the SQL syntax should be before the XML tag?

I suppose he is just guessing.

[Former Member]

Hi,

Do you know how to create XMB2DB_RAWSQL format?

I think that is the issue. The database expects another format than xml.

[stefan_grube]

> I think that is the issue. The database expects another format than xml.

I think that you are wrong

[Former Member]

I changed the message protocol from Native SQL String to XML SQL Format and I got this error instead:

Error processing request in sax parser: Unsupported action attribute value '"INSERT"' found in XML document

[stefan_grube]

> I changed the message protocol from Native SQL String to XML SQL Format and I got this error instead:

> Error processing request in sax parser: Unsupported action attribute value '"INSERT"' found in XML document

In my opinion this error message is absolutely clear.

[baskar_gopalakrishnan2]

>>Error processing request in sax parser: Unsupported action attribute value '"INSERT"' found in XML document

JDBC structure accepts action has an attribute not as an element. So you have to change it as stefan pointed.

Example:

<StatementName>

<dbTableName action=u201DINSERTu201D>

<table>realDbTableName</table>

<access>

<col1>val1</col1>

<col2>val2</col2>

</access>

</dbTableName>

</StatementName>

In your case as follows ....

<?xml version="1.0" encoding="UTF-8"?>

<ns0:MT_XXX_v1 mlns:ns0="http://xxx.com/xxx/xxx">

<Insert_Load_Stg_Customer>

<Load_Stg_Customer action="INSERT">

<table>Load_Stg_Customer</table>

<access>

<SequenceNumber>0000000000388146</SequenceNumber>

<ContentType>MDI029</ContentType>

<SubContentType>0</SubContentType>

<TransactionDate_Time>20110321105407</TransactionDate_Time>

<IEReceivedDate_Time>201103211003858</IEReceivedDate_Time>

<SourceVersion>v1</SourceVersion>

</access>

</Load_Stg_Customer>

</Insert_Load_Stg_Customer>

</ns0:MT_XXX_v1>

[Former Member]

Hi,

I have changed it to attribute. But the main issue I think is that the database can't accept SQL XML Format. It only can receive Native SQL String. And if I understand correctly, Native SQL String must be done only by Java mapping, right?

[baskar_gopalakrishnan2]

Please pick in JDBC receiver adapter that message protocol is XML SQL Format. JDBC adapter will convert your XML SQL to Native string at runtime.

[Former Member]

Should I have any Module statement in the adapter as well in order to convert the message into Native SQL String? Or does the adapter itself take care of it?

And also, if SQL XML Format does the converting then why is there an option to use Native SQL?

[baskar_gopalakrishnan2]

>>Should I have any Module statement in the adapter as well in order to convert the message into Native SQL String? Or does the adapter itself take care of it?

Adapter will take care.

>>And also, if SQL XML Format does the converting then why is there an option to use Native SQL

If you want to specify a SQL statement of your choice in the message payload, to be transferred unchanged to the database for processing, select Native SQL String

Refer this link

http://help.sap.com/saphelp_nw73/helpdata/en/48/465581929734d9e10000000a42189d/content.htm

Hope that helps.

Provide your target data types here... We will see what is error?

[Former Member]

Hi, Thanks for answer.

Actually that is the case that the receiver database excpects the SQL statement in the message payload, to be transferred unchanged to the database for processing. That's why I chosed Native SQL String, but I found out that for Native SQL String one must do a Java mapping, true?

And also, you said before that if I use XML SQL Format then the adapter will make it into Native SQL String? If that is the case then I don't need to choose Native SQL String, Right?

Best regards

[stefan_grube]

JDBC adapter transforms the XML to native SQL.

So there is no need that you use SQL XML.

But if you want to use SQL XML, this is also possible with graphical mapping tool.

It is really not difficult to work with JDBC adapter. Just follow the online help.

[Former Member]

ok, now we have take a step forward. It is a stored procedure we're handling. And therefore I must have action='EXECUTE'.

And in the table=Load_Stg_Customer. But I get 'Load_Stg_Customer' is not a stored procedure.

My target structure is:

<?xml version="1.0" encoding="UTF-8"?>

<ns0:MT_XXX_v1 mlns:ns0="http://xxx.com/xxx/xxx">

<Load_Stg_Customer>

<Load_Stg_Customer action=EXECUTE>

<table>Load_Stg_Customer</table>

<access>

<SequenceNumber>0000000000388146</SequenceNumber>

<ContentType>MDI029</ContentType>

<SubContentType>0</SubContentType>

<TransactionDate_Time>20110321105407</TransactionDate_Time>

<IEReceivedDate_Time>201103211003858</IEReceivedDate_Time>

<SourceVersion>v1</SourceVersion>

</access>

</Load_Stg_Customer>

</EXECUTE_Load_Stg_Customer>

</ns0:MT_XXX_v1>