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

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).

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

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

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.

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

thanks for the clarification

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.

Can you share how you do pagination ? This will determine if it is static or dynamic ..

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.

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;
}
});
}