Sybernet / Supplied Packages Reference Release 3.00 May 13, 2006

HTTP.JAVASCRIPT

The JAVASCRIPT package provides subprograms for creating commonly used JavaScript functions. Some functions are common to both Oracle and Sybase (like rtrim), some functions are patterned after similar functions in C (like isdigit), and some functions are real subroutines that do something useful (like openWindow).

These are JavaScript functions so they must be bound by <script> and </script> tags if you want to use them in your JavaScript code. The one exception is the subprogram script (which you can use to create your script tags).

Many of these functions rely on other functions in this package; for example, if you want to verify a date field with the validateDate() function, you must also invoke trim() and to_char(). All of the examples in this package also include the subprograms that must be invoked if you want to use that function. Try to avoid including all subprograms because you think this will make your coding easier, and although JavaScript is forgiving if you define the same function more than once, you should take care that you don't do this. Take a minute or two, and do a view source to make sure you are not including validateEmployee() in a while loop (for example).

Summary of Subprograms

SCRIPT

ALERT

ASCII

ASCIITOEBCDIC

AVG

CALENDAR

CHR

CONSTANTS

CONVERT

DATENAME

DATEPART

DECODE

EBCDICTOASCII

EXECUTEIMMEDIATE

GETDATE

GOTO

INITCAP

ISALNUM

ISALPHA

ISDIGIT

ISLOWER

ISSPACE

ISUPPER

ISXDIGIT

LTRIM

LPAD

MAX

MIN

ONES

OPENWINDOW

PATINDEX

REPLACE

ROUND

RPAD

RTRIM

SIGN

SORTTABLE

STD

SUBSTR

SYSDATE

TO_CHAR

TO_DATE

TRANSPOSE

TRUNC

TRIM

VALIDATECOUNTRY

VALIDATEDATE

VALIDATEEMPLOYEE

VALIDATELANGUAGE

VALIDATENUMBER

VALIDATESTATE

VALIDATETIME

Subprogram SCRIPT

The subprogram SCRIPT creates your beginning or ending script tag. The Boolean parameter value determines if you are opening this tag (when true) or ending this tag (when false).

Syntax

Procedure SCRIPT (@value varchar(6));

Example

The following example illustrates how to call SCRIPT:

exec http.javascript.script 'true'
select 'window.document.writeln("Hello World");'
exec http.javascript.script 'false'

Subprogram ALERT

The subprogram ALERT calls the JavaScript function alert() from your Transact-SQL block. Like the latter it accepts a single string parameter

• text

that is automatically escaped or encoded so that your text is actually displayed without causing a JavaScript error.

Syntax

Procedure ALERT (@text varchar(255));

Example

The following example illustrates how to call ALERT:

begin
    if (@@error = 0)
    begin
        exec http.javascript.script 'true'
        exec http.javascript.alert 'Success!'
        exec http.javascript.script 'false'
    end else
    begin
        exec http.javascript.script 'true'
        exec http.javascript.alert 'Oops!'
        exec http.javascript.script 'false'
    end
end

Subprogram ASCII

The subprogram ASCII creates the function ascii() which accepts the following parameters:

• c

where c is the ascii character whose decimal value is returned.

Syntax

Procedure ASCII;

Example

The following example illustrates how to call ASCII:

exec http.javascript.script 'true'
exec http.javascript.ascii
select 'window.document.writeln(ascii("a"));'
exec http.javascript.script 'false'

Subprogram ASCIITOEBCDIC

The subprogram ASCIITOEBCDIC creates a JavaScript array called ASCIITOEBCDIC[] which allows you to translate Ascii to EBCDIC.

Syntax

Procedure ASCIITOEBCDIC;

Example

The following example illustrates how to call ASCIITOEBCDIC:

exec http.javascript.script 'true'
exec http.javascript.asciitoebcdic
select 'var a=ASCIITOEBCDIC[97];'
exec http.javascript.script 'false'

Subprogram AVG

The subprogram AVG creates the function avg() which accepts one or more numeric values.

Syntax

Procedure AVG;

Example

The following example illustrates how to call AVG:

exec http.javascript.script 'true'
exec http.javascript.average
select 'window.document.writeln(avg(1,2,3,4,5,6,7,8,9));'
exec http.javascript.script 'false'

Subprogram CALENDAR (STYLE)

The subprogram CALENDAR creates the function calendar() which accepts the following parameters:

• cell
• style

where cell is the cell on your form that is be updated and style is the format that is used to format this cell. If style is null, your date cell is populated with the STYLE that is passed to this procedure.

Syntax

Procedure CALENDAR (@STYLE INT = 100);

Example

The following example illustrates how to call CALENDAR:

exec http.javascript.script "true"
exec http.javascript.calendar
exec http.javascript.script "false"
select '<form name=form>'
select '<input type=text name=datetime>'
exec http.javascript.calendarLink 'window.document.form.datetime'
select '</form>'

The subprogram calendarLink() can be used to invoke this function if you are satisfied with the default image. To use this you must pass a fully qualified path to this cell's window, form and cell as illustrated above. If you wish to use something else, then you must create your own href tag and image. You also need to call this manually if the nls_date_format can be different between two or more cells. The call to calendarLink() looks something like this:

select '<a href="" onClick="return calendar(window.document.form.datetime);"><img src="/images/calendar.gif" border=0></a>'

Example

In conjunction with the validateDate() function provided in this package (which you should also use), this subprogram makes it possible to enter a valid date manually by entering that date in this cell or selecting that date from an HTML calendar. The calendar() function obviously updates the year, month and date, but attempts to leave the hours, minutes and seconds untouched. Here is how the two are used together:

declare
    @style int
begin
    select @style = 100
    exec http.javascript.script "true"
    exec http.javascript.ltrim
    exec http.javascript.rtrim
    exec http.javascript.trim
    exec http.javascript.to_char
    exec http.javascript.converts
    exec http.javascript.validateDate @style
    exec http.javascript.calendar @style
    exec http.javascript.script "false"
    select '<form name=form>'
    select '<input type="text" name="datetime" onChange="validateDate(this);">'
    exec http.javascript.calendarLink 'window.document.form.datetime'
    select '</form>'
end

Subprogram CHR

The subprogram CHR creates the function chr() which accepts the following parameters:

• x

where x the numeric or ascii value of the character you wish returned.

Syntax

Procedure CHR;

Example

The following example illustrates how to call CHR:

exec http.javascript.script 'true'
exec http.javascript.chr
select 'window.document.writeln(chr(10));'
exec http.javascript.script 'false'

Subprogram CONSTANTS

The subprogram CONSTANTS defines the following constants:

var
    ASCII_NUL              = 0X00       // Null.
,   ASCII_SOH              = 0X01       // Start of heading.
,   ASCII_STX              = 0X02       // Start of text.
,   ASCII_ETX              = 0X03       // End of text.
,   ASCII_EOT              = 0X04       // End of transmission.
,   ASCII_ENQ              = 0X05       // Enquire.
,   ASCII_ACK              = 0X06       // Acknowledge.
,   ASCII_BEL              = 0X07       // Bell (Master Reset on IBM).
,   ASCII_BS               = 0X08       // Back space.
,   ASCII_HT               = 0X09       // Horizontal tab.
,   ASCII_LF               = 0X0A       // Line feed.
,   ASCII_VT               = 0X0B       // Vertical tab.
,   ASCII_FF               = 0X0C       // Form feed.
,   ASCII_CR               = 0X0D       // Carriage return.
,   ASCII_SO               = 0X0E       // Shift out.
,   ASCII_SI               = 0X0F       // Shift in.
,   ASCII_DLE              = 0X10       // Data link escape.
,   ASCII_DC1              = 0X11       // Device control 1.
,   ASCII_DC2              = 0X12       // Device control 2.
,   ASCII_DC3              = 0X13       // Device control 3.
,   ASCII_DC4              = 0X14       // Device control 4.
,   ASCII_NAK              = 0X15       // Negative acknowledge.
,   ASCII_SYN              = 0X16       // Synchronize.
,   ASCII_ETB              = 0X17       // End of transmission block.
,   ASCII_CAN              = 0X18       // Cancel.
,   ASCII_EM               = 0X19       // End of medium.
,   ASCII_SUB              = 0X1A       // Substitute.
,   ASCII_ESC              = 0X1B       // Escape.
,   ASCII_FS               = 0X1C       // File separator.
,   ASCII_GS               = 0X1D       // Group separator.
,   ASCII_RS               = 0X1E       // Record separator.
,   ASCII_US               = 0X1F       // Unit separator.
,   ASCII_SPACE            = 0X20       // Space.
,   ASCII_DOUBLE_QUOTE     = 0X22       // double-quote.
,   ASCII_SINGLE_QUOTE     = 0X27       // single-quote.
,   ASCII_DEL              = 0X7F       // Roubout (delete).
    ;
var
    EBCDIC_NUL             = 0X00       // Null.
,   EBCDIC_SOH             = 0X01       // Start of heading.
,   EBCDIC_STX             = 0X02       // Start of text.
,   EBCDIC_ETX             = 0X03       // End of text.
,   EBCDIC_HT              = 0X05       // Horizontal tab.
,   EBCDIC_DEL             = 0X07       // Rubout (delete).
,   EBCDIC_VT              = 0X0B       // Vertical tab.
,   EBCDIC_FF              = 0X0C       // Form feed.
,   EBCDIC_CR              = 0X0D       // Carriage return.
,   EBCDIC_SO              = 0X0E       // Shift out..
,   EBCDIC_SI              = 0X0F       // Shift in.
,   EBCDIC_DLE             = 0X10       // Data link escape.
,   EBCDIC_DC1             = 0X11       // Device control 1.
,   EBCDIC_DC2             = 0X12       // Device control 2.
,   EBCDIC_DC3             = 0X13       // Device control 3.
,   EBCDIC_NL              = 0X15       // New Line?
,   EBCDIC_BS              = 0X16       // Back space.
,   EBCDIC_CAN             = 0X18       // Cancel.
,   EBCDIC_EM              = 0X19       // End of medium.
,   EBCDIC_FS              = 0X1C       // File separator.
,   EBCDIC_GS              = 0X1D       // Group separator.
,   EBCDIC_RS              = 0X1E       // Record separator
,   EBCDIC_US              = 0X1F       // Unit separator.
,   EBCDIC_LF              = 0X25       // Line feed.
,   EBCDIC_ETB             = 0X26       // End of transmission block.
,   EBCDIC_ESC             = 0X27       // Escape.
,   EBCDIC_ENQ             = 0X2D       // Enquire.
,   EBCDIC_ACK             = 0X2E       // Acknowledge.
,   EBCDIC_BEL             = 0X2F       // Bell (Master Reset on IBM).
,   EBCDIC_SYN             = 0X32       // Synchronize.
,   EBCDIC_EOT             = 0X37       // End of transmission.
,   EBCDIC_DC4             = 0X3C       // Device control 4.
,   EBCDIC_NAK             = 0X3D       // Negative acknowledge.
,   EBCDIC_SUB             = 0X3F       // Substitute.
,   EBCDIC_SPACE           = 0X40       // Space.
,   ECBDIC_SINGLE_QUOTE    = 0X7D       // single-quote.
,   EBCDIC_DOUBLE_QUOTE    = 0X7F       // double-quote.
    ;

