1. Sivaprakash BKR
  2. PowerBuilder
  3. Saturday, 3 November 2018 09:59 AM UTC

Hello,

Using PowerBuilder 2017 R3, PostgreSQL 11

Need to pass a string array value to the following query, written in an itemchanged event.

Select count(*) into :ll_count from tableA where series in (:ls_array);

Tried:

Method 1:
String         ls_array[]
Long          ll-count

ls_array[1] = 'Value1'
ls_array[2] = 'Value2'

Select Count(*) into :ll_count from TableA where series in (:ls_array) ;

This gives the following error:

 ---------- Compiler: Errors   (15:20:02)
basecommon.pbl(w_entry_voucherseries_master_branch).dw_entry.itemchanged.44: Error       C0044: Variable reference in database statement has unsupported data type.
 ---------- Finished Errors   (15:20:02)

 

Method 2:

   ls_sql ="Select Count(*) from co_voucherseries_master_branch where prefix = ? And voucherseries in (?) "
   PREPARE sqlsa FROM :ls_sql;
   Describe Sqlsa into Sqlda ;
   Declare my_cursor Dynamic Cursor for Sqlsa ;
   SetDynamicParm(Sqlda, 1, ls_prefix)
   ll_ret = SetDynamicParm(Sqlda, 2, {'VHCUSTMAS', 'VHSUPPMAS'})
   Messagebox('ret', ll_ret)
   OPEN DYNAMIC my_cursor using Descriptor Sqlda ;
   //EXECUTE Dynamic Procedure :ll_count, :ls_prefix, :ls_checkseries ;
   FETCH my_cursor INTO :ll_count ;
   CLOSE my_cursor ;

Assigning an array into Parm2 returns -1.

 

Method 3:

String         ls_array
Long          ll_count

ls_array = "'Value1', 'Value2'"

Select Count(*) into :ll_count from TableA where series in (:ls_array) ;

Wrong result.

Any ways to pass arrays and get the result without using datawindow or datastore or database procedure.   Looks like a simple one, but could not achieve it.

Happiness Always
BKR Sivaprakash

 

Accepted Answer
Matthew Balent Accepted Answer Pending Moderation
  1. Friday, 9 November 2018 12:52 PM UTC
  2. PowerBuilder
  3. # Permalink

You might just be better off to create a datawindow object which takes the array as an argument.  Use that in a datastore to retrieve the count.  Often this is just plain easier to do than messing with embedded SQL.

Comment
  1. Sivaprakash BKR
  2. Friday, 9 November 2018 13:03 PM UTC
Thanks Matthew Balent,



Need to create plenty of datawindow objects, which I want to avoid. Writing a query looks easier in certain (such) conditions. Or should find a way to create datawindow objects from sql, run it and return the result.

Any link or code snippet to create such solutions ?

  1. Helpful
  1. Sivaprakash BKR
  2. Monday, 12 November 2018 05:49 AM UTC
Got it.

Created a nvo with a method to accept sql as a parameter, that creates a datawindow object from the passed sql and return the value.

This resolves this issue. Trying to create a more generic solution to return all the columns and rows from the retrieved ds.
  1. Helpful
There are no comments made yet.
Olan Knight Accepted Answer Pending Moderation
  1. Saturday, 3 November 2018 23:02 PM UTC
  2. PowerBuilder
  3. # 1

Method 3:
String         ls_array[], ls_parms
Long          ll-count, ll_idx

ls_array[1] = 'Value1'
ls_array[2] = 'Value2'

ll_count = UpperBound (ls_array)
FOR ll_idx = 1 TO ll_count

   if (ll_idx = 1) THEN
      ls_parms = ls_array [1]
   else
      ls_parms = ls_parms + ', ' + ls_parms [ll_idx]
NEXT

Select Count(*) into :ll_count from TableA where series in (:ls_parms ) ;


Comment
  1. Matthew Balent
  2. Monday, 5 November 2018 12:50 PM UTC
Remember if your list (array) is a string, you will have to enclose the values in single quotes as well.
  1. Helpful
There are no comments made yet.
Sivaprakash BKR Accepted Answer Pending Moderation
  1. Thursday, 8 November 2018 01:39 AM UTC
  2. PowerBuilder
  3. # 2

Thanks Olan , Matthew Balent

Converting from array to string gives the same value as mentioned in Method 3 [ With quotes or without quotes].  That's why I didn't mention that.  Result is the same. 

When I add a quote (') in variable and pass that variable into query, it seems PB is adding one more quote.

Irrespective of creating the value from the array or passing it as a string statement, if we pass the string value with quotes, the result that's passed to RDBMS is 

Say the value is 

ls_array = "'Value1', 'Value2'"
Select Count(*) into :ll_count from TableA where series in (:ls_array) ;

the value passed to RDBMS is

Select Count(*) into :ll_count from TableA where series in (' 'Value1' ', ' 'Value2' ');

The extra quote, added by PB, is the issue, it seems.   [Space between two quotes left intentionally to make it visible]

Is there any settings, that could make this extra quote not to be added ?

Happiness Always
BKR Sivaprakash

 

Comment
  1. Matthew Balent
  2. Friday, 9 November 2018 12:50 PM UTC
My guess is the quote is added to 'escape' the original quote mark in the statement.
  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.