Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2002
    Posts
    3

    Question Unanswered: DB2 Stored proc w/ parms errors (from VB, ADO, DB2 Connect)

    I am using DB2 v. 7 and DB2 Connect (fp5) from Visual Basic (through ADO). The issue is that I only able to call stored procedures from
    Visual Basic when it has no parameters.

    I have created two very simple stored procedures that do exactly the same thing. One has 0 parameters and the other takes 1 parameter (I've tested with char(5), decimal(9,0), or integer).

    I have been able to call both of these stored procedures through the IBM DB2 Connect Command Line Processor utility and get the correct results in that environment.

    I have tried testing the ADO Connection.Execute method with the stored procedures described above. Using the following statement I get the desired results (with the parameter-less stored proc),
    Call adconDBCon.Execute("call TESTPROC(); ")

    With the statement,
    Call adconDBCon.Execute("call TESTPARM2('296.2'); ")

    I get the following error:
    [IBM][CLI Driver][DB2] SQL0440N No function by the
    name "TSTPARM2" having compatible arguments was found in the
    function path. SQLSTATE=42884 (-214746259)

    When I fully qualify the name I get this same error. I have also tried different types of parameters but the error perists. In the case above (with the char(5) parameter) the stored procedure is created as:
    CREATE PROCEDURE RGOOD.TSTPARM2 ( IN varDiagCod char(5) )

    If I pass the SQL statements (i.e. "SELECT ... " ) through the
    connection's Execute method I also get the desired results.

    Is there some setting that explains why the two procedures behave
    differently (especially since the Command Line Processor utility functions
    properly).

    Thanks,
    Rich
    p.s. I have also been able to call them using the ADO Command object.
    However, I would rather use the Execute method to allow for batches
    of queries.

  2. #2
    Join Date
    Aug 2002
    Posts
    12
    I don't have the exact answer for you,
    But
    - it seems to me that this message is for Functions, not for Stored Procedures. why ?

    -DB2 strictly checks the number and dataype of SP parms.
    if there's a mismatch between client passed parms and DB2 difinition,
    SP call will be fail.( just like java signiture unmatch pattern)

    Especially, Numeric Datatype check is strict.
    For example, Integer and Smallint are to be distinguished.
    ( Maybe ADO will pass numeric parameter as text string without quotation )

    So I suggest to
    -try 1 parm pattern which is character type.
    -check CREATE PROCEDURE parm definition. ( if there is )
    -get CLI trace .( just edit c:\sqllib\db2cli.ini . it's easy. )

    Thank you.

  3. #3
    Join Date
    Mar 2002
    Posts
    34
    I have run into the exact problem only when using paramters in eScript (javascript).

    As a work around, I have created a bat file within javaScript.

    So,

    I would create a bat file with the input file

    db2cmd.exe /w /f connect to sample user db2inst1 using ibmdb2 /n
    call stored_proc(var1, var2) /n
    disconnect

    NOTE: you need the "/w" this allows the vb code to wait for db2 to return with a result before continuing.

    Hope this helps, if you find a better solution please let me know. I also considered created a custom dll.

  4. #4
    Join Date
    Jul 2002
    Posts
    3
    Thanks for your replies.

    The issue turned out to be that DB2 Connect requires that the SQL call
    passed to the connection's Execute method be enclosed in braces - if
    it contains parameters.

    The original line of VB code that did not work was as follows:
    Call adconDBCon.Execute("call TESTPARM2('296.2'); ")

    It worked once I changed it to read:
    Call adconDBCon.Execute("{ call TESTPARM2('296.2'); }")

    Rich

Posting Permissions

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