-
Anthony Mancuso
- PowerBuilder
- Thursday, 9 June 2022 07: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.
Find Questions by Tag
Helpful?
If a reply or comment is helpful for you, please don’t hesitate to click the Helpful button. This action is further confirmation of their invaluable contribution to the Appeon Community.