on 2011 Jun 24 9:18 AM
I am attempting to code a function to report changes in table column values as follows. Is there the equivalent of the PowerBuilder "any" data type in SQL Anywhere or will automatic conversion handle old and new values provided they fit in a varchar(254) in this case? The documentation alludes to SQL Anywhere performing automatic conversions of data types but makes no reference (that I have found) to whether or not this occurs on a stored procedure call. While I do not like relying on automatic conversions -- this may be a case where the savings could be significant over coding multiple functions for every datatype I want to pass through this function. I know the profiler can perform this function however there are issues with data volumes and preserving the actual execution sequence I have not been able to resolve as well as my desired potential to use common text diff utilities on the console log (saved to disk) as part of my testing procedures -- for matching the output of two separate runs.
CREATE PROCEDURE data.p_display_diff ( IN p_column varchar(62), IN p_old varchar(254), IN p_new varchar(254) ) BEGIN MESSAGE STRING (' UPDATE ', p_column) TYPE STATUS TO CONSOLE DEBUG ONLY; IF ((p_old IS NULL) and (p_new IS NOT NULL) THEN MESSAGE STRING (' BEFORE ({null})') TYPE STATUS TO CONSOLE DEBUG ONLY; MESSAGE STRING (' AFTER (', p_new, ')') TYPE STATUS TO CONSOLE DEBUG ONLY; ELSEIF ((p_old IS NOT NULL) and (p_new IS NULL) THEN MESSAGE STRING (' BEFORE (', p_old, ')') TYPE STATUS TO CONSOLE DEBUG ONLY; MESSAGE STRING (' AFTER ({null})') TYPE STATUS TO CONSOLE DEBUG ONLY; ELSEIF p_old <> p_new THEN MESSAGE STRING (' BEFORE (', p_old, ')') TYPE STATUS TO CONSOLE DEBUG ONLY; MESSAGE STRING (' AFTER (', p_new, ')') TYPE STATUS TO CONSOLE DEBUG ONLY; END IF; END;
SQL Anywhere will attempt to do automatic type conversion in all cases so you only need to write one procedure.
FWIW: I'm not sure why you chose varchar(254) in your sample, but if you are unsure how long the string values will be you could easily change the parameters to long varchar and then you do not need to be concerned with string truncation errors.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
oops, I deleted the wrong comment by mistake; it contained a "thank you" from pasha19.
And now here's my comment: NCHAR to CHAR will be lossy; are there any others like that? http://dcx.sybase.com/index.html#1201/en/dbreference/datatypes-s-5442544.html
Thanks - sounds good. - pasha19 (yesterday)
(that was the comment I accidentally deleted... I just discovered it was still open in another tab in Chrome 🙂
It's working as indicated. I will keep in mind the nchar issue but it is not applicable in my case -- thanks again to all.
User | Count |
---|---|
66 | |
11 | |
10 | |
10 | |
9 | |
7 | |
7 | |
6 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.