PowerBuilder 12.5 Changed DataWindow Does Not Qualify Column Names With Table Owner

Question

I am trying to support PowerBuilder and do not have access to the previous programmer who wrote the code. I changed DataWindow and now generates source code that DOES NOT include the table owner (dbo) in the column names when selected. But it includes dbo in the WHERE clause. See below.

(The old source code shows dbo.FieldAuxs throughout choice and in which.)

retrieve="PBSELECT( VERSION(400) TABLE(NAME=~"FieldAuxs~" ) COLUMN(NAME=~"FieldAuxs.id~") COLUMN(NAME=~"FieldAuxs.clientid~") COLUMN(NAME=~"FieldAuxs.status~") COLUMN(NAME=~"FieldAuxs.historyear~") WHERE( EXP1 =~"dbo.FieldAuxs.id~" OP =~"=~" EXP2 =~":al_id~" ) ) ARG(NAME = ~"al_id~" TYPE = number) " update="FieldAuxs" updatewhere=1 updatekeyinplace=no arguments=(("al_id", number)) ) 

This results in an error: The column prefix dbo.FieldAuxs does not match the table name or alias used in the query ...

My database profile registers me in the PowerBuilder development environment as I (and not dbo). I believe that this is what I want to do.

I read about setting my SQLCA.DBParm to SQLQualifiers = 1, but I do not see the DBParms input area in setting up the database profile. It seems that in some version prior to 12.5 you can enter the DBParm value directly into some kind of frame. But at 12.5 they only have checkboxes and drop-down lists that set the value for DBParm. And I do not see a choice that translates to setting values ​​for SQLQualifiers. According to the documentation: SQLQualifiers = 1 - Defines identifiers with owner names in SQL statements.

I should not correctly configure that my DataWindow automatically generates source code that fully matches the column names ONLY in the WHERE clause, and not in Selection.

Ideas are welcome!

Answer 1

The problem you are describing is a general one.

A typical reason is developers using different database identifiers in development mode

One of the developers is registered in the database (in development mode) as the "owner of the scheme", and the other developer is registered in the database (in development mode) as a standard user (for example, john_doe).

The developer should generally avoid logging in as the owner of the schema when developing or creating datawindows , especially in databases with multiple schemas, because data windows will fail when the user starts the application under the usual (non-schema owner) user ID.

Although this is conceptually simple, this happens all the time because developers often have access to the owner of the schema / table in development, which they use to create tables or change columns, etc., and then they forget to return to normal userid for developing datawindows.

When PowerBuilder Qualifies Identifiers (from PB12.5 Help)

If the owner name of the table matches the name of the user registered in the database, PowerBuilder does not qualify the identifiers with the owner names in the SQL queries that it generates. Therefore, if you need PowerBuilder to qualify owner names, you need to log in with a user who does not own this table.

Using the SQLQualifiers Database Parameter (from PB12.5 Help)

This option does not work for most databases. According to the documentation for PB 12.5, SQLQualifiers is specific to Sybase DirectConnect DIR only.

Other decisions and notes

Converting SQL data to syntax will work, but I generally try to avoid this if absolutely necessary. The reason why you shouldn't convert to syntax is because you lose some of the magic embedded in the data window. If you are working on an application that supports two or more databases, then to convert to syntax you will need to create a separate data set for each DBMS. For example, consider the syntax of the left outer join for Oracle compared to MS SQL Server, if it is encoded in the syntax, the datawindow will work in one database, but if you leave it in graphical mode, PowerBuilder will automatically use the correct syntax of the left outer join based on the DBMS used . There are many other benefits (magic) that you deny when converting to syntax that go beyond the original question.

Answer 2

The shortest solution: in Illustrator for DataWindow SQL, go to the Design / Convert to Syntax menu and manually edit the SQL syntax yourself. (Honestly, I believe that a graphic artist is quite limiting and slowing me down more than helping me, this is usually one of my first steps in creating a DataWindow.)

There are probably tips and settings that I present that are very dependent on the DBMS (and I still do not see the DBMS mentioned), for example, assigning your account as the owner of the database (dbo) of the corresponding database, but the above will help you overcome faster your current lock.

Good luck

Terry.

Answer 3

I did not mention earlier that we recently upgraded to Powerbuilder 12.5 from 10.5. This is the first time that the data window has been changed since the conversion.

Today I carefully looked at SQL Painter for this data window and clicked on the WHERE tab. The column says dbo.FieldAuxs.id (that was the full name that appeared in the WHERE clause). I didn’t understand that this is a fall, since the arrow does not appear until you select this widget. When I threw it to see the choice, not one of them was fully qualified, i.e. None of them were prefixed by the owner of the dbo table). I selected FieldAuxs.id and saved the data window. Now the automatically generated select statement does NOT consistently include dbo. as a prefix.

It seems that dbo.FieldAuxs.id was an artifact of version 10.5 when the former programmer decided to use full column names in his database profile.