Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (3)

Resolved Re: Passing array into an sql

  1. How-to
0
Votes
Undo
  1. Sivaprakash BKR
    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

 

  1. Array
  2. Parameter
  3. PowerBuilder
Datawindow Sort function
PostgreSQL Minimum install size
Accepted Answer
Matthew Balent
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
Sivaprakash BKR
  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
Sivaprakash BKR
  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.
Responses (3)
Sivaprakash BKR
Sivaprakash BKR Accepted Answer Pending Moderation
  1. Thursday, 8 November 2018 01:39 AM UTC
  2. PowerBuilder
  3. # 1

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
Matthew Balent
  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.
Olan Knight
Olan Knight Accepted Answer Pending Moderation
  1. Saturday, 3 November 2018 23:02 PM UTC
  2. PowerBuilder
  3. # 2

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
Matthew Balent
  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.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.