Syntax

Procedure CONSTANTS;

Example

The following example illustrates how to call CONSTANTS:

exec http.javascript.script 'true'
exec http.javascript.constants
exec http.javascript.chr
select 'window.document.writeln(chr(ASCII_LF));'
exec http.javascript.script 'false'

Subprogram CONVERT

The subprogram CONVERT creates the function convert() which accepts the following parameters:

• datatype
• date
• style

Syntax

Procedure CONVERT;

Example

The following example illustrates how to call CONVERT:

exec http.javascript.script 'true'
exec http.javascript.getdate
exec http.javascript.rpad
exec http.javascript.substr
exec http.javascript.to_char
exec http.javascript.converts
select 'window.document.writeln("<pre>");'
select 'for (var i=0;i<13;i++)'
select 'window.document.writeln(convert(varchar(30),getdate(),i));'
select 'window.document.writeln("</pre>");'
exec http.javascript.script 'false'

Subprogram DATENAME

The subprogram DATENAME creates the function datename() which accepts the following parameters:

• datepart
• date

Syntax

Procedure DATENAME;

Example

The following example illustrates how to call DATENAME:

exec http.javascript.script 'true'
exec http.javascript.getdate
exec http.javascript.datename
select 'window.document.writeln("<pre>");'
select 'window.document.writeln(datename(year,getdate()));'
select 'window.document.writeln(datename(month,getdate()));'
select 'window.document.writeln(datename(day,getdate()));'
select 'window.document.writeln(datename(weekday,getdate()));'
select 'window.document.writeln(datename(hour,getdate()));'
select 'window.document.writeln(datename(minute,getdate()));'
select 'window.document.writeln(datename(second,getdate()));'
select 'window.document.writeln("</pre>");'
exec http.javascript.script 'false'

Subprogram DATEPART

The subprogram DATEPART creates the function datepart() which accepts the following parameters:

• datepart
• date

Syntax

Procedure DATEPART;

Example

The following example illustrates how to call DATEPART:

exec http.javascript.script 'true'
exec http.javascript.getdate
exec http.javascript.datepart
select 'window.document.writeln("<pre>");'
select 'window.document.writeln(datepart(year,getdate()));'
select 'window.document.writeln(datepart(month,getdate()));'
select 'window.document.writeln(datepart(day,getdate()));'
select 'window.document.writeln(datepart(weekday,getdate()));'
select 'window.document.writeln(datepart(hour,getdate()));'
select 'window.document.writeln(datepart(minute,getdate()));'
select 'window.document.writeln(datepart(second,getdate()));'
select 'window.document.writeln("</pre>");'
exec http.javascript.script 'false'

Subprogram DECODE

The subprogram DECODE creates the function decode() which accepts one or more numeric or string values.

Syntax

Procedure DECODE;

Example

The following example illustrates how to call DECODE:

exec http.javascript.script 'true'
exec http.javascript.decode
select 'window.document.writeln(decode(2,1,"Hello",2,"World","undefined"));'
exec http.javascript.script 'false'

Subprogram EBCDICTOASCII

The subprogram EBCDICTOASCII creates a JavaScript array called ECBDCITOASCII[] which allows you to translate EBCDIC to Ascii.

Syntax

Procedure EBCDICTOASCII;

Example

The following example illustrates how to call EBCDICTOASCII:

exec http.javascript.script 'true'
exec http.javascript.ebcdictoascii
select 'var a=EBCDICTOASCII[97];'
exec http.javascript.script 'false'

Subprogram EXECUTEIMMEDIATE

The subprogram EXECUTEIMMEDIATE creates the function executeImmediate() which accepts the following parameter:

• SQLTEXT

where SQLTEXT is your Transact-SQL text that is to be executed. This function assumes you are calling sp_html_frame so that it can use (and rebuild) your NORTH frame to execute your Transact-SQL block.

Syntax

Procedure EXECUTEIMMEDIATE(@PROCID INT);

Example

The following example illustrates how to call EXECUTEIMMEDIATE:

create procedure executeImmediate(@BUTTON varchar(30)=null)
as
declare
    @procid int
begin
    if (@BUTTON is null)
    begin
        exec http..sp_html_frame @PROCNAME='http..executeImmediate',@CENTER='*'
        return
    end
    if (@BUTTON = 'CENTER')
    begin
        select @procid = @@procid
        exec http.javascript.script 'true'
        exec http.javascript.executeImmediate @procid
        select 'executeImmediate("begin exec http.javascript.script ''true'' exec http.javascript.alert ''HelloWorld'' exec http.javascript.script ''false'' end");'
        exec http.javascript.script 'false'
        return
    end
end

The above example is a little bizarre because you are executing Transact-SQL code from JavaScript which then invokes a JavaScript alert. The fact that we are executing a Sybase stored procedure directly from JavaScript, however, should illustrate the power of this facility.

Subprogram GETDATE

The subprogram GETDATE creates the function getdate() which returns the current date and time as a JavaScript variable.

Syntax

Procedure GETDATE;

Example

The following example illustrates how to call GETDATE:

exec http.javascript.script 'true'
exec http.javascript.getdate
select 'window.document.writeln(getdate());'
exec http.javascript.script 'false'

Subprogram GOTO

The subprogram GOTO creates the function GOTO() which accepts the following parameters:

• CELL

where CELL is the form element that is to receive focus.

Syntax

Procedure GOTO;

Example

The following example illustrates how to call GOTO:

exec http.javascript.script 'true'
exec http.javascript.go
select 'function validate(FORM)                                   '
select '{                                                         '
select '    if (FORM.NAME.length==0)                              '
select '    {                                                     '
select '        alert("Oops!\n \nName is required on this form.");'
select '        GOTO(FORM.NAME);                                  '
select '        return false;                                     '
select '    }                                                     '
select '    return true;                                          '
select '}                                                         '
exec http.javascript.script 'false'
select '<form onSubmit="return validate(this);">'
select '<input type="text" name=NAME>'
select '</form>'

Subprogram INITCAP

The subprogram INITCAP creates the function initcap() which accepts the following parameters:

• string

where string is the value to be operated on. The first character in each word is set to uppercase and the rest are set to lowercase

Syntax

Procedure INITCAP;

Example

The following example illustrates how to call INITCAP:

exec http.javascript.script 'true'
exec http.javascript.islower
exec http.javascript.isupper
exec http.javascript.isspace
exec http.javascript.initcap
select 'window.document.writeln(initcap("hello world"));'
exec http.javascript.script 'false'

Subprogram ISALNUM

The subprogram ISALNUM creates the function isalnum() which accepts the following parameters:

• c

where c is the character to test for alpha numeric.

Syntax

Procedure ISALNUM;

Example

The following example illustrates how to call ISALNUM:

exec http.javascript.script 'true'
exec http.javascript.isdigit
exec http.javascript.islower
exec http.javascript.isupper
exec http.javascript.isalpha
exec http.javascript.isalnum
select 'if (isalnum("a"))'
select '    window.document.writeln("true");'
select 'else'
select '    window.document.writeln("false");'
exec http.javascript.script 'false'

Subprogram ISALPHA

The subprogram ISALPHA creates the function isalpha() which accepts the following parameters:

• c

where c is the character to test for alpha.

Syntax

Procedure ISALPHA;

Example

The following example illustrates how to call ISALPHA:

exec http.javascript.script 'true'
exec http.javascript.islower
exec http.javascript.isupper
exec http.javascript.isalpha
select 'if (isalpha("a"))'
select '    window.document.writeln("true");'
select 'else'
select '    window.document.writeln("false");'
exec http.javascript.script 'false'

Subprogram ISDIGIT

The subprogram ISDIGIT creates the function isdigit() which accepts the following parameters:

• c

where c is the character to test for digit.

Syntax

Procedure ISDIGIT;

Example

The following example illustrates how to call ISDIGIT:

exec http.javascript.script 'true'
exec http.javascript.isdigit
select 'if (isdigit("a"))'
select '    window.document.writeln("true");'
select 'else'
select '    window.document.writeln("false");'
exec http.javascript.script 'false'

Subprogram ISLOWER

The subprogram ISLOWER creates the function islower() which accepts the following parameters:

• c

where c is the character to test for lower-case.

Syntax

Procedure ISLOWER;

Example

The following example illustrates how to call ISLOWER:

exec http.javascript.script 'true'
exec http.javascript.islower
select 'if (islower("a"))'
select '    window.document.writeln("true");'
select 'else'
select '    window.document.writeln("false");'
exec http.javascript.script 'false'

Subprogram ISSPACE

The subprogram ISSPACE creates the function isspace() which accepts the following parameters:

• c

where c is the character to test for white space.

Syntax

Procedure ISSPACE;

Example

The following example illustrates how to call ISSPACE:

exec http.javascript.script 'true'
exec http.javascript.isspace
select 'if (isspace("a"))'
select '    window.document.writeln("true");'
select 'else'
select '    window.document.writeln("false");'
exec http.javascript.script 'false'

Subprogram ISUPPER

The subprogram ISUPPER creates the function isupper() which accepts the following parameters:

• c

where c is the character to test for upper-case.

Syntax

Procedure ISUPPER;

Example

The following example illustrates how to call ISUPPER:

exec http.javascript.script 'true'
exec http.javascript.isupper
select 'if (isupper("a"))'
select '    window.document.writeln("true");'
select 'else'
select '    window.document.writeln("false");'
exec http.javascript.script 'false'

Subprogram ISXDIGIT

The subprogram ISXDIGIT creates the function isxdigit() which accepts the following parameters:

• c

where c is the character to test for hexadecimal.

Syntax

Procedure ISXDIGIT;

Example

The following example illustrates how to call ISXDIGIT:

exec http.javascript.script 'true'
exec http.javascript.isdigit
exec http.javascript.isxdigit
select 'if (isxdigit("a"))'
select '    window.document.writeln("true");'
select 'else'
select '    window.document.writeln("false");'
exec http.javascript.script 'false'

