Thx Gavin, any other suggestions from others?
Gabe 2011/10/13 Gavin Flower <gavinflo...@archidevsys.co.nz> > On 13/10/11 17:55, Gabriel Filipiak wrote: > > Hi all, > > I have lately learned what is dynamic sql and one of the most interesting > features of it to me is that we can use dynamic columns names and tables. > But I cannot think about useful real life examples. The only one that came > into my mind is statistical table. > > Let`s say that we have table with name, type and created_data. Then we want > to have a table that in columns are years from created_data column and in > row type and number of names created in years. (sorry for my English) > > What can be other useful real life examples of using dynamic sql with > column and table as parameters? How do you use it? > > > I am particulary interested in examples that do not contain administrative > things or database convertion or something like that, I am looking for > examples where the code in example java is more complicated than using a > dynamic sql in for example stored procedure. > > Thanks for any suggestions and help :) regards Gabe > > Hi Gabe, > > I have experience both in the design and implementation of Java systems, as > well as in creating and querying databases (from Java and directly using > SQL). > > A complex query will be executed by PostgreSQL far more efficiently than a > series of simpler queries – even if both are initiated via JDBC. > > An example where dynamic SQL would useful would be in SQL generated to > support a search function with multiple fields, some of which are optional. > From memory, when I did this in Java, the Java application constructed the > query and passed it via JDBC to the database. > > In another situation, I constructed stored procedures in Sybase TransactSQL > with dynamically executed SQL to support a report generation program where > some of the search fields where optional. PostgreSQL is easier to work with, > but it was an existing database. Also using Java was not practicable. > > Be very careful to avoid SQL injection attacks. Consider using the > functions: > > quote_ident(string text) > > and > > quote_literal(string text) > > (see the section '9.4. String Functions and Operators' of the PostgreSQL > 9.1.1 manual) > > > Cheers, > Gavin > > > > > > . >
