2011/10/13 Gavin Flower <gavinflo...@archidevsys.co.nz> > On 13/10/11 18:35, Gabriel Filipiak wrote: > > 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 >> >> >> Hi Gabe, > > Please do not 'top post'. In these mailings lists, you are expected to add > your comments either interpersed, or (more normally) at the bottom. This > allows people to read the context, before they read your comments. > > > Cheers, > Gavin >
Sorry about that. Gabe
