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. answered 24 Jun '11, 08:50 Mark Culp Comment Text Removed
Comment Text Removed
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
(25 Jun '11, 12:21)
Breck Carter
Replies hidden
Hmmm, wouldn't it be nice to have an undo operation.... or at least an undelete... but sadly there isn't one (yet).
(25 Jun '11, 12:29)
Mark Culp
1
Thanks - sounds good. - pasha19 (yesterday) (that was the comment I accidentally deleted... I just discovered it was still open in another tab in Chrome :)
(25 Jun '11, 14:32)
Breck Carter
1
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.
(30 Jun '11, 07:58)
pasha19
|