Re: Dynamic sql real examples

Gabriel Filipiak <gabriel.filipiak@xxxxxxxxx> · Thu, 13 Oct 2011 07:44:24 +0200

2011/10/13 Gavin Flower <GavinFlower@xxxxxxxxxxxxxxxxx>

    On 13/10/11 18:35, Gabriel Filipiak wrote:
    Thx Gavin,

      any other suggestions from others?

      Gabe

      2011/10/13 Gavin Flower <GavinFlower@xxxxxxxxxxxxxxxxx>

               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