1. Rômulo Sorato
  2. PowerBuilder
  3. Wednesday, 11 September 2019 14:22 PM UTC

Hello.

I´m trying to delete multiple rows in a function passing an array to IN function

and i made this sql :

 

DELETE FROM CONTRATOS WHERE ID IN (:DELETED_IDS)

 

Powerbuilder is returning me an error "variable reference in database statement has unsupported data type"

 

So , how is the right way to delete multiple ids from an array?

Accepted Answer
Rômulo Sorato Accepted Answer Pending Moderation
  1. Thursday, 12 September 2019 18:23 PM UTC
  2. PowerBuilder
  3. # Permalink

Looks like its not possible to use array direct into IN clause, powerbuilder doesn´t allow that in embedded sql.

 

So i just used a loop to delete one by one:

 

	liNdeleted_rows = UpperBound(deleted_ids[] )	 
	       if liNdeleted_rows >  0 then
		     delete_contracts(deleted_ids[]) 
            end if	liNdeleted_rows = UpperBound(deleted_ids[] )	 
	       if liNdeleted_rows >  0 then
		     delete_contracts(deleted_ids[]) 
            end if

 

delete_contracts():

int size
int i
int id
	size = UpperBound (ids)
	FOR  i = 1 TO size
		id = ids[i]
       DELETE FROM CONTRATOS WHERE ID = :id;
      if sqlca.sqlcode < 0 then
        	MessageBox ("Erro ao deletar contrato" , sqlca.sqlerrtext, Exclamation!)
	     exit
	 end if 
    NEXT
Comment
There are no comments made yet.
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Wednesday, 11 September 2019 14:57 PM UTC
  2. PowerBuilder
  3. # 1

Hi,

Which Database are you using?

Which dbparm parameters are you using in your connection to the transaction object (sqlca)

 

Comment
  1. Rômulo Sorato
  2. Thursday, 12 September 2019 18:25 PM UTC
I´m using SQL Anywhere!
  1. Helpful
There are no comments made yet.
Michael Kramer Accepted Answer Pending Moderation
  1. Wednesday, 11 September 2019 15:02 PM UTC
  2. PowerBuilder
  3. # 2

What are the datatypes of CONTRATOS.ID and DELETED_IDS ?

Did you terminate your embedded SQL statement with a semicolon?
Devil is in the detail.

/Michael

Comment
There are no comments made yet.
Olan Knight Accepted Answer Pending Moderation
  1. Wednesday, 11 September 2019 20:43 PM UTC
  2. PowerBuilder
  3. # 3
DELETE FROM CONTRATOS WHERE ID IN (:DELETED_IDS)

Assuming that the ID field in the CONTRATOS table is either numeric or string, and NOT a list, then you need to  parse the list you have:

// For numeric IDs:

ll_max = UpperBound (deleted_ids)
FOR ll_idx = 1 TO ll_max

   IF (ll_idx > 1) THEN
      ls_list_of_ids = ls_list_of_ids  + ", "
   END IF

   ls_list_of_ids = ls_list_of_ids + deleted_ids [ll_idx]

NEXT

ls_cmd = "DELETE FROM CONTRATOS where ID IN (" + ls_list_of_ids  + ") "
execute immediate ls_cmd;

 

// For stringIDs:

ll_max = UpperBound (deleted_ids)
FOR ll_idx = 1 TO ll_max

   IF (ll_idx > 1) THEN
      ls_list_of_ids = ls_list_of_ids  + ", "
   END IF

   ls_list_of_ids = ls_list_of_ids + "'" + deleted_ids [ll_idx] + "'"     // Adding single quotes around the ID

NEXT

ls_cmd = "DELETE FROM CONTRATOS where ID IN (" + ls_list_of_ids  + ") "
execute immediate ls_cmd;

 
Later -

Olan

Comment
  1. Rômulo Sorato
  2. Thursday, 12 September 2019 18:25 PM UTC
Thanks for the alternative solution
  1. Helpful
There are no comments made yet.
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Wednesday, 11 September 2019 21:40 PM UTC
  2. PowerBuilder
  3. # 4

Another idea would be to use a datastore and avoid "embedded sql" in your code, by doing something similar to this:

Steps:

1) You create a datawindow object in the IDE with a string array or number array argument.

2) Then in code, create the datastore and assign the dataobject of 1)

3) Do a retrieve of that datastore, passing a string- or number array as the parameter.

4) Then delete all rows with one line of code: RowsMove()

5) Update the datastore

6) commit or rollback your changes depending on the outcome of the Update() statement.

7) destroy the datastore

// ----------------------------------- CODE -------------------------------

long ll_idList[] // this example assumes your id's are numeric

datastore lds_delete

 

// your code to fill ll_idList[]

.....

.....

 

// 2)

lds_delete = create datastore 

lds_delete.dataobject = 'd_name_of_dw_created_in_step_1'

lds_delete.setTransObject(SQLCA)

 // 3)

if lds_delete.Retrieve(ll_idList) > 0 then

// 4)

lds_delete.RowsMove(1, lds_delete.rowcount(), Primary!, lds_delete, 1, Delete!)

// 5)

if lds_delete.Update() = 1 then

// 6)

COMMIT;

else

// 6)

ROLLBACK;

end if

end if

// 7)

destroy lds_delete

 

Comment
  1. Miguel Leeuwe
  2. Wednesday, 11 September 2019 22:04 PM UTC
So my conclusion here is that it would be best to use Olan's example with the EXECUTE IMMEDIATE statement. That could be done in a function or non visual object (transaction object?). That way you'd have one central place for your sql (at least for your DELETES) and can easily do things like writing logs, etc.

HIH
  1. Helpful
  1. Rômulo Sorato
  2. Thursday, 12 September 2019 14:42 PM UTC
Thanks for the answer but its a complicated solution for such a simple question.

I´m using embed sql for training reasons only.

In my case i cant use dw or datastore functions to delete.
  1. Helpful
  1. Miguel Leeuwe
  2. Thursday, 19 September 2019 22:09 PM UTC
I agree that it's maybe too complicated, but using embedded sql for "training reasons" ... as long as you also teach to try avoiding embedded sql, I guess it's ok. With all respect.

regards.
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.