Questions? Feedback?powered byOlark live chat software
Bug 3860

SyntaxFromSQL needs updated to work consistent with dw painter 15 January, 2020

mike S
10 January, 2020
Product: PowerBuilder Category: DataWindow
Version: 2017 R3 Publishing: Public
Classification: Enhancement Priority: P3
Status: Transferring Reason:
Ken Guo 15 January, 2020
Hi Mike,

Thanks for the feedback! I will submit it as a requirement for our product team.
However, due to that we are implementing tasks that have higher priority, we will analyze this requirement after that.

Regards,
Ken
mike S 15 January, 2020
I think that it is simply counting the number of commas in the FROM clause and was never updated by sybase (a database company!) to look at JOIN keyword to see how many tables there are and what the names are.   


Longer term PB should make it easy to construct datawindows from syntax, easily add columns, and easily deconstruct the sql syntax.  It will allow developers to really expand the capabilities of their applications to make them very flexible - add columns dynamically, create dynamic reports, etc.   

The idea of the static datawindow does not work for today's customers who demand flexible applications.  PB has that capability, but the tools to make it work easily and well do not exist.  We need those tools.
Ken Guo 15 January, 2020
Hi Mike,

PB will parse according to different drives and SQL itself. After that, it will generate Syntax. When it is handling some SQLs, it is indeed not that intelligent. This is also an issue that exists in all PB versions.

Currently we still don’t have a plan to improve it. I suggest you use Column ID to work around this issue.

Regards,
Ken
mike S 14 January, 2020
maybe PB doesn't handle different database drivers the same way when doing syntax from sql?  Maybe it gets confused when using now standard join syntax (i am using JOIN, you are using old style from with multiple tables)?
mike S 14 January, 2020
this is what my sql looks like, and my presentation style:

ls_sql = "select invoice.inv_no,invdet.invd_total_cost from invoice
JOIN invdet ON invoice.inv_no = invdet.invd_inv_no AND invoice.inv_co = invdet.invd_inv_co
JOIN company ON invoice.inv_co = company.company_cd"


ls_presentation = "Style(Type=Grid) DataWindow(Units=0 ) column( Font.Face='Calibri'  Font.Height=-10 )  text(Font.Face='Calibri' Font.Height=-10 Font.Weight=700 )"

ls_syntax = sqlca.SyntaxFromSQL( ls_sql, ls_presentation, ls_error)	

=================================================
AND this is the resulting syntax - note that the field names do NOT include the table names:
	column=(type=long updatewhereclause = yes name=inv_no  dbname="inv_no")
	column=(type=decimal(4) updatewhereclause = yes name=invd_total_cost  dbname="invd_total_cost")

Full syntax returned:

release 17;
datawindow(units=0 timer_interval=0 color=1073741824 processing=1 print.margin.bottom=96 print.margin.left=110 print.margin.right=110 print.margin.top=96 print.preview.buttons=no )
table(
	column=(type=long updatewhereclause = yes name=inv_no  dbname="inv_no")
	column=(type=decimal(4) updatewhereclause = yes name=invd_total_cost  dbname="invd_total_cost")
	retrieve="select invoice.inv_no,invdet.invd_total_cost from invoice~r~nJOIN invdet ON invoice.inv_no = invdet.invd_inv_no AND invoice.inv_co = invdet.invd_inv_co~r~nJOIN company ON invoice.inv_co = company.company_cd~r~n")
