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

Resolved Schema coded in Data window select

  1. How-to
0
Votes
Undo
  1. Anthony Mancuso
    Anthony Mancuso
  2. PowerBuilder
  3. Thursday, 9 June 2022 19:18 PM UTC

Powerbuilder 2021 ver. 1311. Oracle 12. SQLserver 2019  

We are migrating from Oracle to MSSQLSERVER.  I am encountering an issue with Data Windows.  My objective is to make sure the Data windows function under both Databases without a lot of re-tooling. 

The new MsSqlServer database does not use the default dbo schema. We created another BCBS_DBO.  

So when i look at a data window created in Oracle, i first remove all of the Quoted table names and quotes around column names.  So 

select "WEB_TABLE"."STATUS"  becomes select status.  Sqlserver cannot handle the Quoted column.

 

The above is the data window i created while connected to Oracle.

Wnen i went to look at the select statement behind the data window it looked like the following:

SELECT "WEB_HFAF_CONFIRMATION_NUMBER"."WEB_IDENTITY",
"WEB_HFAF_CONFIRMATION_NUMBER"."WEB_LOCATION",
"WEB_HFAF_CONFIRMATION_NUMBER"."STATUS",
"WEB_HFAF_CONFIRMATION_NUMBER"."USERID",
"WEB_HFAF_CONFIRMATION_NUMBER"."SETDATE",
"WEB_HFAF_CONFIRMATION_NUMBER"."SUBMITTER_NAME",
"WEB_HFAF_CONFIRMATION_NUMBER"."SUBMITTER_TITLE",
"WEB_HFAF_CONFIRMATION_NUMBER"."SUBMITTER_PHONE_NUMBER",
"WEB_HFAF_CONFIRMATION_NUMBER"."SUBMITTER_PHONE_EXT",
"WEB_HFAF_CONFIRMATION_NUMBER"."SUBMITTER_MMIS",
"WEB_HFAF_CONFIRMATION_NUMBER"."SUBMITTER_EMAIL_ADDRESS",
"WEB_HFAF_CONFIRMATION_NUMBER"."E_SIGN",
"WEB_HFAF_CONFIRMATION_NUMBER"."CERTIFIER_NAME",
"WEB_HFAF_CONFIRMATION_NUMBER"."CERTIFIER_PHONE_NUMBER",
"WEB_HFAF_CONFIRMATION_NUMBER"."CERTIFIER_PHONE_EXTENSION",
"WEB_HFAF_CONFIRMATION_NUMBER"."CERTIFIER_EMAIL_ADDRESS",
"WEB_HFAF_CONFIRMATION_NUMBER"."CERTIFIER_TITLE"
FROM "WEB_HFAF_CONFIRMATION_NUMBER"
WHERE ( "WEB_HFAF_CONFIRMATION_NUMBER"."WEB_IDENTITY" = :as_web_identity ) AND
( "WEB_HFAF_CONFIRMATION_NUMBER"."WEB_LOCATION" = :as_web_location )

 

So in order to make it work under SQLSERVER i had to remove the quoted table names and quote column names. SQLSERVER does not like these.

The Update properties were automatically set to update the table with the primary key and all fields.

So after my changes to the select  statement it now looks like the following:

SELECT WEB_IDENTITY,
WEB_LOCATION,
STATUS,
USERID,
SETDATE,
SUBMITTER_NAME,
SUBMITTER_TITLE,
SUBMITTER_PHONE_NUMBER,
SUBMITTER_PHONE_EXT,
SUBMITTER_MMIS,
SUBMITTER_EMAIL_ADDRESS,
E_SIGN,
CERTIFIER_NAME,
CERTIFIER_PHONE_NUMBER,
CERTIFIER_PHONE_EXTENSION,
CERTIFIER_EMAIL_ADDRESS,
CERTIFIER_TITLE
FROM WEB_HFAF_CONFIRMATION_NUMBER
WHERE ( WEB_IDENTITY = :as_web_identity ) AND
( WEB_LOCATION = :as_web_location )

I converted back to graphics successfully, and went back to the data window painter portion successfully.

 My update properties did not change.

I re-retrieved the confirmation number.
Performed an update which worked fine. i retrieved again to check and see if my update worked.

I then disconnected from Oracle and connected to SQLSERVER without changing anything on the data window.

I was able to retrieve data and update a column with no problem from the SQLserver side.

 

Then i tried to look at the select statement behind the data window. I was immediately told it could not recreate the select grapihcally.
But it will still show the syntax of the select statement.