Subprogram LTRIM

The subprogram LTRIM creates the function ltrim() which accepts the following parameters:

• string

where string is the string to be trimmed.

Syntax

Procedure LTRIM;

Example

The following example illustrates how to call LTRIM:

exec http.javascript.script 'true'
exec http.javascript.ltrim
select 'window.document.writeln(ltrim("    Hello World"));'
exec http.javascript.script 'false'

Subprogram LPAD

The subprogram LPAD creates the function lpad() which accepts the following parameters:

• string
• n
• string_pad

where string is left padded to length n with string_pad. If string_pad is omitted, a space will be used as default. RPAD is similar, but pads right instead of left.

Syntax

Procedure LPAD;

Example

The following example illustrates how to call LPAD:

exec http.javascript.script 'true'
exec http.javascript.lpad
select 'window.document.writeln("<pre>");'
select 'for (var i=1;i<15;i++)'
select '    window.document.writeln(lpad("string",i),"<");'
select 'window.document.writeln("</pre>");'
exec http.javascript.script 'false'

Subprogram MAX

The subprogram MAX creates the function max() which accepts one or more numeric values.

Syntax

Procedure MAX;

Example

The following example illustrates how to call MAX:

exec http.javascript.script 'true'
exec http.javascript.maximum
select 'window.document.writeln(max(1,2,3,4,5,6,7,8,9));'
exec http.javascript.script 'false'

Subprogram MIN

The subprogram MIN creates the function min() which accepts one or more numeric values.

Syntax

Procedure MIN;

Example

The following example illustrates how to call MIN:

exec http.javascript.script 'true'
exec http.javascript.minimum
select 'window.document.writeln(min(1,2,3,4,5,6,7,8,9));'
exec http.javascript.script 'false'

Subprogram ONES

The subprogram ONES creates the function ones() which accepts the following parameters:

• x

where x is the numeric value whose number of bits are returned.

Syntax

Procedure ONES;

Example

The following example illustrates how to call ONES:

exec http.javascript.script 'true'
exec http.javascript.ones
select 'window.document.writeln(ones(10));'
exec http.javascript.script 'false'

Subprogram PATINDEX

The subprogram PATINDEX creates the function patindex() which accepts the following parameters:

• pattern
• string
• selectionStart

where pattern is the pattern to search for, string is the string to search, and selectionStart is the starting position in string to start searching.

Syntax

Procedure PATINDEX;

Example

The following example illustrates how to call PATINDEX:

exec http.javascript.script 'true'
exec http.javascript.patindex
select 'window.document.writeln(patindex("%o%","Hello World",0));'
exec http.javascript.script 'false'

Subprogram OPENWINDOW

The subprogram OPENWINOW creates the function openWindow() which accepts the following parameters:

• WindowURL
• WindowName
• width
• height
• scrollbars
• resizable
• toolbar
• location
• directories
• status
• menubar

WindowURL is the URL this window is suppose to invoke. WindowName is the name of this window. width is the width of this window in pixels. height is the height of this window in pixels. scrollbars, resizable, toolbar, location, directories, status, and menubar are Boolean values (1 or 0) which determine whether or not this option is enabled.

Syntax

Procedure OPENWINDOW;

Example

The following example illustrates how to call OPENWINDOW:

exec http.javascript.script 'true'
exec http.javascript.openWindow
select 'openWindow("http://Sybernet.sri.com","Sybernet",300,300,1,0,0,0,0,0,0);'
exec http.javascript.script 'false'

Subprogram REPLACE

The function REPLACE creates the function replace() which accepts the following parameters:

• string
• search_string
• replacement_string

string is the string to be searched. search_string is the string to be found. replacement_string is the string that replaces all occurrences of search_string.

Syntax

Procedure REPLACE;

Example

The following example illustrates how to call REPLACE:

exec http.javascript.script 'true'
exec http.javascript.replacer
select 'window.document.writeln(replace("Hello World","o","i"));'
exec http.javascript.script 'false'

Subprogram ROUND

The subprogram ROUND creates the function round() which accepts the following parameters:

• value
• scale

Syntax

Procedure ROUND;

Example

The following example illustrates how to call ROUND:

exec http.javascript.script 'true'
exec http.javascript.round
select 'window.document.writeln(round(123.456,1));'
exec http.javascript.script 'false'

Subprogram RPAD

The subprogram RPAD creates the function rpad() which accepts the following parameters:

• string
• n
• string_pad

where string is right padded to length n with string_pad. If string_pad is omitted, a space will be used as default. LPAD is similar, but pads left instead of right.

Syntax

Procedure RPAD;

Example

The following example illustrates how to call RPAD:

exec http.javascript.script 'true'
exec http.javascript.rpad
select 'window.document.writeln("<pre>");'
select 'for (var i=1;i<15;i++)'
select '    window.document.writeln(rpad("string",i),"<");'
select 'window.document.writeln("</pre>");'
exec http.javascript.script 'false'

Subprogram RTRIM

The subprogram RTRIM creates the function rtrim() which accepts the following parameters:

• string

where string is the string to be trimmed.

Syntax

Procedure RTRIM;

Example

The following example illustrates how to call RTRIM:

exec http.javascript.script 'true'
exec http.javascript.rtrim
select 'window.document.writeln(rtrim("Hello World    "));'
exec http.javascript.script 'false'