header(height=136)
detail(height=88)
column(band=detail id=1 x="14" y="8" height="72" width="439" alignment="1" font.face="Calibri" font.height="-10" font.weight="0" font.charset="0" font.pitch="0" font.family="0" font.underline="0" font.italic="0" font.strikethrough="0" border="0" color="0" background.mode="1" background.color="536870912" background.brushmode="0" background.gradient.color="8421504" background.gradient.scale="100" background.gradient.spread="100" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" tooltip.enabled="0" tooltip.icon="0" tooltip.backcolor="134217752" tooltip.textcolor="134217751" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.isbubble="0"  edit.autoselect=yes edit.autohscroll=yes edit.autovscroll=no edit.focusrectangle=no )
text(band=header text="Inv No" x="14" y="8" height="60" width="439" font.face="Calibri" font.height="-10" font.weight="700" font.charset="0" font.pitch="0" font.family="0" font.underline="0" font.italic="0" font.strikethrough="0" border="0" color="0" background.mode="1" background.color="536870912" background.brushmode="0" background.gradient.color="8421504" background.gradient.scale="100" background.gradient.spread="100" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" tooltip.enabled="0" tooltip.icon="0" tooltip.backcolor="134217752" tooltip.textcolor="134217751" tooltip.delay.initial="0" tooltip.delay.visible="32000"  tooltip.isbubble="0" alignment="2"  name=inv_no_t )
column(band=detail id=2 x="467" y="8" height="72" width="462" alignment="1" font.face="Calibri" font.height="-10" font.weight="0" font.charset="0" font.pitch="0" font.family="0" font.underline="0" font.italic="0" font.strikethrough="0" border="0" color="0" background.mode="1" background.color="536870912" background.brushmode="0" background.gradient.color="8421504" background.gradient.scale="100" background.gradient.spread="100" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" tooltip.enabled="0" tooltip.icon="0" tooltip.backcolor="134217752" tooltip.textcolor="134217751" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.isbubble="0"  edit.autoselect=yes edit.autohscroll=yes edit.autovscroll=no edit.focusrectangle=no )
text(band=header text="Invd Total Cost" x="467" y="8" height="60" width="462" font.face="Calibri" font.height="-10" font.weight="700" font.charset="0" font.pitch="0" font.family="0" font.underline="0" font.italic="0" font.strikethrough="0" border="0" color="0" background.mode="1" background.color="536870912" background.brushmode="0" background.gradient.color="8421504" background.gradient.scale="100" background.gradient.spread="100" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" tooltip.enabled="0" tooltip.icon="0" tooltip.backcolor="134217752" tooltip.textcolor="134217751" tooltip.delay.initial="0" tooltip.delay.visible="32000"  tooltip.isbubble="0" alignment="2"  name=invd_total_cost_t )
htmltable(border="0" cellpadding="1" cellspacing="1" generatecss="no" nowrap="no")
Ken Guo 14 January, 2020
Hi Mike,

I am not so clear about your issue, can you elaborate on it? It would be better if you could provide the screenshots and point out where the problem is.

I tried to use ls_syntax = sqlca.SyntaxfromSQL(‘select employee.emp_id, department.dept_name  from employee,department where department.dept_id = employee.dept_id;’) and it returns the following string:

release 17;
datawindow(units=0 timer_interval=0 color=1073741824 processing=0 print.margin.bottom=0 print.margin.left=0 print.margin.right=0 print.margin.top=0 print.preview.buttons=no )
table(
                column=(type=long updatewhereclause = yes name=employee_emp_id  dbname="employee.emp_id")
                column=(type=char(40) updatewhereclause = yes name=department_dept_name  dbname="department.dept_name")
                retrieve="select employee.emp_id, department.dept_name  from employee,department where department.dept_id = employee.dept_id;")
...

After that, I used DW.create(ls_syntax, ‘’, ls_err), and it returns the following dw Syntax,Can you tell me exactly what the problem is?
...
table(column=(type=long updatewhereclause=yes name=employee_emp_id dbname="employee.emp_id"  )
column=(type=char(40) updatewhereclause=yes name=department_dept_name dbname="department.dept_name"  )
retrieve="select employee.emp_id, department.dept_name  from employee,department where department.dept_id = employee.dept_id;"
)
text(name=employee_emp_id_t band=header font.charset="0" font.face="Tahoma" font.family="2" font.height="-9" font.italic="0" font.pitch="2" font.strikethrough="0" font.weight="700" font.underline="0" tooltip.enabled="0" tooltip.isbubble="0" tooltip.textcolor="134217751" tooltip.backcolor="134217752" tooltip.icon="0" tooltip.delay.visible="32000" tooltip.delay.initial="0" background.mode="1" background.color="536870912" background.gradient.color="8421504" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.length="100" background.gradient.repetition.count="0" background.gradient.scale="100" background.gradient.spread="100" color="0" alignment="2" border="0" x="5" y="4" height="112" width="331" text="Employee
ID" )
text(name=department_dept_name_t band=header font.charset="0" font.face="Tahoma" font.family="2" font.height="-9" font.italic="0" font.pitch="2" font.strikethrough="0" font.weight="700" font.underline="0" tooltip.enabled="0" tooltip.isbubble="0" tooltip.textcolor="134217751" tooltip.backcolor="134217752" tooltip.icon="0" tooltip.delay.visible="32000" tooltip.delay.initial="0" background.mode="1" background.color="536870912" background.gradient.color="8421504" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.length="100" background.gradient.repetition.count="0" background.gradient.scale="100" background.gradient.spread="100" color="0" alignment="0" border="0" x="341" y="4" height="56" width="896" text="Department Name" )
column(name=employee_emp_id tag="Identification Number of the employee" band=detail id=1 x="5" y="4" height="60" width="331" color="0" border="0" alignment="1" format="[general]" edit.focusrectangle=no edit.autohscroll=yes edit.autoselect=yes edit.autovscroll=no edit.case=any edit.codetable=no edit.displayonly=no edit.hscrollbar=no edit.imemode=0 edit.limit=0 edit.password=no edit.vscrollbar=no edit.validatecode=no edit.nilisnull=no edit.required=no edit.useellipsis=no criteria.required=no criteria.override_edit=no crosstab.repeat=no background.mode="1" background.color="536870912" background.gradient.color="8421504" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.length="100" background.gradient.repetition.count="0" background.gradient.scale="100" background.gradient.spread="100" tooltip.enabled="0" tooltip.isbubble="0" tooltip.textcolor="134217751" tooltip.backcolor="134217752" tooltip.icon="0" tooltip.delay.visible="32000" tooltip.delay.initial="0" font.charset="0" font.face="Tahoma" font.family="2" font.height="-9" font.italic="0" font.pitch="2" font.strikethrough="0" font.weight="400" font.underline="0" tabsequence=0 )
column(name=department_dept_name tag="Name of the department" band=detail id=2 x="341" y="4" height="60" width="896" color="0" border="0" alignment="0" format="[general]" edit.focusrectangle=no edit.autohscroll=yes edit.autoselect=yes edit.autovscroll=no edit.case=any edit.codetable=no edit.displayonly=no edit.hscrollbar=no edit.imemode=0 edit.limit=40 edit.password=no edit.vscrollbar=no edit.validatecode=no edit.nilisnull=no edit.required=no edit.useellipsis=no criteria.required=no criteria.override_edit=no crosstab.repeat=no background.mode="1" background.color="536870912" background.gradient.color="8421504" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.length="100" background.gradient.repetition.count="0" background.gradient.scale="100" background.gradient.spread="100" tooltip.enabled="0" tooltip.isbubble="0" tooltip.textcolor="134217751" tooltip.backcolor="134217752" tooltip.icon="0" tooltip.delay.visible="32000" tooltip.delay.initial="0" font.charset="0" font.face="Tahoma" font.family="2" font.height="-9" font.italic="0" font.pitch="2" font.strikethrough="0" font.weight="400" font.underline="0" tabsequence=0 )
htmltable(border="0" cellPadding="1" cellSpacing="1" generateCSS="no" noWrap="no" )
...


Regards,
Ken
Chris Pollach 10 January, 2020
Hi Mike;

  Thank you for that feedback. Yes I agree, the dynamic DWO feature could certainly use a GUI "freshen up". I will now transfer this ticket over to the main Engineering Team as an enhancement request.

Regards ... Chris
mike S 10 January, 2020
*Phenomenon:
SyntaxFromSQL builds datawindows in the style of really old PB versions.

the naming of fields when a columns from different tables are selected is the first one is the column name, the second on is the column name PLUS the number 1 (ie col and col1).

while dw painter it prepends the table name with an underscore.  

The dbname in SyntaxFromSQL is just the column name, while in the dw painter it correctly builds it out as "tablename.colname"

Since SyntaxFromSQL does NOT Follow the conventions of current PB versions, it should specify the release that it IS following rather than the current release.  So instead of 17, it should specify 3 as the release.

*Reproduce Steps:


Remarks:
This is very powerful yet easy way to create add-hoc reports from a PB application.  like a lot of other parts of PB, sybase/sap didn't bother in keeping this feature up to date.  If this is NOT something that you plan on keeping up to date, then you do need to let people know.   I can work around this by building my own syntax completely from scratch however it is a fair bit of work to do this.
OS:
All 
Platform:
 
Database Type:
 
Database Version: