Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2002
    Posts
    5

    Unanswered: problem in creating function in db2

    Hi Sir,

    I am trying to create following function with two way

    I have created follwing type

    create TYPE TestCursor
    ( NOTES VARCHAR(500), NOTES1 VARCHAR(100), NOTE2 VARCHAR(255), NOTE3 VARCHAR(257), ID VARCHAR(10)
    )

    First try was

    create function RefCursFunc () RETURNS REF(TestCursor) LANGUAGE SQL CONTAINS SQL NO EXTERNAL ACTION
    BEGIN ATOMIC
    declare testcur cursor with hold for SELECT NOTES, NOTES1, NOTE2, NOTE3, ID FROM TEST;
    open testcur;
    return testcur;
    END @


    I got error

    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "CURSOR WITH HOLD FOR SELECT NOTES" was found
    following " DECLARE testcur". Expected tokens may include: "<space>".
    LINE NUMBER=3. SQLSTATE=42601



    Second try was

    create function RefCursFunc () RETURNS REF(TESTCURSOR) LANGUAGE SQL CONTAINS SQL NO EXTERNAL ACTION
    BEGIN ATOMIC
    return open testcur CURSOR WITH HOLD FOR SELECT NOTES, NOTES1, NOTE2, NOTE3, ID FROM TEST;
    END @


    I got error

    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "open testcur CURSOR WITH HOLD FOR" was found
    following "N ATOMIC return". Expected tokens may include: "<space>".
    LINE NUMBER=3. SQLSTATE=42601


    Thanks,
    -Paresh

  2. #2
    Join Date
    Jun 2002
    Posts
    3

    Post How to compile a SQL function or procedure

    I wrestled with this one today.

    The problem is that you have to have a C compiler set up, and your environment properly configured in order to compile a stored procedure or function on db2. I only know how to do this for Visual Studio 6 under win2k. Here goes:

    1.) do a search on your machine for vcvars32.bat You will use the path
    in a later step.

    2.) build a batch file with the following commands, replacing the following paths with the paths to your vcvars32.bat file.

    @echo on
    rem Setting the SQL PROCEDURE environment:
    db2set DB2_SQLROUTINE_COMPILER_PATH="c:\Program Files\Microsoft Visual Studio\VC98\Bin\vcvars32.bat"
    rem db2set DB2_SQLROUTINE_COMPILER_COMMAND="cl -0d -W2 /TC -D_X86_=1 -I%DB2PATH%\include SQLROUTINE_FILENAME.c /link -dll -defQLROUTINE_FILENAME.def /outQLROUTINE_FILENAME.dll %DB2PATH%\lib\db2api.lib"
    @echo off

    3.) open a command prompt and type db2cmd

    4.) run the batch file above to initialize the environment

    5.) type "db2", and run the create function command.

    -C

Posting Permissions

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