Subprogram SIGN

The subprogram SIGN creates the function sign() which accepts the following parameters:

• x

where x is a numeric value whose sign is returned. 1 is returned for positive values; -1 is returned for negative values; 0 is returned for zero.

Syntax

Procedure SIGN;

Example

The following example illustrates how to call SIGN:

exec http.javascript.script 'true'
exec http.javascript.sign
select 'window.document.writeln(sign(123));'
exec http.javascript.script 'false'

Subprogram SORTTABLE

The subprogram SORTTABLE creates the function sortTable() which accepts the following parameters:

• id
• col

where id is the ID of your table's <tbody> tag and col is the column number to sort.

This function allows you to sort an HTML table from the client. Inspired by code written by Mark Hill at brainjar, this subprogram has been optimized to sort more than a few hundred rows. Clicking a column heading sorts your table in ascending order. Clicking a column heading again sorts your table in descending order. A table of 1600 rows and 6 columns can be sorted in about 10 seconds.

Syntax

Procedure SORTTABLE;

Example

The following example illustrates how to call SORTTABLE:

exec http.javascript.script 'true'
exec http.javascript.sortTable
exec http.javascript.script 'false'
select '<table border=1>'
,      '<thead>'
,      '<tr bgcolor=lightgrey>'
,      '<th><a href="" onClick="return sortTable(''id'',0);">Name</a></th>'
,      '<th><a href="" onClick="return sortTable(''id'',1);">Color</a></th>'
,      '</tr>'
,      '</thead>'
,      '<tbody id="id">'
select '<tr>'
,      '<td>'
,      NAME
,      '</td>'
,      '<td>'
,      COLOR
,      '</td>'
,      '</tr>'
from   http.dbo.HTTP_COLORS
select '</tbody>'
,      '</table>'

Subprogram SUBSTR

The subprogram SUBSTR creates the function substr() which accepts the following parameters:

• string
• start_position
• length

where string is the source string, start_position is the position for extraction, and length is the number of characters to extract. If lenth is omitted, the function substr() returns the entire string extracted from start_position. If start_position is 0, start_position is assumed to be 1. This is different from JavaScript's substring() function because that function is zero-relative and length is the zero-relative column that is to be extracted last.

Syntax

Procedure SUBSTR;

Example

The following example illustrates how to call SUBSTR:

exec http.javascript.script 'true'
exec http.javascript.substr
select 'window.document.writeln(substr("Hello World",2,3));'
exec http.javascript.script 'false'

Subprogram SYSDATE

The subprogram SYSDATE creates the function sysdate() which returns the current date and time as a JavaScript variable.

Syntax

Procedure SYSDATE;

Example

The following example illustrates how to call SYSDATE:

exec http.javascript.script 'true'
exec http.javascript.to_char
exec http.javascript.sysdate
select 'window.document.writeln(to_char(sysdate(),"MON DD YYYY"));'
exec http.javascript.script 'false'

Subprogram TO_DATE (NLS_DATE_FORMAT)

The subprogram TO_DATE creates the function to_date() which accepts the following parameters:

• value
• nls_date_format

Syntax

Procedure TO_DATE (@NLS_DATE_FORMAT VARCHAR(30) = 'Mon DD YYYY HH:MI:SSAM');

Example

The following example illustrates how to call TO_DATE:

exec http.javascript.script 'true'
exec http.javascript.isdigit
exec http.javascript.to_date
select 'window.document.writeln(to_date("11/25/2006","MM/DD/YYYY"));'
exec http.javascript.script 'false'

Subprogram TRANSPOSE

The subprogram TRANSPOSE creates the function transpose() which accepts the following parameters:

• string

where string is the string to be reversed.

Syntax

Procedure TRANSPOSE;

Example

The following example illustrates how to call TRANSPOSE:

exec http.javascript.script 'true'
exec http.javascript.transpose
select 'window.document.writeln(transpose("Hello World"));'
exec http.javascript.script 'false'

Subprogram TO_CHAR

The subprogram TO_CHAR creates the function to_char() which accepts the following parameters:

• value
• format_mask

value can be either a JavaScript number or a JavaScript date that will be converted to a string. The format_mask is optional, but causes this function to call the method toString() which is of little use.

Syntax

Procedure TO_CHAR;

Example

The following example illustrates how to call TO_CHAR:

exec http.javascript.script 'true'
exec http.javascript.lpad
exec http.javascript.rpad
exec http.javascript.substr
exec http.javascript.ltrim
exec http.javascript.rtrim
exec http.javascript.trim
exec http.javascript.to_char
select 'window.document.writeln("<pre>");'
select 'window.document.writeln(to_char(new Date(),"Mon DD YYYY HH12:MIAM"));'
select 'window.document.writeln(to_char(1234567.9,"fm99999999999999"));'
select 'window.document.writeln("</pre>");'
exec http.javascript.script 'false'

Subprogram TRUNC

The subprogram TRUNC creates the function trunc() which accepts the following parameters:

• value
• scale

Syntax

Procedure TRUNC;

Example

The following example illustrates how to call TRUNC:

exec http.javascript.script 'true'
exec http.javascript.trunc
select 'window.document.writeln(trunc(123.456,1));'
exec http.javascript.script 'false'

Subprogram TRIM

The subprogram TRIM creates the function trim() which accepts the following parameters:

• string

where string is the string to be trimmed of spaces.

