SQL variable troubles in Sybase central

Classic Servoy
1

Hi, I need some help here:

I want to fire the following SQL in Sybase Central:

DECLARE @oldtable varchar(50)
DECLARE @newtable varchar(50)

-- CHANGE TABLENAME
SET @oldtable = 'klanten'
SET @newtable = 'customers'
-
UPDATE mytable SET tablename = @newtable WHERE tablename = @oldtable;
UPDATE mytable SET property_value = @newtable WHERE property_value = @oldtable AND (content_id = '41' OR content_id = '46' OR content_id = '145' OR content_id = '146' OR content_id = '160');

When I’m firing this code, I’m getting an error at the last line that column @oldtable can not be found.

if I’m uncomment the last line, like this:

DECLARE @oldtable varchar(50)
DECLARE @newtable varchar(50)

-- CHANGE TABLENAME
SET @oldtable = 'klanten'
SET @newtable = 'customers'

UPDATE mytable SET tablename = @newtable WHERE tablename = @oldtable;
-- UPDATE mytable SET property_value = @newtable WHERE property_value = @oldtable AND (content_id = '41' OR content_id = '46' OR content_id = '145' OR content_id = '146' OR content_id = '160');

the SQL is fired with no errors,

if I uncomment the second last line, like this:

DECLARE @oldtable varchar(50)
DECLARE @newtable varchar(50)

-- CHANGE TABLENAME
SET @oldtable = 'klanten'
SET @newtable = 'customers'

-- UPDATE mytable SET tablename = @newtable WHERE tablename = @oldtable;
UPDATE mytable SET property_value = @newtable WHERE property_value = @oldtable AND (content_id = '41' OR content_id = '46' OR content_id = '145' OR content_id = '146' OR content_id = '160');

the SQL is also fired with no errors. So it looks like if you once use a variable you can’t use it the second time again?

2

Harjo,

I think the semicolon at the end of the first update statement is hitting you.
After this a new sql statement is started and the scope of the @oldtable variable ended.

Rob

3

Yes. It’s the semicolon because it ends the statement. Local variables are only accessible within the statement they are declared. Usually they are placed within compound statements (between BEGIN and END keywords), which would look like this:

BEGIN
DECLARE @oldtable varchar(50);
DECLARE @newtable varchar(50);

– CHANGE TABLENAME
SET @oldtable = ‘klanten’;
SET @newtable = ‘customers’;

UPDATE mytable SET tablename = @newtable
WHERE tablename = @oldtable;

UPDATE mytable SET property_value = @newtable
WHERE property_value = @oldtable
AND (content_id = ‘41’ OR content_id = ‘46’ OR
content_id = ‘145’ OR content_id = ‘146’ OR
content_id = ‘160’);
END

You will notice that this query will also execute properly … and is probably better form than just removing the semicolon off the end of the first UPDATE statement.

David

4

oke thanks! works now! :-)

5

oke, an additional question:

Can I start this SQL with a transaction? check if everything is fine, and than commit or rollback?

6

When the autocommit property for the JDBC driver is on, each statement within SQL Anywhere is treated as a transaction unless you explicitly place BEGIN END keywords around a group of statements. In other words, if you type:

SELECT * FROM customers;

…SQL Anywhere will treat that as:

BEGIN
SELECT * FROM customers;
END

…with a COMMIT command being executed when it reaches the END statement. Because you want to use local variables that are accessible to multiple statements we are creating a batch of statements i.e. placing them within a BEGIN END block.

If you have autocommit ON, then a commit will automatically happen if the server successfully reaches the END statement. If you wanted to roll back the transaction, you would need to execute the ROLLBACK statement within the BEGIN END block.

If you have autocommit OFF, then you need to give an explicit COMMIT or ROLLBACK statement. It can be within the BEGIN END block or it can be after it. It doesn’t matter.

Autocommit is on by default in JDBC drivers, but I’m not sure if the Servoy team has turned it off.

David