Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2005
    Posts
    3

    Unanswered: What is the difference between static vs dynamic in SQLJ

    I dont know the difference, could anyone explain. In my application I will have pagination functionality. Does that mean it's dynamic?

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    If it blinks, makes loud sounds, and jumps around the screen, then it's definitely dynamic. If it sits in the corner, dark and cold, it's probably static.

    Seriously though, with respect to SQL "dynamic" means that the statement is only sent to the DBMS at the program execution time; it is only compiled (prepared) and executed when the program runs.

    Static SQL statements, on the other hand, are prepared at the program compile time and then saved (bound) to the database. When the program runs it requests execution of the prepared and bound SQL snippets (packages).

  3. #3
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    SQLJ is inherently static. If you are using SQLJ embedding it's static.

    (Although I've now found out through another thread that if you don't properly bind the package, it will be executed dynamically, even though that was never the intention of SQLJ... another topic )

    So if your app looks like this:

    #sql cur = {SELECT * FROM staff WHERE id >= :myid};

    It's static.

    JDBC is dynamic. So if you find yourself coding your app like this:

    PreparedStatement pstmt = con.prepareStatement("SELECT * FROM STAFF WHERE ID >= ?");
    pstmt.setInteger(myid);

    ...
    etc.

    It's dynamic.

    Pagination does not neccessarily imply you need to use dynamic SQL, it can be done statically depending on how you decide to implement it. For instance if you're paginating on a range of ids or something, you might do a static statement like:

    #sql cur = {SELECT * FROM staff WHERE id >= :myid and id < :myotherid};

    Again, depends on exactly what you're trying to accomplish.

    jono
    --
    Jonathan Petruk
    DB2 Database Consultant

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    I guess you can have dynamic stmts even if you embed SQL Statements in SQLJ.

    eg.
    #sql ins1={insert into :t1 values(:col1,:col2)}

    When you bind, this statement can be held in a package but is dynamic SQL ..

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Oct 2005
    Posts
    109
    sathyaram,

    how about authentication then?
    Knowing it exists I still I try to avoid this, since we have either authorities on packages or on tables.
    This way we get a mixture of both: to do one thing you need to be granted to the package and to the table, right ? So I think it is valid under certain circumstances but can give a headache for administration concepts.
    Juliane

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    The authentication depends on the DYNAMICRULES option you used when BINDing the package ...

    If you use DYNAMICRULES RUN (the default), the user executing the package will be required to have necessary authorities on the objects referred in the statement .. If DYNAMICRULES BIND is used, it is only the package owner needs authority over the objects (quiet similar to static SQL)

    http://publib.boulder.ibm.com/infoce...72%75%6c%22%20

    http://publib.boulder.ibm.com/infoce...d/c0005234.htm

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Oct 2005
    Posts
    109
    thanks for the clarification
    Juliane

  8. #8
    Join Date
    Dec 2005
    Posts
    3

    Dynamic SQL

    From your explanation a pagination feature is dynamic SQL. Is that right? For pagination functionality is SQLJ appropriate or should I look at JDBC or other. Thanks for your response.

    Quote Originally Posted by n_i
    If it blinks, makes loud sounds, and jumps around the screen, then it's definitely dynamic. If it sits in the corner, dark and cold, it's probably static.

    Seriously though, with respect to SQL "dynamic" means that the statement is only sent to the DBMS at the program execution time; it is only compiled (prepared) and executed when the program runs.

    Static SQL statements, on the other hand, are prepared at the program compile time and then saved (bound) to the database. When the program runs it requests execution of the prepared and bound SQL snippets (packages).

  9. #9
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Can you share how you do pagination ? This will determine if it is static or dynamic ..

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  10. #10
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by sathyaram_s
    I guess you can have dynamic stmts even if you embed SQL Statements in SQLJ.

    eg.
    #sql ins1={insert into :t1 values(:col1,:col2)}

    When you bind, this statement can be held in a package but is dynamic SQL ..

    Cheers
    Sathyaram
    Are you sure you can do that...? I haven't been able to find a sample that allows that, or anything in the docs.
    --
    Jonathan Petruk
    DB2 Database Consultant

  11. #11
    Join Date
    Dec 2005
    Posts
    3

    Pagination

    Do u have any suggestion or reconmendation on how to implement pagination with SQLJ. I need to know in SQLJ if the following methods are available:

    setFirstResult(startRecord); << does not seem to be available
    setMaxResults(endRecord); << seems to be available in SQLJ


    I usually do pagination with Hibernate as show below:

    private List getAllFeeds(final String queryName, final int startRecord, final int endRecord) {
    return getHibernateTemplate().executeFind(new HibernateCallback(){
    public Object doInHibernate(Session session) throws HibernateException, SQLException {
    Query query = session.getNamedQuery(queryName);
    query.setFirstResult(startRecord);
    query.setMaxResults(endRecord);
    List list = query.list();
    return list;
    }
    });
    }



    Quote Originally Posted by sathyaram_s
    Can you share how you do pagination ? This will determine if it is static or dynamic ..

    Cheers
    Sathyaram

Posting Permissions

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