Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi,
Can anyone please help me to solve this problem?

In a script, I am using a hash variable to retrieve the values from the webpage(using CGI scripting). These values are passed to SYBASE stored procedure to insert into a table.
The problem is when we do not enter values to some of the fields in webpage, the values for some of the keys of hash are undefined; and when i am trying to use this hash(where some of the key values are undefined), I am getting error msg as below
"DBD::Sybase::st execute failed: Server message number=170 severity=15 state=1 line=11 server=SALWEB02 text=Line 4: Incorrect syntax near ','.

For example. 

 
my %FORM; 
$FORM{Number} = '2'; 
$FORM{Name} = "test"; 
$FORM{ReqName}; 
$FORM{Email}= 'test@mail'; 
$FORM{Part} = '12'; 

and 
$sql = qq!exec ex_proc \@part= $FORMPartNumber}, 
                       \@Num = $FORM{Number}, 
                       \@Name = $FORM{Name}, 
                       \@ReqName= $FORM{ReqName},  
                       \@Email= $FORM{Email}!; 
$ins_sth = $dbh->prepare($sql); 
$ins_sth->execute();

[download]
Here as Reqname is not defined, the error message has text:line 4(as Reqname is used in line 4 in the sql statement).
and my procedure is as 
CREATE PROCEDURE ex_proc (@part nvarchar(128), 
           @Num nvarchar(128), @Name nvarchar(256), 
           @ReqName nvarchar(256), @Email nvarchar(128)) 
AS INSERT INTO sample (part, Num,Name, ReqName, Email) VALUES ( @part,
+ @Num, @Name, @ReqName, @Email) 
GO

[download]

Can anyone help me why this error is occuring and how to resolve it?
Thanks in advance.
  • Comment on Pass the values to the sybase stored procedure using DBI-Sybase module and exexute the procedure.
  • Select or Download Code

Replies are listed 'Best First'.
Re: Pass the values to the sybase stored procedure using DBI-Sybase module and exexute the procedure.
by Corion (Patriarch) on Jun 27, 2006 at 12:42 UTC

    Have you tried printing out your SQL statement before trying to execute it? Also think about how you will handle a Name with whitespace in it.

    You should read up on DBI placeholders and what they are commonly used for.

[reply]
[d/l]
      Corion,
      Thank yoy very much for your suggestion!
      I have to pass nearly 20-25 fields to the stored procedure. I can check whether a value is defined for a key or not. But I don't know how to pass a null value if the value is undefined by using the same sql statement.

      Placeholders are not accepted in my code. If I am using placeholders, error mesages like "DBD::Sybase::st execute failed: ct_param() failed!" are displayed.
      Thanks
[reply]

        So, if you need to create the SQL statement including the values as a string, have you looked at the quote method of DBI? I find it very helpfull to first try out my SQL in the SQL shell (isql for Sybase I think) and when I'm satisfied with the SQL statement to reproduce that statement in my Perl code. Have you done that already?

[reply]
[d/l]
[select]
        Does DBD::Sybase accept the NULL alias for null values?
[reply]
Re: Pass the values to the sybase stored procedure using DBI-Sybase module and exexute the procedure.
by Moron (Curate) on Jun 27, 2006 at 13:43 UTC
    T-SQL is rejecting the '=,' caused by the empty data. But even if that were filled, you would get another error for the nvarchars because you are entering the data unquoted into the procedure and it would complain with words to the effect: "no such column". This can be resolved by placing quotes around the nvarchar RHS's although not for numeric data. It is unclear what you intend when the data is empty -- should it be accepted or rejected as null? Should the procedure set it to null? Should the referential integrity checks pick it up later? Should the Perl script reject it already? etc. etc.

    Update:There is also the related question of whether empty numeric data should become initialised to 0 or whether some other null treatment such as one of the possibilities above should be applied.

    -M

    Free your mind

[reply]
      Thanks a lot for your suggestion Moron.
      I have modified my code as suggested by you. i.e using quotes for empty data.

      But, actually I have to pass NULL values if the data passed to column is empty. But by using quotes, the value is stored as empty string in the database instead of NULLS.

      Is there anyway to handle this in Perl script itself so that NULL values are passed to the SYBASE stored procedure when the data that is passed to the column is nothing(NULL or no data).

      Thanks a lot to all the Perl Monks for your effort and time!!!

[reply]
        What I meant to suggest was:

        - numeric data not have quotes in the RHS

        - string data should have quotes in RHS constant assignments

        - but empty data needs a null treatment - such as the NULL keyword.

        I avoided being specific because normally one needs to know more about the database design before suggesting what to do about nulls - it is common for some empty fields to be unnacceptable and whereas this can be controlled by a database constraint, it is now very popular to precontrol it at the data entry point rather than endure the overhead of submitting to the database.

        -M

        Free your mind

[reply]

Back to Seekers of Perl Wisdom