INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Getting pbselect syntax using querymode after modifying dddw property

Getting pbselect syntax using querymode after modifying dddw property

Getting pbselect syntax using querymode after modifying dddw property

(OP)
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


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close