1. Paul Shue
  2. PowerBuilder
  3. Thursday, 28 March 2019 15:21 PM UTC

Using PB 10.2.0 Build 8100 and SQL server. I am trying to use embedded SQL and I want to variable-ize the length parameter to SQL server function substring. In my script I tried Substring(column_name, 4, :ll_len) where I set the local long variable ll_len = 4 and then run the embedded SQL. I get no compile error but when I run the result is not based on the length variable I set. Is there a way to do this in script?

 

Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Thursday, 28 March 2019 16:25 PM UTC
  2. PowerBuilder
  3. # 1

Hi Paul;

  It should work OK by substringing the data into a PB "String" variable by using the MID() method. Then pass the PB entire String variable into the inline SQL as one argument.

Regards ... Chris

Comment
There are no comments made yet.
Paul Shue Accepted Answer Pending Moderation
  1. Thursday, 28 March 2019 17:00 PM UTC
  2. PowerBuilder
  3. # 2

I know that Mid is just a PB function that does the same as substring - I am just not seeing how in my script I could do what you are saying. How would I setup my string variable ls_string based on a dynamic length?

 

Comment
  1. Chris Pollach @Appeon
  2. Thursday, 28 March 2019 17:57 PM UTC
The String Variable should take on the length of the Mid() method's return length.
  1. Helpful
There are no comments made yet.
John Fauss Accepted Answer Pending Moderation
  1. Thursday, 28 March 2019 18:24 PM UTC
  2. PowerBuilder
  3. # 3

Paul;

The following PowerScript code executed in a command button's clicked event works for me using PB 2017 R2 and MS SQL Server:

Long ll_count, ll_length = 4

SELECT COUNT(*)
  INTO :ll_count
  FROM dbo.Table t
 WHERE SUBSTRING(t.Name,1,:ll_length) = '????'
 USING SQLCA;

If you still are unable to get this to work, maybe you can show us your code?

-John

Comment
There are no comments made yet.
Paul Shue Accepted Answer Pending Moderation
  1. Thursday, 28 March 2019 18:36 PM UTC
  2. PowerBuilder
  3. # 4

Here is my SQL with the hardcoded length of 4 - The last parameter to substring (see entire

SQL at bottom);

In my script, I declare a local variable

int li_len

li_len = 4

and substitute:

select max(convert(integer, rtrim(substring(workorderrequest.user_seq, 4, :li_len))))

...

This script compiles but does not produce the same value as in the db painter when I submit it with the parameters hardcoded.

select max(convert(integer, rtrim(substring(workorderrequest.user_seq, 4, 4))))
into :ll_wor_count
from workorderrequest
where workorderrequest.siteseq = :siteseq and
year(workorderrequest.wo_req_date) = :li_year and
rtrim(substring(workorderrequest.user_seq, 4, 4)) NOT LIKE '%[^0-9]%'
using sqlca;

Comment
  1. John Fauss
  2. Friday, 29 March 2019 11:43 AM UTC
What are the transaction object (SQLCA) properties when your app is running? Are there any property differences from those in the Database Profile used in the PB IDE? Have you run the app with the transaction object trace enabled (SQLCA.DBMS="SNC TRACE", for example)? What does the trace show?
  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.