Loading ...
Sorry, an error occurred while loading the content.

Select with constant value

Expand Messages
  • Juan Pedro López Sáez
    Hello all. In Firebird it s possible a SELECT statement like this: SELECT TABLE1.FIELD1, TABLE1.FIELD2, K AS CONST FROM TABLE1 WHERE ... The interesting point
    Message 1 of 7 , Mar 1, 2005
    • 0 Attachment
      Hello all.

      In Firebird it's possible a SELECT statement like this:

      SELECT TABLE1.FIELD1, TABLE1.FIELD2, K AS CONST
      FROM TABLE1
      WHERE ...

      The interesting point in this query is the constant value that will
      appear in the resulting output set.

      I've tested this kind of query against my database from isql, the
      command line tool, providing an integer as the constant value.
      Everything works perfectly.

      Now I want to use it from jaybird, by means of a prepared statement,
      because the constant value will change in every new query.

      I tested it just replacing the constant value with the usual ? character
      without success. It seem this parameter is not considered when preparing
      the query so it fails because of some type mismatch in the subsequent
      parameters.

      Isn't this kind of parametrized query currently supported?

      Thank you very much.

      Juan Pedro Lopez
    • Rick Fincher
      Hi All, An update to the thread on using Sun Java Studio Creator and JayBird/Firebird: Right now, Creator is only aware of IBM s DB2 database not supporting
      Message 2 of 7 , Mar 1, 2005
      • 0 Attachment
        Hi All,

        An update to the thread on using Sun Java Studio Creator and
        JayBird/Firebird:

        Right now, Creator is only aware of IBM's DB2 database not supporting
        the syntax described below.

        Creator tests for DB2, but no other Database.

        The Creator development team says they are going to fix the problem by
        testing for databases that support the syntax and assume anything not in
        that list doesn't support it.

        That way everything should work. Worst case being a database that
        supports the syntax isn't in the list and will run slower.

        They can't use a setting at development time to indicate this because
        they can't assume the same database will be used for deployment.

        The problem was due to the fact that Creator assumes databases have the
        ability to use PreparedStatements with 'Where ? IS NULL' syntax. This
        is not in the SQL spec but Oracle, MySQL, Postgres, and PointBase
        support this.

        The SQL 2003 spec supports an alternate syntax that will do essentially
        the same thing, but this will not be available til Firebird 2.0. Since
        the syntax is non-spec the Firebird team considers it a non-problem,
        even though it can create incompatibilities with all the other
        databases, and can create performance problems for Firebird vs the others.

        By not having this syntax Creator is forced to create a new statement
        for each row that is updated. That is slower than a PreparedStatement.
        I guess on a large system that can add up to a lot of overhead.

        Rick
      • Roman Rokytskyy
        ... In fact you should get org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544569. Dynamic SQL Error SQL error code = -804 Data type unknown This means
        Message 3 of 7 , Mar 3, 2005
        • 0 Attachment
          > Now I want to use it from jaybird, by means of a prepared statement,
          > because the constant value will change in every new query.
          >
          > I tested it just replacing the constant value with the usual ? character
          > without success. It seem this parameter is not considered when preparing
          > the query so it fails because of some type mismatch in the subsequent
          > parameters.

          In fact you should get

          org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544569. Dynamic SQL
          Error
          SQL error code = -804
          Data type unknown

          This means that server does not know the type of the parameter to prepare
          it. This is the same issue as in SELECT * FROM myTable WHERE ? IS NULL OR
          myPk = ? that was discussed some time ago.

          > Isn't this kind of parametrized query currently supported?

          No, unfortunately this syntax is not supported. But if your query does not
          change itself, you can create a selectable procedure that will do the same
          thing.

          For example, if you had

          SELECT mainCol, ? AS selectParam FROM myTable WHERE someOtherCol = ?

          you should create following procedure

          CREATE PROCEDURE workaroundProc(
          firstParam INTEGER,
          secondParam VARCHAR
          ) RETURNS (
          mainCol TIMESTAMP,
          selectParam INTEGER
          ) AS BEGIN
          FOR SELECT myCol, :firstParam FROM myTable
          WHERE someOtherCol = :secondParam
          INTO :mainCol, :selectParam
          DO
          SUSPEND;
          END

          Then you can use

          SELECT mainCol, selectParam FROM workaroundProc(?, ?)

          This will give you the same result with virtually no performance loss.

          Roman
        • Juan Pedro López Sáez
          Thank you for your answer. Now, I m preparing this sort of query directly appending the constant value to the sql string. So it looks like: int const; String
          Message 4 of 7 , Mar 4, 2005
          • 0 Attachment
            Thank you for your answer.

            Now, I'm preparing this sort of query directly appending the constant
            value to the sql string. So it looks like:

            int const;

            String str = "SELECT FIELD1, " + const + " AS CONST, FIELD2 ";
            str += "FROM TABLE1 ";
            str += "WHERE FIELD1 = ?";

            pst = conn.prepareStatement(str);
            pst.setInt(1, val);
            pst.execute();

            Am I loosing the prepared statement's befenefits in this situation? The
            const value will change in every new query.

            The same question arises related to the "SELECT FIRST x SKIP y" clause.
            In my application I use the same way, appending to the query string the
            desired constant values, to make that kind of queries.

            Thank you very much.

            Juan Pedro

            > > Now I want to use it from jaybird, by means of a prepared statement,
            > > because the constant value will change in every new query.
            > >
            > > I tested it just replacing the constant value with the usual ? character
            > > without success. It seem this parameter is not considered when preparing
            > > the query so it fails because of some type mismatch in the subsequent
            > > parameters.
            >
            > In fact you should get
            >
            > org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544569. Dynamic SQL
            > Error
            > SQL error code = -804
            > Data type unknown
            >
            > This means that server does not know the type of the parameter to prepare
            > it. This is the same issue as in SELECT * FROM myTable WHERE ? IS NULL OR
            > myPk = ? that was discussed some time ago.
            >
            > > Isn't this kind of parametrized query currently supported?
            >
            > No, unfortunately this syntax is not supported. But if your query does not
            > change itself, you can create a selectable procedure that will do the same
            > thing.
            >
            > For example, if you had
            >
            > SELECT mainCol, ? AS selectParam FROM myTable WHERE someOtherCol = ?
            >
            > you should create following procedure
            >
            > CREATE PROCEDURE workaroundProc(
            > firstParam INTEGER,
            > secondParam VARCHAR
            > ) RETURNS (
            > mainCol TIMESTAMP,
            > selectParam INTEGER
            > ) AS BEGIN
            > FOR SELECT myCol, :firstParam FROM myTable
            > WHERE someOtherCol = :secondParam
            > INTO :mainCol, :selectParam
            > DO
            > SUSPEND;
            > END
            >
            > Then you can use
            >
            > SELECT mainCol, selectParam FROM workaroundProc(?, ?)
            >
            > This will give you the same result with virtually no performance loss.
            >
            > Roman
            >
            >
            >
            >
            > Yahoo! Groups Links
            >
            >
            >
            >
            >
            >
            >
          • Roman Rokytskyy
            ... You re loosing it by calling conn.prepareStatement(String). In general each call to Connection.prepareStatement(String) tells server to compile new
            Message 5 of 7 , Mar 4, 2005
            • 0 Attachment
              > Now, I'm preparing this sort of query directly appending the constant
              > value to the sql string. So it looks like:
              >
              > int const;
              >
              > String str = "SELECT FIELD1, " + const + " AS CONST, FIELD2 ";
              > str += "FROM TABLE1 ";
              > str += "WHERE FIELD1 = ?";
              >
              > pst = conn.prepareStatement(str);
              > pst.setInt(1, val);
              > pst.execute();
              >
              > Am I loosing the prepared statement's befenefits in this situation? The
              > const value will change in every new query.

              You're loosing it by calling conn.prepareStatement(String). In general each
              call to Connection.prepareStatement(String) tells server to compile new
              statement. However, some connection pools (including JayBird
              FBWrappingDataSource) still cache prepared statements by comparing the SQL
              being prepared. By appending some changing value you loose this feature too.

              > The same question arises related to the "SELECT FIRST x SKIP y" clause.
              > In my application I use the same way, appending to the query string the
              > desired constant values, to make that kind of queries.

              This should work without any problems, i.e. SELECT FIRST ? SKIP ? * FROM
              myTable ORDER BY myCol.

              Roman
            • tsangccn
              ... Can you give me the link where the discussion was? I have the same problem as the SQL shown but I cannot found the discussion on that issue. Thanks C. N.
              Message 6 of 7 , Mar 13, 2005
              • 0 Attachment
                --- In Firebird-Java@yahoogroups.com, "Roman Rokytskyy"
                <rrokytskyy@a...> wrote:

                > This is the same issue as in SELECT * FROM myTable WHERE ? IS NULL OR
                > myPk = ? that was discussed some time ago.
                >
                Can you give me the link where the discussion was? I have the same
                problem as the SQL shown but I cannot found the discussion on that issue.

                Thanks
                C. N.
              • Roman Rokytskyy
                ... See http://groups.yahoo.com/group/Firebird-Java/message/7197 thread and http://groups.yahoo.com/group/Firebird-Java/message/7204 message especially. But I
                Message 7 of 7 , Mar 14, 2005
                • 0 Attachment
                  >> This is the same issue as in SELECT * FROM myTable WHERE ? IS NULL OR
                  >> myPk = ? that was discussed some time ago.
                  >>
                  > Can you give me the link where the discussion was? I have the same
                  > problem as the SQL shown but I cannot found the discussion on that issue.

                  See http://groups.yahoo.com/group/Firebird-Java/message/7197 thread and
                  http://groups.yahoo.com/group/Firebird-Java/message/7204 message
                  especially.

                  But I do not think that will help you a lot - there is no fix or
                  workaround for this problem.

                  Roman
                Your message has been successfully submitted and would be delivered to recipients shortly.