How to update SQL for external DB

Hi

I am trying to update the external Oracle DB . There is no identifier in the table and no update action in the integration studio.BUT there is the 4 primary key in the table.So i am making the sql in the update action.How do we write the sql for check box value?Please see the below pic, the user can change only for the check box value.


Q:I do not know how to write the sql for check boxes,(which check boxes will be clicked or not)

Thanks 

Hi Em,

I think you are doing mistake in where conditions, multiple where conditions should be connect by an "and" not ",".

below is working fine for me

Dear EM WA,

I believe you are using wrong syntax for SQL.

Your query should look like following.

UPDATE {ITEM_LIST}

SET 

     {ITEM_LIST}.[EXPENSE_FLG]=1 // if its boolean variable in DB

WHERE

      {ITEM_LIST}.[COMPANY_CODE] = @CompanyCd 

      AND

      {ITEM_LIST}.[ITEM_NO] = @KojiNo


Try this out and let us know if its working or not. 

EM WA wrote:

Hi

I am trying to update the external Oracle DB . There is no identifier in the table and no update action in the integration studio.BUT there is the 4 primary key in the table.So i am making the sql in the update action.How do we write the sql for check box value?Please see the below pic, the user can change only for the check box value.


Q:I do not know how to write the sql for check boxes,(which check boxes will be clicked or not)

Thanks 

Hi EM,

As Vikas and Palak shared you shoud correct your where condition. 

But for your original questions you want to save your checkbox values so for that you need them in input parameters of advance sql widget which you cna pass as either three separate variable, in an structure or entity which you are using to bind them in your form.

So just pass the local screen variable which you are using in your form to this advance sql widget and then use that variable in your SQL 

EM WA wrote:

Hi

I am trying to update the external Oracle DB . There is no identifier in the table and no update action in the integration studio.BUT there is the 4 primary key in the table.So i am making the sql in the update action.How do we write the sql for check box value?Please see the below pic, the user can change only for the check box value.


Q:I do not know how to write the sql for check boxes,(which check boxes will be clicked or not)

Thanks 


Hi,


Firstly correct the where clauses I mean no comma only logical condition, operator etc.

and if you want to update your form value to SQL then u must pass the input values to SQL command accordingly.

Hi,

if you want to set values always 'true" then can give value directly, but it can be true or false in that case you need to pass boolean variable using input parameters. But its good practice to pass the values as input parameters instead of giving as static value in query.

Thanks.

Nikhil Gaur wrote:

EM WA wrote:

Hi

I am trying to update the external Oracle DB . There is no identifier in the table and no update action in the integration studio.BUT there is the 4 primary key in the table.So i am making the sql in the update action.How do we write the sql for check box value?Please see the below pic, the user can change only for the check box value.


Q:I do not know how to write the sql for check boxes,(which check boxes will be clicked or not)

Thanks 

Hi EM,

As Vikas and Palak shared you shoud correct your where condition. 

But for your original questions you want to save your checkbox values so for that you need them in input parameters of advance sql widget which you cna pass as either three separate variable, in an structure or entity which you are using to bind them in your form.

So just pass the local screen variable which you are using in your form to this advance sql widget and then use that variable in your SQL 

Hi Nikhil Gaur

I changed the sql as Vikas and Palak said, but "Query returned no rows" appear.

The check boxes values are TEXT type in db, I catch them in the structure giving checkbox value  attribute as boolean and then made mapping.So,The source for the form is local variable(structure type).

Again,Before sql in Update action , i assign the structure value to the entity value by mapping .

  ?Since the check box in db are text, 1 or 0 , i changed false to 0 .Still the row does not come out.

Thanks again ,


Vikas Sharma wrote:

Hi,

if you want to set values always 'true" then can give value directly, but it can be true or false in that case you need to pass boolean variable using input parameters. But its good practice to pass the values as input parameters instead of giving as static value in query.

Thanks.

Hi Vikas Sharma

Thanks .

Can you suggest me where should i check .

I assume the sql is ok ,though still return no row .i do have only primary key.the condition now goes smoothly .

This is the update action.

Assign: being assigned from the local value(structure type) of the form widget to the button local value(entity data type). 


Palak Patel wrote:

Dear EM WA,

I believe you are using wrong syntax for SQL.

Your query should look like following.

UPDATE {ITEM_LIST}

SET 

     {ITEM_LIST}.[EXPENSE_FLG]=1 // if its boolean variable in DB

WHERE

      {ITEM_LIST}.[COMPANY_CODE] = @CompanyCd 

      AND

      {ITEM_LIST}.[ITEM_NO] = @KojiNo


Try this out and let us know if its working or not. 

Thanks Palak


Dear EM,

Update queries will return no rows.
That's why its giving you this information.

Once the data is updated, its done. If you need that data to be coming out of the query then, you can add select statement at the bottom of your query.

Hope its clear for you.

Regards,

Palak Patel wrote:

Dear EM,

Update queries will return no rows.
That's why its giving you this information.

Once the data is updated, its done. If you need that data to be coming out of the query then, you can add select statement at the bottom of your query.

Hope its clear for you.

Regards,


Thanks Palak Patel 

I may be wrong some where, 

I need that data to be displayed in the form correctly, still it isnot happened yet. 

Sorry i m really to new write sql from external db.

Thanks 


EM WA wrote:

Vikas Sharma wrote:

Hi,

if you want to set values always 'true" then can give value directly, but it can be true or false in that case you need to pass boolean variable using input parameters. But its good practice to pass the values as input parameters instead of giving as static value in query.

Thanks.

Hi Vikas Sharma

Thanks .

Can you suggest me where should i check .

I assume the sql is ok ,though still return no row .i do have only primary key.the condition now goes smoothly .

This is the update action.

Assign: being assigned from the local value(structure type) of the form widget to the button local value(entity data type). 


Hi,

As mentioned already, update queries don't return any rows. You should check database manually that data is updated by "update query" or not.

Thanks.


EM WA wrote:

Palak Patel wrote:

Dear EM,

Update queries will return no rows.
That's why its giving you this information.

Once the data is updated, its done. If you need that data to be coming out of the query then, you can add select statement at the bottom of your query.

Hope its clear for you.

Regards,


Thanks Palak Patel 

I may be wrong some where, 

I need that data to be displayed in the form correctly, still it isnot happened yet. 

Sorry i m really to new write sql from external db.

Thanks 



Ok no problems, but if you want to access records after ur update commands,

1. First, update as u mentioned

2. Next action to read the data so you can use aggregate after advanced SQL.