PowerBuilder.NET not reading Datawindow SQL Syntax properly

Question

Hopefully, someone has come across this issue before, as it has stumped a co-worker and I for a few days.

We successfully converted our project from PowerBuilder 12 Classic to PowerBuilder.NET and things are running decently, but we are experiencing an issue with how PowerBuilder.NET is reading the SQL syntax from a datawindow.

Opening up the datawindow, I copied the Syntax and pasted it into SQL Server 2008 R2, and it did all of it's retrieval. The SQL is below (note that some of the fields are removed to save space)

  SELECT hl7_in.intf_app_parm_id,   
     hl7_in.sending_app,   
     hl7_in.msg_typ,   
     hl7_in.process_ind,   
     hl7_in.hl7_in_seq_no,   
     hl7_in.msg_evnt_typ,  
     hl7_in.wrng_msg_cnt,   
     pt.pt_middle_name,    
     hl7_in.resolved_ind,
     hl7_in.pt_id,   
     hl7_in.hl7_msg_cntl_id,   
     hl7_in.msg_txt,   
     intf_app_parm.app_parm_desc,  
     intf_engine.engine_name  
FROM {oj hl7_in LEFT OUTER JOIN pt ON hl7_in.pt_id = pt.pt_id}, {oj intf_app_parm LEFT OUTER JOIN intf_engine ON intf_app_parm.intf_engine_id = intf_engine.intf_engine_id}  
WHERE ( hl7_in.intf_app_parm_id = intf_app_parm.intf_app_parm_id ) and  
     ( ( intf_app_parm.direction_ind = 'I' ) )

But, when the code is executed during runtime, I get an SQL error (even though the SQL works)

SQLSTATE = 42000
Microsoft SQL Native Client
The multi-part identifier "intf_app_parm.intf_engine_id" could not be found

After trying to figure out what was causing the error (since both the joins on the above SQL work fine), I decided that we should check out the datawindow through the debugger. Well that's what we did, and we figured out why (Please see the below SQL):

SELECT hl7_in.intf_app_parm_id,   
      hl7_in.sending_app,   
      hl7_in.msg_typ,   
      hl7_in.process_ind,   
      hl7_in.hl7_in_seq_no,   
      hl7_in.msg_evnt_typ,  
      hl7_in.wrng_msg_cnt,   
      pt.pt_middle_name,    
      hl7_in.resolved_ind,
      hl7_in.pt_id,   
      hl7_in.hl7_msg_cntl_id,   
      hl7_in.msg_txt,   
      intf_app_parm.app_parm_desc,  
      intf_engine.engine_name  
 FROM hl7_in LEFT OUTER JOIN pt ON hl7_in.pt_id = pt.pt_id 
      LEFT OUTER JOIN intf_engine ON intf_app_parm.intf_engine_id = intf_engine.intf_engine_id  
WHERE ( hl7_in.intf_app_parm_id = intf_app_parm.intf_app_parm_id ) and  
      ( ( intf_app_parm.direction_ind = 'I' ) )

If you take a look at the FROM statement, you will see that PowerBuilder decided to modify that statement by removing the comma (,) after pt.pt_id as well as the beginning part of the second join (intf_app_parm). We decided to check the original PB12 Classic code to see maybe it was a problem there too, but this strange thing doesn't happen there (the retrieve statement is read correctly).

The datawindow itself is not modified at any point during the application, nor is the SQL statement modified either. Anyone that has had experience with PowerBuilder.NET ever see this happen? If so, what did you do to fix the issue (this is happening on more than one data window).

Thank you for your time!

Is the DataWindow using a graphical select? If not that's the first thing I'd try. — Hugh Brackett
Yes we are. I tried converting the datawindow to Syntax Design, and when it was set like that it works fine, but almost all of the columns on the datawindow are missing. — Christopher MacKinnon
What happens if you make a new DataWindow in PB.Net and make that same join in the graphical query painter? — Hugh Brackett
When you say "columns... are missing", do you mean columns on the UI or columns in the data set? If the UI columns are missing, maybe you could make a new DW with SQL in syntax, then edit the DW syntax (i.e. Edit Source) of the original DW, replacing the table() portion with the one from the new DW. — Terry

Rich Bianco Rich Bianco · Accepted Answer · 2011-07-30T16:50:07

What you are probably seeing is syntax that was constructed to conform to ODBC specification. The braces are an ODBC escape sequence. Is it possible you are using an ODBC connection during development (DB Profile) and connecting to the database using native driver in the application, or vice-versa?

The SQL Server ODBC driver will translate any ODBC syntax into T-SQL on the fly, if it is not directly supported by the SQL Server database engine.

Also note, that the difference in SQL isn't the comma being missing it is the curly braces surrounding parts of your where clause in one and not the other. :)

*Editing this many years later I just learned that PB12.5 does not support SQL Server 2008 and up but supports 2000 & 2005 but my PowerBuilder 2017 supports connection to SQL Server 2008, 2002, 2014, 2016 taken directly from the help files. PB 11.5 supports SQL Server 2008 as long as you use the newer provider. We had a problem where you could only put one retrieval argument, trying to add more gives you invalid syntax on column 2 which I have not seen in my career and have used all versions from PB 3 on up.

You can get more information here:

Microsoft ODBC Programmer's Reference: http://msdn.microsoft.com/en-us/library/....

And specifically for ODBC SQL Grammar: http://msdn.microsoft.com/en-us/library/....

PowerBuilder.NET not reading Datawindow SQL Syntax properly

1 Answers