Re: Dynamic sql real examples

Gavin Flower <GavinFlower@xxxxxxxxxxxxxxxxx> · Thu, 13 Oct 2011 18:31:22 +1300



    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
    

    .