MySQL Parameterised Update Syntax
In answer to a previous question Arana provided the solution to my MySQL Search Syntax question with the following line:
SQSTRING="SELECT HASH_SALTED,ROWNUM FROM HASH_CHAIN WHERE HASH_SALTED=?TARGHASH"
which works a treat.
I am now trying to update an existing row using a similar line
SQSTRING='UPDATE HASH_CHAIN SET HASH_CONFIRMED_BY=?TARGHAS
but it doesn't work (instead I am prompted for the value of TARGHASH). Presumably this is because either the SELECT or WHERE command can accept the parameter but either the UPDATE or SET command cannot.
So how should I construct the command?
SQSTRING="SELECT HASH_SALTED,ROWNUM FROM HASH_CHAIN WHERE HASH_SALTED=?TARGHASH"
which works a treat.
I am now trying to update an existing row using a similar line
SQSTRING='UPDATE HASH_CHAIN SET HASH_CONFIRMED_BY=?TARGHAS
but it doesn't work (instead I am prompted for the value of TARGHASH). Presumably this is because either the SELECT or WHERE command can accept the parameter but either the UPDATE or SET command cannot.
So how should I construct the command?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Pavel Celba
I've added FoxPro zone to this question as it seems you are calling the query execution from inside FoxPro.
usually parameterized means you only have ?,?,?
with each being a parameter, there should not be named ......
you need to separate the columns you are updating by commas..
update table setup column=?,column2=?,column3
with each being a parameter, there should not be named ......
you need to separate the columns you are updating by commas..
update table setup column=?,column2=?,column3
ASKER
Thanks Pcelba
I have no idea why changing a local var to a private var has that effect (within the SAME function!) but it immediately sorted the problem. Can I speculate that it's because we're calling a remote procedure with the SQL and it's THERE that the local var drops out of scope?
And Arnold, your contribution wasn't relevant to this particular issue but I suspect it's going to come in handy down the line.
Cheers
I have no idea why changing a local var to a private var has that effect (within the SAME function!) but it immediately sorted the problem. Can I speculate that it's because we're calling a remote procedure with the SQL and it's THERE that the local var drops out of scope?
And Arnold, your contribution wasn't relevant to this particular issue but I suspect it's going to come in handy down the line.
Cheers
If you are calling SQLEXEC() in the same function where the LOCAL variable is declared then it should work but if you are passing the SQSTRING to another function which calls SQLEXEC() then it cannot see what's inside the LOCAL variable declared in one or more levels up.
PRIVATE variable is visible from the declaration point down the calling stack so it must work.
PRIVATE variable is visible from the declaration point down the calling stack so it must work.