Results 1 to 3 of 3
  1. #1
    Join Date
    May 2002
    Posts
    28

    Unanswered: Validate the value passed to my SP

    Hi,
    I am new to sybase & want to clarify my doubt...
    A Perl program will pass some parameters to my Stored procedure in sybase...
    Those parameters are basically values to the columns of a particular table...
    I would like to validate the values that has been passed to my procedure...

    Like for eg:
    if i get an integer, i would have to check whether that column accepts integer(may be from system tables like sysobjects,syscolumns,systypes... & others) & report accordingly.
    if i get a char, i would have to check whether that column accepts integer or char & its length validation etc....
    There can be quiet a few checks for this kind...

    CAN ANY ONE HELP ME ON THIS...OR PROVIDE ME A LINK WHERE I CAN BE BENEFITTED...
    Thanks in advance...

  2. #2
    Join Date
    Jan 2003
    Posts
    26
    I think I did something similar to what you did, but instead of checking the values in the stored proc I use Perl.

    Here's my stored proc that gets info on the fields in a table:
    create procedure sp_col_info (
    @table_name varchar(100)
    )
    as
    begin
    select b.colid, b.name, c.name, b.length
    from sysobjects a, syscolumns b, systypes c
    where a.name = @table_name
    and a.id = b.id
    and b.type = c.type
    and c.name not in ('sysname', 'nid', 'uid', 'nvarchar', 'tid', 'nchar')
    end

    Then I load these results into some hashes and do some conversions along the way on the types:
    $sql = "sp_col_info subscriber_load";
    $sth = $dbh->prepare($sql) || die "Could not prepare $sql";
    $sth->execute() || die "Could not run $sql";
    while (($colid, $colname, $type, $length) = $sth->fetchrow_array()) {
    if ($type eq "intn") { $type = "int"; }
    if ($type eq "datetimn") {$type = "datetime"; }
    if ($type eq "smalldatetime") {$type = "datetime"; }
    if ($type eq "numericn") {$type = "numeric"; }
    $coltype{$colname} = $type;
    $colid{$colname} = $colid;
    $collength{$colname} = $length;
    }

    Now anytime I get a value to load into a field, I can easily get info on that field.

    I'm sure this could use some tweaking but it should help.

    Frank

  3. #3
    Join Date
    May 2002
    Posts
    28

    Thanks frank...

    Thanks for the extended help...

    Regards
    Deb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •