JDBC UPDATE Statement

[Former Member]

Hello,

I'm trying a Proxy-JDBC scenario. I have to select a few fields from a oracle database and then when the records have been sent to a table in R/3, update the same fields with a flag.

I have already created the structues and also written the selecct statement. There are two tables involved where i have too set the flag in one based on conditions from an another table. Can anyone help me with the Update statement?

[bhavesh_kantilal · Accepted solution]

Hi Kieth,

<i>There are two tables involved where i have too set the flag in one based on conditions from an another table.</i>

No Prob, set the TABLENAME tag with the correspoding tablename.

Create STATEMENT tag for each of these updations and use the same.

If the table strucutrues are different then , create it of the following format,

<root>

<statement1> 1..1

<table action=update>

<tablename>table1</tablename1>

<access>

<column1> </column1>

<column2> </column2>

</access>

<key>

<column1> </column1>

<column2> </column2

</key>

</table>

<statement1>

<statement2> 1..1

<tablename>table2</tablename1>

<access>

<c1> <c1>

<c2> <c2>

<c3> <c3>

</acess>

<key>

<c1> </c1>

<c2> </c2>

<c3> </c3>

<key>

</access>

<statement2>

</root>

this is equiavlent to 2 SQL update statements,

Update table1 set column1= column2 = where column1= annd column2=

update table2 set col1= col2= col3 where col1= col2= col3=

You can create any number of STATEMENT level TAGs, and each are converted into the corresponding SQLstatement.

Regards,

Bhavesh

U

[Former Member · Accepted solution]

hi bhavesh ,

Thanks for u r quick response.

but i want to update a fields from one table depending on the conditions specified on the fields second table i.e. :

update tab1 set field = 'value' where tab2.field = 'value' and tab1.field between 'val1' and 'val2';

So how to create message type for this or there is any other way to update fields.

Regards,

Keith.

[bhavesh_kantilal · Accepted solution]

Keith,

How about using a SQL_DML in your ACTION = ...

<root>

<stmt>

<Customers action="SQL_DML">

<access> UPDATE Tab1 SET Field=’$VALUE$’, WHERE TAB2.$FIELD$ between '$VAL1$’ and '$VAL2$’

</access>

<key>

<VALUE>Company</VALUE>

<FIELD>XYZ</FIELD>

<val1> bbb </val1>

<val2> aaa</val2>

</key>

</Customers>

</stmt>

</root>

Regards,

Bhavesh

[bhavesh_kantilal · Accepted solution]

Keith,

the above will be converted into an SQL lik,

<i>UPDATE TAB1 set FIELD='COMPANY' where tab2.xyz between 'bbb' and 'aaa'</i>

Regards,

Bhavesh

[Former Member · Accepted solution]

Hi bhavesh ,

I am using SQL_DML only and the query is :

UPDATE transactions set transactions.FLAG = 'false' WHERE transactions.SDATETIME BETWEEN '10-may-06' and '12-may-06' and EMPCODE = ( SELECT empcode from empmaster where comid ='2000' )

I have tried this query on oracle client and it is working properly but in XI the message status is 'DELIVERING' and mo updation is done.

I am wondering whether nested queries are supported by JDBC RECEIVER adapter.

Regards,

Keith.

[bhavesh_kantilal · Accepted solution]

Keith,

<b>ACTION = SQL_DML</b> will allow <b>only INSERT / UPDATE and DELETE</b>

<b>ACTIOn = SQL_QUERY</b> will allow <b>only SELECT</b>.

though I have not tried this, try <b>ACTION = SQL_QUERY | SQL_DML</b>. Think it would do thr trick.

Do try and let me know.

Regards,

Bhavesh

[Former Member · Accepted solution]

Hi bhavesh,

using ACTION = SQL_QUERY | SQL_DML it is not working , i think i'll go for stored procedure . Do u have any good links for that.

Regards,

Keith.

[bhavesh_kantilal · Accepted solution]

Keith,

Stroed Procedure sounds good to me .

There is a blog by Sriram Vasudevan regarding calling Stored Procedures from a MAXDB. Take a look at it. Some prob with the SDN site, blogs not getting displayed now.

From the online help,

<i><root>

<StatementName>

<storedProcedureName action=” EXECUTE”>

<table>realStoredProcedureeName</table>

<param1 [isInput=”true”] [isOutput=true] type=SQLDatatype>val1</param1>

</storedProcedureName >

</StatementName></i>

<i>action=EXECUTE

Statements with this action result in a stored procedure being executed. The name of the element is interpreted as the name of the stored procedure in the database. If you use the optional <table> element, the value specified here is used as the stored procedure name. This enables you, for example, to define stored procedure names containing non-XML-compatible characters or characters that prevent them from being used in interface definitions in the Integration Builder/PCK. If specified, <table> must be the first element in the block within <dbTableName>.

The elements within the stored procedure are interpreted as parameters. They can optionally have the attribute isInput=“1“ (input parameter) or isOutput=“1“ (output parameter) or both (INOUT parameter). If both attributes are missing, the element is interpreted as an input parameter. The parameter names must be identical to those of the stored procedure definition.

The attribute type=<SQL-Datatype> , which describes the valid SQL data type, is mandatory for all parameter types (IN, OUT, INOUT).

The following SQL data types are supported:

INTEGER, BIT, TINYINT, SMALLINT, BIGINT, FLOAT, REAL, DOUBLE, NUMERIC, DECIMAL, CHAR, VARCHAR, STRING, LONGVARCHAR, DATE, TIME, TIMESTAMP, BINARY, VARBINARY, LONGVARBINARY, BLOB (input and output),CLOB (input and output), CURSOR (output; only in conjunction with the Oracle JDBC driver).

All return values are returned in an XML structure. The results within the stored procedure are returned either as a table or as the element <update_count>. This depends on the SQL statements executed within the stored procedure. The return parameters of a stored procedure are attached in a separate structure.</i>

Regards,

Bhavesh

[former_member431549]

I also think stored procedure would be the way to go on this one, but I believe you have to be on SP15 (or maybe it is SP16) to use a SP with Oracle.

[bhavesh_kantilal]

Tim,

i have used STORED PROCEDURE to an Oracle Database in the RECEIEVR JDBC adapter and I was on sp14.

are you in any way refering to the option of oracle stored procedure being available on sender jdbc adapter from SP 15?

Regards,

bhavesh