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

Resolved Could not reconstruct the statement graphically

  1. How-to
0
Votes
Undo
  1. Aleš Vojáček
    Aleš Vojáček
  2. PowerBuilder
  3. Friday, 9 September 2022 15:02 PM UTC

Hi all,

in PB12 it seems, that table and column names was case insensitive. If I try to edit dw DataSource I can do it graphically. 

When I migrate to PB2019, 2021, 2022 I cannot edit data source in graphics mode (it displays error :  could not reconstruct the statement graphically ...) If I try to compare migrated datawindow and one created in PB2022 there is difference in casing in datawindows created in PB2022 there is casing same as in databate (MSSQL) . Migrated dws has column and table names in lowercase.

Is there any way how to make PB2022 case insensitive for table and column names?

I tried to disable binding in DB profile, but it did not help.

 

Thank you Ales 

 

EDIT:

Support ticket was created for this issue :

https://www.appeon.com/standardsupport/track/view?id=8969

 

EDIT 2:

Support ticket is not bug anymore it is transfering as feature request to product team for may be some of next product version.

Conclusion as far is : ODBC makes all columns/tables names lowecase while OLEDB/Native Client has same casing as DB for entity names. So if you wont to use graphical designer for dw datasource you have to stick with ODBC or OLEDB/Native Client for whole project. If you want to switch profile from ODBC to OLEDB/Native Client you have to adjust casing properly.

How do I over-ride Windows paste() function?
Autosize Width of Grid DataWindow NOT WORK
Responses (4)
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Friday, 9 September 2022 15:17 PM UTC
  2. PowerBuilder
  3. # 1

Hi Ales ;

  PB is not case sensitive for any names .. however, SS is. Please check with your SS DBA on how they set the case sensitivity in your SS instance.

Regards ... Chris

Comment
Load more comments
Aleš Vojáček
  1. Aleš Vojáček
  2. Friday, 9 September 2022 15:35 PM UTC
if I edit exported dw and change casing in pb select of dw and import that datawindow back, then grahpics editor of datasource working. So that is why I think that problem is only in grahpic editor vs different casing in pb select in DW.

  1. Helpful
Sivaprakash BKR
  1. Sivaprakash BKR
  2. Saturday, 10 September 2022 11:54 AM UTC
I hope SQL Server is case insensitive. I can create column names as Name, Address and query it as Select name, address ... OR is there any settings to make it case sensitive ?

  1. Helpful
Aleš Vojáček
  1. Aleš Vojáček
  2. Saturday, 10 September 2022 17:25 PM UTC
@sivaprakash-bkr I think so too. Tried that.

I think that only if you add quoted identifiers ("Name", "Address",.....) then it must be correct casing, but dit not try that. Oracle works that way I think.

A.
  1. Helpful
There are no comments made yet.
mike S
mike S Accepted Answer Pending Moderation
  1. Friday, 9 September 2022 16:20 PM UTC
  2. PowerBuilder
  3. # 2

do you have enclose table and column names in quotes turned on in the database connection setup ?

Comment
Aleš Vojáček
  1. Aleš Vojáček
  2. Friday, 9 September 2022 16:28 PM UTC
Hi no I have not.
  1. Helpful
Aleš Vojáček
  1. Aleš Vojáček
  2. Friday, 9 September 2022 16:32 PM UTC
And there is no problem with returning data from database (so I can use any casing in SQL query from PB script or Datawindow) and I can get data back. The only problem is, that If I try to open datasource editor on dw which is migrated from PB12 and there is lower casing in datawindows select then it cannot be edited graphicaly. If I create new dw in PB2022 (and 21, 19) it can be edited in graphic mode, but as soon as I rewrite sqlselect in lower case then graphic mod cannot be used.
  1. Helpful
There are no comments made yet.
Sivaprakash BKR
Sivaprakash BKR Accepted Answer Pending Moderation
  1. Saturday, 10 September 2022 12:14 PM UTC
  2. PowerBuilder
  3. # 3

Can you tell us how you connect PB with SQL Server ?  ODBC, OLE-DB, SQL Native, MSOLEDBSQL... 

We've used OLE-DB, SQL Native in PB 11.5 which were migrated to PB 2019R3 without any issues.   At many places we have used different cases in queries, which is working fine without issues.  

Have you checked whether the order of column(s) selection have changed after the upgrade?  Or any insertion of new columns in tables ?

Happiness Always
BKR Sivaprakash

 

Comment
Aleš Vojáček
  1. Aleš Vojáček
  2. Saturday, 10 September 2022 17:22 PM UTC
Hi,

I tied SQL Native and MSOLEDBSQL, I can try others.

There are no new columns in tables between PB12 and PB21 migrated version.

If I export migrated DW into text file adjust casing to correspond DB import dw back then graphical designer works ok. Problem is casing for sure, in PB12 version all columns and table names are lowercased.

If I create new DW in PB21 then I have casing same as in DB and then after reopening it works graphically too. If I export new dw into txt and make table or column names lowercase it graphical designer stops working.

A.

P.S. I'm using same database on same server from PB12 and PB21 and in PB12 graphical designer works ok. So I think, that PB19+ works differently in those situations.

  1. Helpful
Aleš Vojáček
  1. Aleš Vojáček
  2. Monday, 12 September 2022 09:17 AM UTC
Hi,

as soon I got into work today, I tried all possible datasources for MSSQL vs PB.

Graphical designer for datawindows works only for ODBC datasource. (when project is migrated from PB12 -> PB2019+) Other datasources need same casing as database have for table, column names.

That is wierd but at least ODBC works.
  1. Helpful
Sivaprakash BKR
  1. Sivaprakash BKR
  2. Tuesday, 13 September 2022 04:59 AM UTC
I'm not sure whether this will of any use

https://www.webucator.com/article/how-to-check-case-sensitivity-in-sql-server/#:~:text=SQL%20Server%20is%2C%20by%20default%2C%20case%20insensitive%3B%20however%2C,look%20for%20%22CI%22%20or%20%22CS%22%20in%20the%20result.



To check case sensitivity of a database or a table.

  1. Helpful
There are no comments made yet.
Sivaprakash BKR
Sivaprakash BKR Accepted Answer Pending Moderation
  1. Tuesday, 13 September 2022 05:33 AM UTC
  2. PowerBuilder
  3. # 4

Hello,

Yes I can replicate this issue in PB 11.5, PB 2019R3, PB 2022 as well

In PB 2022, SQL Server 2012 SP4, OLE-DB 

1.  Creating a new datawindow.  Selecting four columns which has mixed cases in field names

2.  Created datawindow - Free form with column specification details

3.  Now click the data source, Select Design - Convert to Syntax

4.  Change all column names to lower case

5.  Select Design - Convert to Graphics,  we get the error

 

This change we haven't tried so far in either our migrated applications or in new applications.  

Happiness Always
BKR Sivaprakash

 

Comment
Sivaprakash BKR
  1. Sivaprakash BKR
  2. Tuesday, 13 September 2022 06:11 AM UTC
Hello Aleš Vojáček,

Please open a support ticket for this issue.
  1. Helpful
Aleš Vojáček
  1. Aleš Vojáček
  2. Tuesday, 13 September 2022 06:23 AM UTC
Great explanation,

I tied it with my bad english, I had to use pictures :-)

Nice work.

Thank you.
  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.