Hello All,
I have some SQL query but to make my question simple as possible, how to use something like this:
sql_file{prop:sql} = 'SET @var := 10; '&|
'select @var;'
it give me error 90 (File system error, you have an error in your SQL syntax…)
when I execute this directly on database, it works OK.
Thanks.
Hello, I can see this is MySQL, but perhaps the problem is the " := " syntax from the Clarion side? I would’ve used ‘set @var = 10;’ etc …you have, ofcourse declared @var as well?
Hello, syntax “:=” is OK.
Now I found that this works:
sql_file{prop:sql} = 'SET @var := 10;'
sql_file{prop:sql} = 'select @var;'
next(sql_file)
message(sql:f1) <- it give me 10, which is OK.
with other words, using 2 “prop:sql” instead of 1.
Also, even without the Error 90, your original code would not have worked. Clarion assumes that any PROP:SQL that does not start with SELECT is not going to try and return any information back to Clarion, so it doesn’t bother telling the ODBC layer “and this is where I want you to put the information returned” (the file buffer). If you want to get info back from a PROP:SQL that does not start with “SELECT” you need to use PROP:SQLRowSet
Can you create a stored procedure that does what you need and returns the results via select? Then just all the stored proc from Clarion with prop:sql.
Hard to know what they are really trying to do, but if they are just trying to get a result set limited by a variable they set beforehand, then there’s not a lot of difference between an approach like:
x{PROP:SQL} = 'set @town = ’ &sometown
x{PROP:SQL} = ‘select customers from v_customer where town = @town’
and
x{PROP:SQL} = ‘CALL customers_in_town(<39>’ & sometown & ‘<39>)’
except for you have to create the stored procedure on the server first which returns the resultset from v_customer. So for simple stuff the first approach might be easier.
You might be right.
I’m guessing the real statements are more involved.
Here is code I try to call:
sql_file{prop:sql} = 'SET @myorder := 0; '&|
'UPDATE my_table '&|
'SET myorder = (@myorder := @myorder + 1) '&|
'WHERE some_id = 3 AND other_id = 1 '&|
'ORDER BY myorder;'
but it does not work (error 90).
It looks like setting (and clearing) variable @myorder and then calling SQL works, so I’m using now:
sql_file{prop:sql} = 'SET @myorder := 0;'
sql_file{prop:sql} = 'UPDATE my_table '&|
'SET myorder = (@myorder := @myorder + 1) '&|
'WHERE some_id = 3 AND other_id = 1 '&|
'ORDER BY myorder;'
and it looks OK.
If there is some better code for this, I would like to know.
Basically, I need to renumber “myorder” values, when they looks like 1,2,4 etc. (and need 1,2,3).
It happends when I delete some record etc.
Thanks.
It does not work, too. It also give me error 90.
If there is some better code for this, I would like to know.
Basically, I need to renumber “myorder” values, when they looks like 1,2,4 etc. (and need 1,2,3).
Oh. ok. What you want is a window function. Have a look at Row_Number()
https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_row-number
You can issue the statement to renumber the lines of the order in question. You don’t really need to just go from where you are now.
At what point does one give up on providing everything SQL through Clarion and easily create exactly what is needed on the backend for one straight forward call?
At what point does one give up on providing everything SQL through Clarion and easily create exactly what is needed on the backend for one straight forward call?
Hard to say. I tend to create a lot of functions/stored procedures on the backend that I call to do stuff (I use PostgreSQL)
I think your SQL will be something like this:
WITH tord AS (
SELECT
ord_Number,
ord_line,
ROW_NUMBER() OVER (
PARTITION BY ord_Line
ORDER BY
ord_line
) new_num
FROM
orderLines
)
UPDATE OrderLines
SET ord_line = new_num
FROM tord
WHERE
tord.ord_number = orderlines.number AND
tord.ord_line = orderlines.number AND
orderlines.order = somevalue;
You just need to put the order number in somevalue. This will renumber to whole order.
Thank you for your idea.
Another way to do it (rather than embedding SQL statements in strings that cannot be checked by the compiler !) would be to use Clarion’s IDE to provide the equivalent of SELECT filters, ORDER BY parameters, etc, etc …
eg: 
A couple (well, four) of things.
First, I wasn’t suggesting PROP:SQLRowset would solve your invalid SQL error, I was pointing out that even if your SQL was valid, you would have to use PROP:SQLRowset to get clarion to retrieve the value.
Second, like Sean, I would use row_number().
Third, I wouldn’t normally ever bother modifying a sequence number like that in the data. I’d leave the numbers as they are, and if the user wanted to see consecutive numbers I would show them a view that used (for example) row_number() over (partition by some_id,other_id order by myorder).
Fourth, I think you need to read the little section on “Compound Statement syntax” in the MySQL manual. Compound statements have to be enclosed in a BEGIN…END: block. I’m not sure what would happen with your original query (you have a select without an into), but if you wanted to do your set followed by an update, that would have to be enclosed in begin…end; Once you have it enclosed within begin…end it should be a valid (compound) sql statement.
Thanks, but I do not use a browse or queue or anything like that.
That project is all source except one (main) window without anything related to my problem.
Thanks anyway.
Thank you for all your suggestions, I re-read Sean’s suggestion (row_number()) after your post and it looks like great solution.
However, I also try with the BEGIN… END like described here: MySQL :: MySQL 8.0 Reference Manual :: 13.6.1 BEGIN … END Compound Statement and here: MySQL BEGIN … END Compound Statement (tutorialspoint.com)
but it does not worked. It looks like it is designed for stored procedures and I do not use them (I use simple SQL query).
Thanks anyway.
Works for me using Oracle, which has more or less the same syntax.
With a little program like this:
PROGRAM
CODE_TABLE FILE,DRIVER(‘ODBC’),OWNER(‘ora,testjon’),NAME(‘TESTJON.“CODE_TABLE”’),PRE(COD)
record RECORD
CT_TYPE_TABLE CSTRING(27),NAME(‘“CT_TYPE_TABLE”’)
CT_DESCRIPTION CSTRING(31),NAME(‘“CT_DESCRIPTION”’)
CT_ASSOCIATED_TABLE CSTRING(3),NAME(‘“CT_ASSOCIATED_TABLE”’)
CT_VALUE CSTRING(5),NAME(‘“CT_VALUE”’)
CT_CODETABLE_ID SHORT,NAME(‘“CT_CODETABLE_ID”’)
END
END
MAP
END
CODE
open(code_table)
code_table{PROP:SQL} = ‘declare some_value number(2); begin some_value := 42; insert into code_table values(’‘1’‘,’‘2’‘,’‘3’‘,’‘4’‘,199); end;’
if error() then message(‘Take 1 ’ & error() & fileerror()).
code_table{PROP:SQL} = ‘insert into code_table values(’‘1’’,‘‘2’’,‘‘3’’,‘‘4’’,199);insert into code_table values(‘‘1’’,‘‘2’’,‘‘3’’,‘‘4’’,399); ’
if error() then message(‘Take 2 ’ & error() & fileerror()).
code_table{PROP:SQL} = ‘insert into code_table values(’‘1’’,‘‘2’’,‘‘3’’,‘‘4’’,199)’
if error() then message('Take 3 ’ & error() & fileerror()).
Only Take 2 (which has the two statements not in a PL/SQL block) raises an error. The first statement, with the declare and the begin…end anonymous block, works fine. Take 3, which has a single sql statement works fine.
Thanks but it does not work with MySQL ODBC, except third one.
Code I used on MSSQL works fine:
sql_file{prop:sql} = 'DECLARE @myorder as int; '&|
'SET @myorder = 0 ; '&|
'UPDATE mytable SET @myorder = myorderOther = @myorder + 1 '&|
'WHERE mytable.id = 123'
but for MySQL not.
I also try to combine BEGIN … END but it does not worked, too.
Like said, I will use row_number(), it works fine.
Thanks anyway.
I’m curious why you don’t want to use stored procedures.
Using them would eliminate all of these oddities of using prop:sql for multiple statement batches.