Re: Dynamic sql real examples

Thx Gavin,

any other suggestions from others?

Gabe

2011/10/13 Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)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
>
>
>
>
>
> .
>