Question Re: generic function coding issue in Technology Q&A

generic function coding issue

Former Member — Fri, 24 Jun 2011 08:18:49 GMT

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;

Re: generic function coding issue

MarkCulp — Fri, 24 Jun 2011 08:50:38 GMT

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.

Re: generic function coding issue

Breck_Carter — Sat, 25 Jun 2011 12:21:57 GMT

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

Re: generic function coding issue

MarkCulp — Sat, 25 Jun 2011 12:29:22 GMT

Hmmm, wouldn't it be nice to have an undo operation.... or at least an undelete... but sadly there isn't one (yet).

Re: generic function coding issue

Breck_Carter — Sat, 25 Jun 2011 14:32:17 GMT

Thanks - sounds good. - pasha19 (yesterday)

(that was the comment I accidentally deleted... I just discovered it was still open in another tab in Chrome 🙂

Re: generic function coding issue

Former Member — Thu, 30 Jun 2011 07:58:23 GMT

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.