Displaying Currency with Adaptive Server/SQL Anywhere

SQL Anywhere, also known as Adaptive Server Anywhere, does not provide support to display values as currency.

So, I had to write my own function. Since I’m copying my SQL from Oracle, I want to easily replace the TO_CHAR( amount, ‘$9,999.99’) expressions. So, this function is named TO_MONEY() and has the same signature as TO_CHAR()’s. I just replace CHAR with MONEY in SQL Anywhere.

TO_MONEY( IN ad_value double, IN as_format varchar(50) DEFAULT ‘$9,999.99’ )

ALTER FUNCTION "dba"."to_money"( 
    IN ad_value double,
    IN as_format varchar(50) DEFAULT '$9,999.99' )
RETURNS varchar(50)
DETERMINISTIC
BEGIN
    DECLARE ls_value varchar(50);
    DECLARE ls_v varchar(50);
    DECLARE len integer;
    DECLARE ls_separator char(1);
	
    SET ls_separator = '.';
    
    SET ls_value = CAST( ROUND(ad_value*100,0) AS CHAR );
    SET len = LENGTH( ls_value );

    SET ls_v = RIGHT( ls_value, 2 );
    SET ls_value = SUBSTR( ls_value, 1, len - 2 );
    SET len = len - 2;
    
    WHILE len > 3 LOOP
        SET ls_v = RIGHT( ls_value, 3 ) + ls_separator + ls_v;
        SET ls_value = SUBSTR( ls_value, 1, len - 3 );
        SET len = len - 3;

        SET ls_separator = ',';
    END LOOP;

    IF len > 0 THEN
        SET ls_v = ls_value + ls_separator + ls_v;
    ELSEIF len = 0 THEN
        SET ls_v = '0' + ls_separator + ls_v;
    END IF;

	RETURN '$' + ls_v;
END

Searching PowerBuilder

It is amazing to me that as of version 10.5, Sybase still hasn’t added the ability to search for an object within your application’s libraries. My application has 69 libraries and over 2200 objects, so a manual search is really painful. I’ve brought this issue up with my very expensive support agents, and the answer has always been, ‘There are third-party apps to do that.’

I got so frustrated I even went looking for some of these apps. The best I could find was TechoKitten’s PBL Peeper. It did what I needed, but required more steps than I wanted to take to use on a regular basis. PBSearch looks promising, but provides more functionality than I need. Plus, it’s not free.

So, even though I shouldn’t have to, as much as it galled me that Sybase won’t provide this most basic IDE functionality, I made my own routine to write out the contents of the libraries.

public function of_listLibraries (string as_filepath)

You pass in the path to the file to which you want the library contents written. That’s it.

I call this in the open event of my application. I can’t see that it adds any time at all to the startup. It’s very fast.

Example call:

if of_inIDE() then
    n_cst_pb lnv_pb

    lnv_pb.of_listLibraries( 'E:\cygwin\home\david\pbappcontents.txt' )
end if

The output looks like:

someLibrary.pbl: n_cst_someobject
someLibrary.pbl: n_cst_anotherobject

anotherLibrary.pbl: n_cst_joesobject
anotherLibrary.pbl: n_cst_fredsobject
anotherLibrary.pbl: n_cst_billsobject

This is very efficiently searched using grep. I write my output to the home directory of my cygwin installation. This way I have the library contents and the grep tool in the same place. There are versions of grep for Windows if you don’t want all the unix utilities and functionality cygwin offers.

Here’s the shell shortcut I use for my application search (as):

as: aliased to cat ~/pbappcontents.txt | grep -i $*

Then, at the shell prompt (in cygwin), I can do:

> as name

nfcdwsrv.pbl: n_cst_dwsrv_fullname
nfcstub.pbl: nfc_n_cst_name
nfcstub.pbl: str_name
client_d.pbl: pl_ll_names_syb2ora
client_d.pbl: pl_ll_names_ora2syb

and get back the locations of all the objects containing the word ‘name.’ Very simple.

The only thing cooler would be Sybase building this search capability directly into the IDE. Or allowing us to add it via a customization interface.

Source Code
listlibraries.txt