Originally posted by: SystemAdmin

I'm curious about a way to update or delete a row but I can't understand it how I can achieve.
I completed some maps to insert and select multiple rows but not update/delete.
I'd appreciated if somebody could give me a 'pointer' to this.

Thanks,
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange

Originally posted by: jvanboga

It really depends on your needs. If you are updating an array or rows, for example, you can define the rows as an output card and point to the table using the command line of -update. Seems like I've also deleted rows from a table using a rule... dblookup("delete ....where....", "-mdq ..."). Can't seem to find my example. Also have deleted as part of a runmap command where the delete and variables are defined in the mdq file.
#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

Thank you very much for your helpful information.

BTW, is there a way to achieve both 'update' and 'insert' with one output card?
It is not clear for me.

Also, do you know whether a functional map require db command line settings, for example, '-DBTYPE or -SOURCE? In my understanding so far, such settings are inherited from the parent map. How can I understand this?

Thanks in advance,
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: SystemAdmin

BTW, I think good example modules related to database access such as insert/update/delete would be enough.
Does anybody know some?
Unfortunately, bundled examples are inadequate in my case.

Thanx,
#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender

Originally posted by: janhess

Have you tried reading the documentation for the relevant database adapter?
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange

Originally posted by: SystemAdmin

Yes, I'm checking some documents such as 'ODBC Adapter','Functions and Expressions', and 'Database Interface Designer' as well as 'Map Designer'.

My problem is that the information I'd like know is scattering.

As an engineer, I'm sure what I want to do is not difficult, it's just db insert/delete/update!
It must be easy, so once I've got simple but enough examples, I would be more familiar with the product.
Unfortunately, I've not found good stuff...

#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender

Originally posted by: janhess

insert - have an output card with the adapter set to the type of database you are connection to. Specify connection parameters in .mdq or as adapter command parameters. Or you can use dblookup and specify the sql statement.

update - in mdq file specify fields for update. Use output card as for insert but specify -UPDATE parameter. Or you can use DBLOOKUP and specify the sql statement.

Delete - use DBLOOKUP and specify the sql statement.
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: SystemAdmin

In the case of 'update' and 'delete', how can primary keys be handled?
Because when I try to update a row, it failed with 'duplicated insert', I'd like to know about how primary keys are handled.
The error means that my map doesn't understand and check existing rows.

I configured a column as a key in Database Interface Designer, but it doesn't work or I'm missing something.

How can I cope with this?

#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange

Originally posted by: janhess

See previous post. To update you need to specify the update fields in the mdq and use the -UPDATE command in the adapter. To delete you need to use DBLOOKUP with delete sql unless you want to delete all content of a table before processing when you can use the -DELETE command.

Read pdf for database interface designer as well.
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

Thank you so much!
You solved my problem and now my maps are working.
I'm grateful for your help.
#DataExchange
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender