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

Resolved Why SetSqlSelect returns -1 with SQLite connection

  1. Issue
0
Votes
Undo
  1. Sivaprakash BKR
    Sivaprakash BKR
  2. PowerBuilder
  3. Friday, 12 April 2024 06:50 AM UTC

Hello,

Using PB 2022 R3 Build 3289 (64 bit) with PostgreSQL 15 and SQLite 3

We have many reports which constructs required SQL and set it with SetSQLSelect to retrieve the data.  It works perfectly fine when we connect to PostGreSQL 15.  And fails to set the SQL and the command SetSQLSelect returns -1.

On application open, there is an option to select whether to connect PG or SQLite and almost all of the windows work fine with both databases [ except  for one issue.. later-on on that ].  

To view any reports, we construct the required SQL based on the options selected in the report and set it to a datawindow / datastore and return the result set to calling report window.  

This works fine when we connect to PG.   If connected to SQLite the statement 

li_rc = dw_1.SetSQLSelect( ls_sql )                 => Returns -1

Couldn't figure out on what conditions it returns this value.  The help stats that

SetSQLSelect returns 1 if it succeeds and -1 if the SELECT statement cannot be changed. If any argument's value is null, in PowerBuilder and JavaScript the method returns null.

From the above statement, it seems the Select statement cannot be changed, when we connect to SQLite database.

Any idea why this is so?

1.  The constructed is SQL is correct.  We verified it by running against the two databases.  It returns the correct results.
2.  When we create a new datawindow with the constructed SQL, and run it from PB, it returns the correct result.  

Happiness Always
BKR Sivaprakash

 

  1. PowerBuilder (Appeon)
PowerBuilder 2022 R3 - Window Border and Titlebar
Middle mouse button (scroll wheel) click
Responses (1)
Sivaprakash BKR
Sivaprakash BKR Accepted Answer Pending Moderation
  1. Friday, 12 April 2024 12:03 PM UTC
  2. PowerBuilder
  3. # 1

Looks like we've found out the cause of the problem.

 We use 64 bit drive from SQLite ODBC Driver (ch-werner.de)

IN the query, we arrive a computed column like this

Select ( Case When d.finalstatementcategory = 'ME' Then 1
              When d.finalstatementcategory = 'MI' Then 2
              When d.finalstatementcategory = 'TE' Then 3
              When d.finalstatementcategory = 'TI' Then 4
              When d.finalstatementcategory = 'PE' Then 5
              When d.finalstatementcategory = 'PI' Then 6
              When d.finalstatementcategory = 'BL' Then 7
              When d.finalstatementcategory = 'BA' Then 8 End)  as porder
From   ....

Which we expect an integer datatype, but in PB/DW we see char(255) as datatype.  This is the case for all computed columns, where it returns char datatype for all numeric values also.   So we tried

Select (( Case When d.finalstatementcategory = 'ME' Then Cast('1' as integer)
              When d.finalstatementcategory = 'MI' Then Cast ('2' as intger)
              When d.finalstatementcategory = 'TE' Then Cast ('3' as integer)
              When d.finalstatementcategory = 'TI' Then Cast ('4' as integer)
              When d.finalstatementcategory = 'PE' Then Cast ('5' as integer)
              When d.finalstatementcategory = 'PI' Then Cast ('6' as integer)
              When d.finalstatementcategory = 'BL' Then Cast ('7' as integer)
              When d.finalstatementcategory = 'BA' Then Cast ('8' as integer) End))  as porder
From   ....

No. We get only Char datatype only.

Select Cast(( Case When d.finalstatementcategory = 'ME' Then Cast('1' as integer)
              When d.finalstatementcategory = 'MI' Then Cast ('2' as intger)
              When d.finalstatementcategory = 'TE' Then Cast ('3' as integer)
              When d.finalstatementcategory = 'TI' Then Cast ('4' as integer)
              When d.finalstatementcategory = 'PE' Then Cast ('5' as integer)
              When d.finalstatementcategory = 'PI' Then Cast ('6' as integer)
              When d.finalstatementcategory = 'BL' Then Cast ('7' as integer)
              When d.finalstatementcategory = 'BA' Then Cast ('8' as integer) End) as integer)  as porder
From   ...

We get only Char datatype only.

IN original datawindow, we have integer / numeric datatype for few columns, where the (SQLite) query returns values as char datatype.  This mismatch may be reason for the failure for SetSQLSelect statement.

Any solutions / workaround that can thought of...?

Happiness Always
BKR Sivaprakash

 EDIT:  When we read data from database/tables, we get correct datatype(s).  Issue is there only for computed columns.

 

Comment
Roland Smith
  1. Roland Smith
  2. Friday, 12 April 2024 14:37 PM UTC
Perhaps you need to add SQLite to pbodb.ini.
  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.