Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2003
    Posts
    51

    Unanswered: Parameter Markers

    The following stmt gives an error.

    Select fieldname1, fieldname2, fieldname3 from table where
    fieldname1 = ?

    The error was,

    The number of host variables in the EXECUTE or OPEN statement is not
    equal to the number of values required. SQLSTATE=07001

    SQL0313N The number of host variables in the EXECUTE or OPEN statement is not equal to the number of values required.

    Explanation:

    The number of host variables specified in the EXECUTE or OPEN
    statement does not equal the number of host variables required
    for the parameter markers (?) appearing in the SQL statement.

    User Response:

    Correct the application program so the number of host variables
    specified in the EXECUTE or OPEN statement is correct for the
    parameter markers in the SQL statement.

    sqlcode : -313

    sqlstate : 07001, 07004

    Can anybody point what's the error in the stmt.
    Thanks.
    sampath gowri

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Please supply the DB2 version, fixpack level, and OS.

    How are you trying to execute this. There needs to be more code involved. Just executing a select statement with a ? is not valid.

    Please supply us with all the pertinent information.

    Andy

  3. #3
    Join Date
    Oct 2003
    Posts
    51
    I am very new to DB2.
    I used Command Editor to type the select stmt and clicked the Execute button to execute this stmt. I currently use Db2 v8. I donno the fixpack level.
    This is how I used command editor.
    Start->Programs->IBM DB2->Command Line tools->Command Editor.
    sampath gowri

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You can generate an access plan on that query from the Command Editor, but you cannot execute the select statement that way. You need to supply a value for the ?.

    Andy

  5. #5
    Join Date
    Oct 2003
    Posts
    51
    How to generate an access plan on that query. Can you write in brief. Also, the sql stmt has to be supplied with a different value every time it is executed. I cannot give a fixed value .
    sampath gowri

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    To generate an access plan, you need to create the explain tables first. Once those are created, in the Command Editor you should see an icon in the tool bar that looks like four dots forming an upside down Y, press that. Or you can do Selected -> Access Plan from the menu.

    The Command Editor is for two purposes. Generating Access plans, and executing statements. In order to execute a query to get a result set back, it HAS to be a valid query.

    Andy

  7. #7
    Join Date
    Oct 2003
    Posts
    51
    Is it not possible to execute a select stmt without creating access plan. My stmt is very simple.
    select pat_no, pat_name from patient where pat_no=?
    Pat_no is an integer.
    Should this be given in a different format in DB2.
    Thanks.
    sampath gowri

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Yes, you can execute statements in DB2 with parameter markers, just not in the Command Editor. Is is easily done in applications. You would use the Command Editor to check the access plan only for such statements, not to try to execute them.

    Andy

  9. #9
    Join Date
    Oct 2003
    Posts
    51
    Then, where should I type the Sql Stmt.? Where will I execute it. I have all these in my system.
    Start->Programs->IBMDB2->Command Line Tools
    ->Development Tools
    ->Genereal Admin tools
    ->Information
    ->Monitoring Tools
    ->Set Up Tools.
    sampath gowri

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I do not think you understand what a parameter marker is. It is a place holder that will get filled in later. This is easily accomplished in an application written in some language such as java. The procedure for this is something like this:

    PreparedStatement pstmt = conn.prepareStatement("select * from table_a where id = ?";
    pstmt.setInt(1,currentID);
    rs = pstmt.executeQuery();

    How do you plan to do this using the Command Editor? It can only accept known values. It can only execute sql and db2 statements.

    What is it that you want to do? Please be specific.

    Andy

  11. #11
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    No pun intended, but my suggestion would be to grab a good book on SQL or to take a basic database course. I believe that some fundamental concepts are not fully understood yet by the OP.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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