Syntax

Procedure TRIM;

Example

The following example illustrates how to call TRIM:

exec http.javascript.script 'true'
exec http.javascript.ltrim
exec http.javascript.rtrim
exec http.javascript.trim
select 'window.document.writeln(trim("  Hello World  "));'
exec http.javascript.script 'false'

Subprogram VALIDATECOUNTRY

The subprogram VALIDATECOUNTRY creates functions for looking up country names and country codes. This subprogram creates the following javascript functions:

• validateCountryCode
• validateCountryName

Both functions accept the following parameters:

• CODE
• NAME

All parameters are optional (if that makes sense). Pass a null value for parameters that don't interest you; for example, if all you are interested in is looking up a country name, pass a "null" value for CODE.

Example

The following example illustrates how to call VALIDATECOUNTRY:

exec http.javascript.script 'true'
exec http.javascript.go
exec http.javascript.openWindow
exec http.javascript.validateCountry
exec http.javascript.script 'false'
select '<form name=form>'
select '<input type=text name=code onChange="validateCountryCode(this,this.form.name);">'
select '<input type=text name=name onChange="validateCountryName(this.form.code,this);">'
select '</form>'

Procedure VALIDATEDATE (@STYLE INT = 107);

declare
    @style int
begin
    select @style = 107
    exec http.javascript.script 'true'
    exec http.javascript.ltrim
    exec http.javascript.rtrim
    exec http.javascript.trim
    exec http.javascript.to_char
    exec http.javascript.converts
    exec http.javascript.validateDate @style
    exec http.javascript.script 'false'
    select '<form>'
    select '<input type="text" onChange="validateDate(this);">'
    select '<input type="text" onChange="validateDate(this,100);">'
    select '<input type="text" onChange="validateDate(this,'|| convert(varchar,@style) || ');">'
    select '</form>'
end

Subprogram VALIDATEEMPLOYEE

The subprogram VALIDATEEMPLOYEE creates functions for looking up employees based on their employee ID (EMPLID), name, location (MAIL_DROP), and department ID (DEPTID).

This subprogram creates the following javascript functions:

• validateEmployeeID
• validateEmployeeName
• validateMailDrop
• validateDeptID

All of these functions accept the following parameters:

• EMPLID
• NAME
• MAIL_DROP
• DEPTID

All parameters are optional (if that makes sense). You should pass a "null" value for parameters that don't interest you; for example, if all you are interested in is looking up employee ID and employee name, MAIL_DROP and DEPTID can be omitted or explicitly set to null.

The purpose here is to allow your users to specify an employee ID or name. Usually, it is the employee ID that you are interested in because you can map an employee ID to a name, location, or department ID. When searching for a name, location, or Department ID that is not unique, your users will be prompted with a list of employees (their ID and Name) that match to this value. From this list they can select the employee ID. What is updated on your form screen depends on the cells you passed that are not null.

Syntax

Procedure VALIDATEEMPLOYEE (@PROCID INT = NULL);

Example

The following example illustrates how to call VALIDATEEMPLOYEE:

exec http.javascript.script 'true'
exec http.javascript.openWindow
exec http.javascript.validateEmployee
exec http.javascript.script 'false'
select '<form>'
select '<input type=text name=employeeID   onChange="validateEmployeeID  (this.form.employeeID,this.form.employeeName);">'
select '<input type=text name=employeeName onChange="validateEmployeeName(this.form.employeeID,this.form.employeeName);">'
select '</form>'

Example

The problem with the above example is that it takes longer to load this function than it does to actually validate an employee. It might be simpler to just ask someone their employee number. To use this function correctly, it should be loaded once and once only, and you should expect it to be used to validate several employees. Frames make this possible. The following example illustrates the correct way to invoke this subprogram. Note that we qualify the reference to validateEmployeeID() and validateEmployeeName() so that they refer to the parent window.

create procedure test(@BUTTON varchar(30) = null)
as
begin
    if @BUTTON is null
    begin
        exec http.javascript.script 'true'
        exec http.javascript.openWindow
        exec http.javascript.validateEmployee
        exec http.javascript.script 'false'
        exec sp_html_frame @PROCNAME='test',@CENTER='*'
        return
    end
    if @BUTTON = 'CENTER'
    begin
        select '<form>'
        select '<input type=text name=employeeID   onChange="parent.validateEmployeeID  (this.form.employeeID,this.form.employeeName);">'
        select '<input type=text name=employeeName onChange="parent.validateEmployeeName(this.form.employeeID,this.form.employeeName);">'
        select '</form>'
        return
    end
end

Example

If you pass your @@procid to this procedure (different from the default), you can validate employees on the server side instead of loading all employees to the client. The interface is almost the same, but it does require that you call sp_html_frame to intialize these functions. The effect is the same except that it takes longer to validate this on the server. If you are attempting to validate only one employee, however, this is the preferred usage.

create procedure test(@BUTTON varchar(30) = null)
as
declare
    @procid int
