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

Resolved PBTableOwner Issue

  1. New
  2. How-to
0
Votes
Undo
  1. Roland Smith
    Roland Smith
  2. PowerBuilder
  3. Friday, 25 October 2024 18:40 PM UTC

PB/IM 2022 / SQL Server ODBC Connection.

Our main database has dbo as table owner. A lesser used database has a different owner and was giving errors trying to select from the tables. This included viewing in the DB Painter and attempting to create a datawindow.

I changed PBTableOwner to YES in pbodb.ini and now it works.

Unfortunately now the main database gives similar errors because dbo wasn't specified in the query.

How can I work with both databases without having to flip flop this setting?

Tooltip / generic solution - this column
Export PDF from datawindow with content hyperlinks
Accepted Answer
Arnd Schmidt
Arnd Schmidt Accepted Answer Pending Moderation
  1. Friday, 25 October 2024 20:03 PM UTC
  2. PowerBuilder
  3. # Permalink

Hi Roland,

you can set the default schema for the database user in the SQL Server.

Something like:

USE DatabaseA;
ALTER USER [yourUser] WITH DEFAULT_SCHEMA = dbo;
USE DatabaseB;
ALTER USER [yourUser] WITH DEFAULT_SCHEMA = xyz;

 

Comment
Roland Smith
  1. Roland Smith
  2. Friday, 25 October 2024 20:30 PM UTC
Thanks! This is for our Customer Support department so I have suggested that to them. They are using 'sa' to login which isn't a user in the database. I suggested adding 'sa' as a user with the default schema set to the table owner.
  1. Helpful
There are no comments made yet.
Responses (2)
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Friday, 25 October 2024 21:15 PM UTC
  2. PowerBuilder
  3. # 1

Hi Roland / Arnd;

  FYI: You can set any user's "Role" normally in any DBMS via the its Administrator utility. If you assign for example your developers to a DBO role, any inline SQL and DW Painter SQL will not have any owner prefix. For example dbo.Contact will simply become just the Contact table with no owner reference in the generated SQL. This is now what you want - no owner prefix.

  If you do this Role assignment across DBMS vendors, (ie: SA, ASE, SS, Oracle, PG, etc), then the non-owner SQL will work 100% across any DB instance and almost any DBMS vendor (as long as of course their schemas are identical).

  You can see this working in my STD Framework's OrderEntry Demo App which can log into either SA, PG or SS at the login dialogue. All the Apps SQL & DML in the Demo App will execute on any of those three DBMS's. The Demo App uses the PB Demo DB for these three DBMS vendors as provided by Appeon on GitHub:  https://github.com/Appeon/PowerBuilder-Project-Example-Database

For the latest Framework & Demo App news & download:  http://chrispollach.blogspot.com/2024/10/2024r3.html

=> The frameworks Beta version now also supports PB 2025 Beta Preview2 as well.  ;-)

Noe that the Order Entry Demo App also contains PowerServer projects. These are configured to connect to either PG, SA or SS from the Logon dialogue. Note that the various DB Cache definitions are also included in these projects. When deployed, the PS Servers will route the Demo App's SQL to either of these three DBMS / DB instances dynamically and the SQL & DML will all work the same on either DBMS vendor! Interesting .. N'est Pas?

Food for thought!  HTH

Regards .. Chris

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.