CODEWITHSUNDEEP
javapowerbuilder
Sanjiv
Sanjiv

Reputation: 1815

Converting PBSELECT to standard SQL

I am trying to convert PowerBuilder stuff(containing PBSELECT statement) to java. I am pretty comfortable to other stuff except PBSELECT statement within it.

I want to convert below PBSELECT statement to standard SQL formate.

PBSELECT( VERSION(400) 
TABLE(NAME="table_barcode" )  
TABLE(NAME="table_barcode_attrib_map" )  
TABLE(NAME="table_barcode_attribute" ) 
COLUMN(NAME="table_barcode.label_name") 
COMPUTE(NAME="IsNull(table_barcode_attrib_map.value,0) pacmed_valid")    
JOIN (LEFT="table_barcode.barcode_id"    OP ="=" RIGHT="table_barcode_attrib_map.barcode_id"    OUTER1 ="table_barcode.barcode_id" )    
JOIN (LEFT="table_barcode_attrib_map.attribute_id"    OP ="=" RIGHT="table_barcode_attribute.attribute_id"    OUTER1 ="table_barcode_attrib_map.attribute_id" ) 
WHERE(    EXP1 ="( ~~"table_barcode~~".~~"barcode_id~~""   OP ="="    EXP2 =":as_barcode_id )"    LOGIC ="and" ) 
WHERE(    EXP1 ="table_barcode_attribute.attribute_name"   OP ="="    EXP2 ="'PACMED VALID FLAG'" ) ) 
ARG(NAME = "as_barcode_id" TYPE = string)

within PBSELECT statement, i am not able understand JOIN clause with OUTER1 and first WHERE containing ~~" pattern.

Please help me converting PBSELECT to standard SQL. Any help highly appreciated.

Upvotes: 0

Views: 1193

Answers (2)

Lila Swan
Lila Swan

Reputation: 1

I'll try to convert it, hope I do it right

DECLARE @as_barcode_id NVarchar (max)
-- Since I don't know argument that passed by the program so I set it as max --

SELECT table_barcode.label_name, 
       IsNull(table_barcode_attrib_map.value,0) AS pacmed_valid
  FROM table_barcode LEFT OUTER JOIN table_barcode_attrib_map
-- Frankly, I still not sure whether it should be LEFT OUTER JOIN OR RIGHT OUTER JOIN --
    ON table_barcode.barcode_id = table_barcode_attrib_map.barcode_id
  LEFT OUTER JOIN table_barcode_attribute 
-- Frankly, I still not sure whether it should be LEFT OUTER JOIN OR RIGHT OUTER JOIN --
    ON table_barcode_attrib_map.attribute_id = table_barcode_attribute.attribute_id
 WHERE table_barcode.barcode_id = @as_barcode_id
   AND table_barcode_attribute.attribute_name = IsNull(table_barcode_attrib_map.value,0)

Upvotes: 0

Terry
Terry

Reputation: 6215

I've seen people write conversion routines for this graphical SQL encoding, but from what I've heard from PowerBuilder engineering staff, this is not possible to do correctly. The conversion derives certain specific information about the conversion from the database driver and connection parameters, so a conversion routine that works in one condition (e.g. one database engine) may not work correctly in another.

As Slapout suggests in the comments (thanks!), my tool PBL Peeper can help. You'll need the database connection parameters that the application used (search the code or INI files for attributes named DBMS), but after that, you can dump the SQL to all the DataWindows (Reports / DataWindow SQL) or get it one at a time (Browse / RMB a DW object / Object Report / DataWindow SQL). It's also a better way to browse around code, especially if you don't have PowerBuilder.

BTW, if you're converting, keep in mind:

  • DataWindows also may implicitly contain specifications for INSERTs, UPDATEs and DELETEs
  • DataWindow SQL (and many other attributes of DataWindows) can be dynamically modified at run time through code
  • Other than SQL, many attributes (like X, Y, Width, Height...) can be non-static values, deriving from formulas that can be built on values from the database, from run time memory variables (e.g. variables in PowerScript), or any other values PowerBuilder can access (day of the week, screen resolution, etc...)
  • Lots of attributes are interdependent (e.g. Y and SlideUp)
  • DataWindows can be generated dynamically from scratch at run time, and that syntax can be generated by code, or pulled from any source like files or database columns (your list of DataWindows from the PowerBuilder Libraries (PBLs) may not be the complete picture)

I've pulled off bigger lists of issues than this, but you get the idea. Don't underestimate your task.

Good luck,

Terry

Upvotes: 1

Related Questions