Getting pbselect syntax using querymode after modifying dddw property

[gordleishman]

Hi,

I have a strange issue that I am unable to solve and wondered if anyone has any ideas.

My application uses querymode heavily. In most screens we have dddw objects that have allowedit set to No. When placing a datawindow into querymode we loop through each dddw field and set the allowedit property to Yes to enable it to be used for querying. When coming back out of querymode we then set them back to allowedit = No.

Here is the generic code I use to place datawindows in and out of querymode:

string s_desc,s_colname,s_allowedit
long l_col,l_cnt

s_desc = dw_query.describe("datawindow.querymode")
choose case s_desc
case "no","No"
//set dddw columns to allowedit
l_col = long(dw_query.describe("datawindow.column.count"))
FOR l_cnt = 1 to l_col
s_desc = "#" + string(l_cnt) + ".name"
s_colname = dw_query.describe(s_desc)
s_desc = dw_query.describe(s_colname + ".DDDW.Name")
if s_desc <> "?" then
s_allowedit = dw_query.describe(s_colname + ".DDDW.AllowEdit")
choose case s_allowedit
case "no","No"
dw_query.modify(s_colname + ".DDDW.AllowEdit=Yes")
end choose
end if
NEXT
//put the datawindow into querymode
dw_query.Modify("DataWindow.Table.Sort=''")
dw_query.modify("datawindow.querysort=Yes")
case "yes","Yes"
//retreive based on user query
dw_query.event ue_retrieve()
//take the datawindow out of querymode
dw_query.modify("datawindow.querymode=No")
//set dddw columns back to allowedit = No
l_col = long(dw_query.describe("datawindow.column.count"))
FOR l_cnt = 1 to l_col
s_desc = "#" + string(l_cnt) + ".name"
s_colname = dw_query.describe(s_desc)
s_desc = dw_query.describe(s_colname + ".DDDW.Name")
if s_desc <> "?" then
s_allowedit = dw_query.describe(s_colname + ".DDDW.AllowEdit")
choose case s_allowedit
case "yes","Yes"
dw_query.modify(s_colname + ".DDDW.AllowEdit=No")
end choose
end if
end choose

The first time you run the retrieve like this it works ok, however, on any subsequent retrieves I get an incorrect syntax error - "Incorrect syntax near 'VERSION'"

Looking at the sqlpreview event, this seems to be caused by the select statement now being in PBSELECT format.

Sqlsyntax in sqlpreview is like this on first retrieve in querymode:

SELECT Bill_exasxcode.asxcode , Bill_exasxcode.appdate , Bill_exasxcode.closedate , Bill_exasxcode.instime , Bill_exasxcode.usrname , Bill_exasxcode.bexasxno , Bill_exasxcode.clsite FROM Bill_exasxcode WHERE (((Bill_exasxcode.asxcode = 'property')))

Then like this in subsequent retrieves:

PBSELECT( VERSION(400) TABLE(NAME="Bill_exasxcode" ) COLUMN(NAME="Bill_exasxcode.asxcode") COLUMN(NAME="Bill_exasxcode.appdate") COLUMN(NAME="Bill_exasxcode.closedate") COLUMN(NAME="Bill_exasxcode.instime") COLUMN(NAME="Bill_exasxcode.usrname") COLUMN(NAME="Bill_exasxcode.bexasxno") COLUMN(NAME="Bill_exasxcode.clsite") EXP2 = "(not sorted))" EXP2 = "(not sorted))" EXP2 = "(not sorted))" EXP2 = "(not sorted))" EXP2 = "(not sorted))" EXP2 = "(not sorted))" EXP2 = "(not sorted))" EXP2 = "(not sorted))" EXP2 = "(not sorted))" EXP2 = "(not sorted))" EXP2 = "(not sorted))" EXP2 = "(not sorted))" WHERE(EXP1 = "(((Bill_exasxcode.asxcode" OP = "=" EXP2 = "'property')))") )

If I remove the code to set allowedit back to No I don't get this issue.

Does anyone have any ideas on fixing this. Can I convert the PBSELECT syntax to standard sql syntax somehow?

I am using PB12.6 classic connecting to SQL Server 2012 using SNC ODBC.

Thanks
Gordo