begin
    if @BUTTON is null
    begin
        select @procid = @@procid
        exec http.javascript.script 'true'
        exec http.javascript.openWindow
        exec http.javascript.validateEmployee @procid
        exec http.javascript.script 'false'
        exec sp_html_frame @PROCNAME='test',@CENTER='*'
        return
    end
    if @BUTTON = 'CENTER'
    begin
        select '<form>'
        select '<input type=text name=employeeID   onChange="parent.validateEmployeeID  (this.form.employeeID,this.form.employeeName,this.form.maildrop,this.form.deptid);">'
        select '<input type=text name=employeeName onChange="parent.validateEmployeeName(this.form.employeeID,this.form.employeeName,this.form.maildrop,this.form.deptid);">'
        select '<input type=text name=maildrop     onChange="parent.validateMailDrop    (this.form.employeeID,this.form.employeeName,this.form.maildrop,this.form.deptid);">'
        select '<input type=text name=deptid       onChange="parent.validateDeptID      (this.form.employeeID,this.form.employeeName,this.form.maildrop,this.form.deptid);">'
        select '</form>'
        return
    end
end

Example

The following example illustrates how you can invoke these functions if there are multiple occurences of the same field:

declare
    i@ int
begin
    select '<form>'
    select @i = 0
    while (@i < 15)
    begin
        select '<input type=text name=employeeID   onChange="parent.validateEmployeeID  (this.form.employeeID['||convert(varchar,@i)||'],this.form.employeeName['||convert(varchar,@i)||']);">'
        select '<input type=text name=employeeName onChange="parent.validateEmployeeName(this.form.employeeID['||convert(varchar,@i)||'],this.form.employeeName['||convert(varchar,@i)||']);">'
        select @i = @i + 1
    end
    select '</form>'
end

Subprogram VALIDATELANGUAGE

The subprogram VALIDATELANGUAGE creates functions for looking up languages and language codes.

This subprogram creates the following javascript functions:

• validateLanguageCode
• validateLanguageName

Both functions accept the following parameters:

• CODE
• NAME

All parameters are optional (if that makes sense). Pass a null value for parameters that don't interest you; for example, if all you are interested in is looking up a language, pass a "null" value for CODE.

Example

The following example illustrates how to call VALIDATELANGUAGE:

exec http.javascript.script 'true'
exec http.javascript.go
exec http.javascript.openWindow
exec http.javascript.validateLanguage
exec http.javascript.script 'false'
select '<form name=form>'
select '<input type=text name=code onChange="validateLanguageCode(this,this.form.name);">'
select '<input type=text name=name onChange="validateLanguageName(this.form.code,this);">'
select '</form>'

Subprogram VALIDATENUMBER (PICTURE)

The subprogram VALIDATENUMBER creates the function validateNumber() which accepts the following parameters:

• value
• picture

where value is the cell on your form that is to be validated. If valid, your cell is formatted with the format (PICTURE) that is passed to this subprogram or the format (picture) that is passed to this function. You can use the former if all numeric fields should have the same format. Use the latter if the picuture clause is cell specific.

Syntax

Procedure VALIDATENUMBER (@PICTURE varchar(30) = 'fm99999999999999999999');

Example

The following example illustrates how to call VALIDATENUMBER:

declare
    @picture varchar(30)
begin
    select @picture = 'fm999'
    exec http.javascript.script 'true'
    exec http.javascript.isdigit
    exec http.javascript.ltrim
    exec http.javascript.rtrim
    exec http.javascript.trim
    exec http.javascript.substr
    exec http.javascript.to_char
    exec http.javascript.lpad
    exec http.javascript.rpad
    exec http.javascript.substr
    exec http.javascript.validateNumber @picture
    exec http.javascript.script 'false'
    select '<form>'
    select '<input type="text" onChange="validateNumber(this);">'
    select '<input type="text" onChange="validateNumber(this,''999,999,999.00'');">'
    select '<input type="text" onChange="validateNumber(this,'''|| @PICTURE || ''');">'
    select '</form>'
end

Subprogram VALIDATESTATE

The subprogram VALIDATESTATE creates functions for looking up state names and state codes. If you're wondering why you wouldn't just create a drop-down list using a <select> tag, by all means that is what you should use and I encourage you to do so.

This subprogram creates the following javascript functions:

• validateStateCode
• validateStateName

Both functions accept the following parameters:

• CODE
• NAME

All parameters are optional (if that makes sense). Pass a null value for parameters that don't interest you; for example, if all you are interested in is looking up a state name, pass a "null" value for CODE.

Example

The following example illustrates how to call VALIDATESTATE:

exec http.javascript.script 'true'
exec http.javascript.go
exec http.javascript.openWindow
exec http.javascript.validateState
exec http.javascript.script 'false'
select '<form name=form>'
select '<input type=text name=code onChange="validateStateCode(this,this.form.name);">'
select '<input type=text name=name onChange="validateStateName(this.form.code,this);">'
select '</form>'

Subprogram VALIDATETIME (NLS_DATE_FORMAT)

This subprogram creates the function validateTime() which accepts the following parameters:

• cell
• nls_date_format

Syntax

Procedure VALIDATETIME (@NLS_DATE_FORMAT VARCHAR(30) = 'HH:MI:SSAM');

Example

The following example illustrates how to call VALIDATETIME:

exec http.javascript.script 'true'
exec http.javascript.isdigit
exec http.javascript.ltrim
exec http.javascript.rtrim
exec http.javascript.trim
exec http.javascript.isspace
exec http.javascript.to_char
exec http.javascript.validateTime
exec http.javascript.script 'false'
select '<form>'
select '<input type=text name=time onChange="validateTime(this);">'
select '</form>'

Your NLS_DATE_FORMAT should reference hours, minutes and seconds only.

See Also