Re: Dynamic sql real examples

2011/10/13 Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz>

> On 13/10/11 18:35, Gabriel Filipiak wrote:
>
> 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
>>
>>
>> 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