From the Syntax of the select, i tried to go back to graphics and was told it would not work.

I went back to the painter mode of the data window and it worked fine.

I was able to retrieve, and update a column.

So because the schema is different between oracle and SQLSERVER it would not got back to graphics unless i put the schema on the name name in the from clause. Then it would work. 

Why do i need to code the schema in the from clause. It is BCBS_DBO not the normal DBO default. I have never had to code a schema in the select before.  I want these Data windows to function under both databases without having to create the same Data Window one for each server.  It seems like if I put the schema in the file, it will go back to graphics and operate just as well.

 

 

 

 

 

 

 

 

 

 

 

Stored Procedure during Runtime returns -1 instead...
pb.ini Documentation
Responses (3)
Anthony Mancuso
Anthony Mancuso Accepted Answer Pending Moderation
  1. Friday, 10 June 2022 00:30 AM UTC
  2. PowerBuilder
  3. # 1

Mike/John

Thank you both for your responses.  To answer one question, we will be connecting to SQLserver with the MSOLEDBSQL driver. 

We are migrating from Oraacle to MSSQLSERVER.  So when they were created, the data windows used the Quoted identifiers, thus i have to remove them from the data window syntax in order for that data window to function without aborting in SQLServer. The removing of the quotes is not a hardship.

I get what you are saying about just always using the syntax side of the data window painter.  The schema name on the SQLSERVER is not the default of DBO it is BCBS_DBO. So i will check with the DBA about my default schema being BCBS_DBO.I believe he said it was. Ill double check it. 

Another issue  I had was trying to change the Update Properties. If the schema was not on the table name in the from clause, PB will say it cannot find the table name in the drop down of the Update properties (what appears is my table without the schema). It wont automatically set the primary key, i have to do it.  Even though it gives me the error it does allow me to accept what I have without the schema. I wasnt sure if that would cause a problem during execution. Any thoughts on that?

 

Thanks again for your assistance.

Tony

 

Comment
Load more comments
Anthony Mancuso
  1. Anthony Mancuso
  2. Friday, 10 June 2022 14:31 PM UTC
We did try to re-create the pb cat tables under the new schema. It did not seem to have any bearing on what i was working on. Thanks for the thought though.
  1. Helpful
mike S
  1. mike S
  2. Friday, 10 June 2022 14:37 PM UTC
not talking about creating tables; i'm talking about the setting in the database profile setup in PB IDE
  1. Helpful
Anthony Mancuso
  1. Anthony Mancuso
  2. Friday, 10 June 2022 15:12 PM UTC
Oh my bad. I tried that also. It also did not seem to have any bearing on what i was working on. Just to let you know, i did change the DWs to remove the quoted fields. I was able to use the DWs in the app just fine. So the abort i had because of the quotes is cleared up. Also i attempted using the DWs in the app during a test. The performed correctly. They updated the tables correctly also.
  1. Helpful
There are no comments made yet.
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Thursday, 9 June 2022 21:52 PM UTC
  2. PowerBuilder
  3. # 2

To supplement Mike's response...

Your SQL Server dba can enable the "quoted identifiers" setting for a database via the "Options" sidebar selection when right-clicking a database in the SQL Server Mgmt Studio's (SSMS) Object Explorer and selecting Properties... from the popup menu:

You can also tailor your personal options in SSMS by opening the Options window (Menu: Tools -> Options), then navigating to the Query Execution -> SQL Server -> ANSI settings, as shown below:

Below is a link to the online documentation for the DBParm setting Mike refers to:

   https://docs.appeon.com/pb2022/connection_reference/DelimitIdentifier.html

I echo Mike's comment about avoiding the Graphical Query Editor in PB. I highly recommend you only use Syntax.

HTH, John

Comment
There are no comments made yet.
mike S
mike S Accepted Answer Pending Moderation
  1. Thursday, 9 June 2022 19:28 PM UTC
  2. PowerBuilder
  3. # 3

check your driver connections (database profile setup). one of the options is whether to use quoted identifiers.

you need to connect as a user that has their default schema set as BCBS_DBO.  talk to your dba about that, its a setting in sql server.

 

you should mention the database driver you are using.

 

you should have little problem with the datawindows working in both oracle and sql server.  just make sure you use ansi standard sql (coalesce instead of nvl/isnull etc).  

 

going back and forth from graphical to syntax rarely works unless it is a single table only.  use syntax for most multi table joins, graphical writes garbage sql.

 

 

 

Comment
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.