4

I am trying to maintain a PowerBuilder program and have no access to the previous programmer who wrote the code. I modified a DataWindow and now it generates source code that does NOT include the table owner (dbo) in the column names in selection. But it DOES include dbo in the WHERE clause. See below.

(Old source code shows dbo.FieldAuxs everywhere in selection and in the where.)

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: Column prefix dbo.FieldAuxs does not match with a table name or alias name used in the query...

My database profile is logging me into the PowerBuilder development environment as me (not the dbo). I believe that is what I want to do.

I have read about setting my SQLCA.DBParm to SQLQualifiers=1, but I see no DBParms input area in the Database Profile Setup. It seems in some version before 12.5 you could type a DBParm value directly into some box. But in 12.5 they just have checkboxes and drop downs that set the value for DBParm. And I do not see a choice that translates into setting a value for SQLQualifiers. According to the documentation: SQLQualifiers=1 --Qualify identifiers with owner names in SQL statements.

I must not have something set up correctly that my DataWindow is auto generating source code that is fully qualifying column names ONLY in the WHERE clause and not in the Selection.

Ideas are welcome!

oscs
  • 41
  • 1
  • 5

3 Answers3

2

The problem you describe is a common one.

Typical Cause - Developers using different database userid's in development mode

One developer logging into the database (in development mode) as the "schema owner" and another developer logging into the database (in development mode) as a standard user (e.g. john_doe).

A developer should generally avoid logging in as schema owner when developing or creating datawindows especially in multiple-schema databases because the datawindows will fail when the user runs the application under a normal (non schema owner) userid.

Even though this is conceptually simple it happens all the time because developers often have access to schema/table owner in development that they use for creating tables, or modifying columns, etc., and then they forget to switch back to a normal userid connection to develop datawindows.

When PowerBuilder qualifies identifiers (from PB12.5 Help)

If the name of the table owner is the same as the name of the user logged in to the database, PowerBuilder does not qualify identifiers with owner names in the SQL statements it generates. So if you need PowerBuilder to qualify owner names you need to log in with a user that does not own the table.

Using the SQLQualifiers database parameter (from PB12.5 Help)

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

Other Solutions & Notes

Converting the datawindow SQL to Syntax will work but I generally try to avoid doing this unless absolutely necessary. The reason why you should not convert to syntax is that you lose some of the magic built into the datawindow. If you are working on an application that supports two or more back-end databases then converting to syntax will require creating a separate set of datawindows for each DBMS. For example consider the left outer join syntax for Oracle compared to MS SQL Server, if coded in syntax the datawindow will work in one database however if left in graphic mode, PowerBuilder automatically uses the proper left outer join syntax based on the DBMS being used. There are many other advantages (magic) that you negate when converting to syntax that go beyond the scope of the original question.

Rich Bianco
  • 4,141
  • 3
  • 29
  • 48
1

The shortest solution is, in the SQL Painter for the DataWindow, go to the menu Design / Convert to Syntax and manually edit the SQL syntax yourself. (Frankly, I find the graphical painter pretty limiting and slows me down more than helps me; this is usually one of my first steps in creating a DataWindow.)

There are probably tips and tweaks that I'm imagining are highly DBMS dependent (and I don't see a DBMS mentioned yet), like assigning your account as a database owner (dbo) of the database concerned, but the above will get you past your current blockage fastest.

Good luck,

Terry.

Terry
  • 6,160
  • 17
  • 16
  • Thanks. I did manually edit the SQL syntax and the error went away. It worried me that I should be able to achieve the same thing with the painter. As I get more familiar with Powerbuilder I will hopefully gain the confidence to manually edit such things. Thanks again! – oscs Nov 06 '12 at 17:59
0

I failed to mention before that we recently upgraded to Powerbuilder 12.5 from 10.5. This is the first time that a data window has been modified since the conversion.

Today I looked carefully at the SQL Painter for this data window and clicked on the 'WHERE' tab. Under column, it said dbo.FieldAuxs.id (which was the fully qualified name that appeared in the WHERE clause). Before, I didn't realize that this was a drop down since the arrow doesn't show until you select this widget. When I dropped it to see the choices, none were fully qualified, i.e. none were prefixed with the table owner dbo). I chose FieldAuxs.id and saved the data window. Now the auto generated select statement consistently does NOT include dbo. as a prefix.

It looks as if dbo.FieldAuxs.id was a artifact of version 10.5 when the former programmer chose to use fully qualified column names in his database profile.

oscs
  • 41
  • 1
  • 5