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;

asked 24 Jun '11, 08:18

pasha19's gravatar image

pasha19
2906818
accept rate: 14%

edited 24 Jun '11, 08:47

Mark%20Culp's gravatar image

Mark Culp
24.9k10141297


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.

permanent link

answered 24 Jun '11, 08:50

Mark%20Culp's gravatar image

Mark Culp
24.9k10141297
accept rate: 41%

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×28

question asked: 24 Jun '11, 08:18

question was seen: 1,870 times

last updated: 30 Jun '11, 07:58