SQL Syntax error [message #2212] |
Wed, 15 July 2009 19:15 ![Go to next message Go to next message](/theme/default/images/down.png) |
gpatti
Messages: 283 Registered: August 2008
|
Senior Member |
|
|
Tony,
I've managed to generate a SQL syntax error through use of $selections in a where clause. I think there is a bracket missing in the generated SQL.
The sequence of events is:
1. I am starting with a LIST2 transaction and selecting some rows
2. I call a 'dummy' ADD2 transaction so that I can select some additional criteria before using scriptNext() in _cm_pre_insertRecord to call an output task.
This works fine if I select 0 rows or 1 row (giving me output for all rows matching the additonal criteria or the single row I have selected. However, if I select more than one row I get a SQL syntax error.
The call is scriptNext($next_task, $where) with
$where = "crf_type.crf_type_id = '$crf_type_id' AND $selections";
However this generates the following SQL WHERE clause:
WHERE (exam.exam_id='6646' AND exam.rdcaccount_id='7' ) OR ( exam.exam_id='6647' AND exam.rdcaccount_id='7' )) AND crf_type.crf_type_id='T' )
I think there should be a double opening bracket after the WHERE keyword to match the double closing bracket surrounding the $selections criteria that is grouped with the OR clause.
Let me know if you need further information to assist with this.
Thanks,
Graham
|
|
|
|
|
Re: SQL Syntax error [message #2215 is a reply to message #2214] |
Thu, 16 July 2009 06:08 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
AJM
Messages: 2361 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
Here's another idea - instead of using a dummy ADD2 task to provide additional selection criteria before the output task why not try a SEARCH task instead. You can use the new _cm_post_search() method to identify the output task which needs to be called next. I added this functionality as I had a need to enter additional selection criteria before calling an output task as a batch process.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
|
Re: SQL Syntax error [message #2218 is a reply to message #2217] |
Thu, 16 July 2009 09:52 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
AJM
Messages: 2361 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
I have just checked what is happening on my system, and I cannot see the problem you are having. When I press the SUBMIT button in the SEARCH screen and see what is available in the _cm_post_search() method I have the following:
- the $where string contains whatever was selected in the current SEARCH screen.
- the $selection string identifies whatever entries were selected in the screen prior to the SEARCH.
When the OUTPUT1 task is activated these strings are available in the _cm_pre_getData() method as:
- $where contains the $selection string in the SEARCH task.
- $this->sql_search contains the $where string in the SEARCH task.
You might want to try the attached files as I have made some slight alterations since the last release.
(attachment deleted, updated in later post)
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
[Updated on: Thu, 16 July 2009 11:26] Report message to a moderator
|
|
|
|
Re: SQL Syntax error [message #2220 is a reply to message #2219] |
Thu, 16 July 2009 11:41 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
AJM
Messages: 2361 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
This attachment contains the files which were missing is the previous post, so this should not produce the error that you encountered.
The missing $where string passed down from the parent LIST2 task is by design. It is shown in the SEARCH screen as non-editable as it is fixed within the LIST2 task and cannot be changed. Because it is non-editable it is not included in the POST array when the SUBMIT button inside the SEARCH screen is pressed, which also means that it is not included in the $where string produced by the SEARCH screen. This does not matter when the SEARCH screen passes control back to the LIST2 task as the original $where string is still in the LIST2 task, and will be merged with whatever is passed back in the $search string.
In your case you want to trap the contents of $where from the LIST2 task so that it can be added to the $search string which is passed to the next task. You can trap this in the $where string in the _cm_initialse() method. Save it to a class variable so that it is available to you in the _cm_post_search() method.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
Re: SQL Syntax error [message #2222 is a reply to message #2221] |
Thu, 16 July 2009 12:21 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
AJM
Messages: 2361 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
Yes, you can use scriptNext() instead of append2ScriptSequence(). You need to choose the one you want depending on what behaviour is best for you. If the next task is a batch task then append2ScriptSequence() is best.
BTW, I've decided that instead of having to create your own code to save the contents of the $where string which was passed down from the parent LIST2 task it would be much easier if I saved the value inside std.search1.inc so that it is automatically available inside _cm_post_search(). I haved changed line 57 to read as follows:
if (!empty($selection)) {
$dbobject->sql_selection = $selection;
} else {
$dbobject->sql_selection = $where;
} // if
$dbobject->sql_selection will be made available as the $selection argument inside the _cm_post_search() method.
This means that the contents of $where will only be used if $selection is empty. You should not need any custom code inside _cm_initialise() to save the contents of either $where or $selection.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
|
|
|
|
Re: SQL Syntax error [message #2228 is a reply to message #2212] |
Thu, 16 July 2009 13:20 ![Go to previous message Go to previous message](/theme/default/images/up.png) |
gpatti
Messages: 283 Registered: August 2008
|
Senior Member |
|
|
Yes thanks.
I can save $selection and $where in the initialise method as that works ok.
It might just be worth you noting the problem with the variable order in case you get chance to look at it in the future (if you can reproduce it) or in case anyone else comes across it.
But everything is fine for now.
Thanks,
Graham
|
|
|