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

Executing an insert procedure

Expand Messages
  • Daniel L. Miller
    Hi all. I m trying to implement a stored procedure to perform updates on Debian Linux packaged FB1.5. Full text of procedure below: CREATE PROCEDURE
    Message 1 of 23 , Nov 1, 2004
    • 0 Attachment
      Hi all.

      I'm trying to implement a stored procedure to perform updates on Debian
      Linux packaged FB1.5. Full text of procedure below:

      CREATE PROCEDURE UPDATE_ZIPCODE (ID INTEGER, ZIPCODE CHARACTER (10),
      STATE CHARACTER (2), CITY VARCHAR (30))
      RETURNS (NEWID INTEGER)
      AS
      BEGIN
      IF (id IS NOT NULL) THEN
      BEGIN
      UPDATE ZIPCODES SET zipcode=:zipcode, state=:state, city=:city WHERE
      id=:id;
      newid=id;
      END
      ELSE
      BEGIN
      newid = GEN_ID(gen_zipcode_id,1);
      INSERT INTO ZIPCODES (id, zipcode, city, state)
      VALUES (:newid, :zipcode, :city, :state);
      END
      END !!

      The idea is to have a single procedure to be used for both inserts &
      updates, and have it return the id of the record created/updated. I'm
      using ibWebAdmin as my interface. The problem comes when I try to test
      the procedure. Executing the following SQL brings these results:

      execute procedure update_zipcode (4,'89102','Las Vegas','NV')
      RETURNING_VALUES NEWID
      >Dynamic SQL Error SQL error code = -104 Token unknown - line 1, char
      63 RETURNING_VALUES

      execute procedure update_zipcode (4,'89102','Las Vegas','NV')
      arithmetic exception, numeric overflow, or string truncation

      I tried this in isql also - same results. What am I doing wrong?

      Daniel
    • Helen Borrie
      ... RETURNING_VALUES belongs to PSQL - it s how you bring back return values in variables when you execute one SP inside another. From a client, just call:
      Message 2 of 23 , Nov 1, 2004
      • 0 Attachment
        At 12:17 AM 1/11/2004 -0800, Daniel Miller wrote:

        >Hi all.
        >
        >I'm trying to implement a stored procedure to perform updates on Debian
        >Linux packaged FB1.5. Full text of procedure below:
        >
        >CREATE PROCEDURE UPDATE_ZIPCODE (ID INTEGER, ZIPCODE CHARACTER (10),
        >STATE CHARACTER (2), CITY VARCHAR (30))
        >RETURNS (NEWID INTEGER)
        >AS
        >BEGIN
        > IF (id IS NOT NULL) THEN
        > BEGIN
        > UPDATE ZIPCODES SET zipcode=:zipcode, state=:state, city=:city WHERE
        > id=:id;
        > newid=id;
        > END
        > ELSE
        > BEGIN
        > newid = GEN_ID(gen_zipcode_id,1);
        > INSERT INTO ZIPCODES (id, zipcode, city, state)
        > VALUES (:newid, :zipcode, :city, :state);
        > END
        >END !!
        >
        >The idea is to have a single procedure to be used for both inserts &
        >updates, and have it return the id of the record created/updated. I'm
        >using ibWebAdmin as my interface. The problem comes when I try to test
        >the procedure. Executing the following SQL brings these results:
        >
        >execute procedure update_zipcode (4,'89102','Las Vegas','NV')
        >RETURNING_VALUES NEWID
        > >Dynamic SQL Error SQL error code = -104 Token unknown - line 1, char
        >63 RETURNING_VALUES
        >
        >execute procedure update_zipcode (4,'89102','Las Vegas','NV')
        >arithmetic exception, numeric overflow, or string truncation
        >
        >I tried this in isql also - same results. What am I doing wrong?

        RETURNING_VALUES belongs to PSQL - it's how you bring back return values in
        variables when you execute one SP inside another. From a client, just call:

        execute procedure update_zipcode (4,'89102','Las Vegas','NV')

        and read the return value(s) in the XSQLDA structure. ISQL does this for
        you: just try it and you should get the result.

        Depends on your data access interface how you read it in your application,
        e.g. in IBO, you read it in the Fields[] array of the TIB_Statement
        object. Jaybird, ODBC, FIBPlus, etc. will each have its own way of
        encapsulating what comes back.

        ./hb
      • Alan McDonald
        ... as helen says - but just a note on logic, you are assuming the presence of id just because it s being passed to the procedure. A better logic would be to
        Message 3 of 23 , Nov 1, 2004
        • 0 Attachment
          > Hi all.
          >
          > I'm trying to implement a stored procedure to perform updates on Debian
          > Linux packaged FB1.5. Full text of procedure below:
          >
          > CREATE PROCEDURE UPDATE_ZIPCODE (ID INTEGER, ZIPCODE CHARACTER (10),
          > STATE CHARACTER (2), CITY VARCHAR (30))
          > RETURNS (NEWID INTEGER)
          > AS
          > BEGIN
          > IF (id IS NOT NULL) THEN
          > BEGIN
          > UPDATE ZIPCODES SET zipcode=:zipcode, state=:state, city=:city WHERE
          > id=:id;
          > newid=id;
          > END
          > ELSE
          > BEGIN
          > newid = GEN_ID(gen_zipcode_id,1);
          > INSERT INTO ZIPCODES (id, zipcode, city, state)
          > VALUES (:newid, :zipcode, :city, :state);
          > END
          > END !!
          >

          as helen says - but just a note on logic, you are assuming the presence of
          id just because it's being passed to the procedure.
          A better logic would be to select for id and see if the record exists before
          trying to update it. It may not exist, but updating where id does not exist
          will be silent. YOu wont know if anything at all has happened.
          Alan
        • Daniel L. Miller
          ... Thank you for the reply. As I noted - I tried this in ISQL already. I tried it again - same arithmetic exception. I tried running a select procedure from
          Message 4 of 23 , Nov 1, 2004
          • 0 Attachment
            Helen Borrie wrote:

            >At 12:17 AM 1/11/2004 -0800, Daniel Miller wrote:
            >
            >
            >
            >>Hi all.
            >>
            >>I'm trying to implement a stored procedure to perform updates on Debian
            >>Linux packaged FB1.5. Full text of procedure below:
            >>
            >>CREATE PROCEDURE UPDATE_ZIPCODE (ID INTEGER, ZIPCODE CHARACTER (10),
            >>STATE CHARACTER (2), CITY VARCHAR (30))
            >>RETURNS (NEWID INTEGER)
            >>AS
            >>BEGIN
            >> IF (id IS NOT NULL) THEN
            >> BEGIN
            >> UPDATE ZIPCODES SET zipcode=:zipcode, state=:state, city=:city WHERE
            >> id=:id;
            >> newid=id;
            >> END
            >> ELSE
            >> BEGIN
            >> newid = GEN_ID(gen_zipcode_id,1);
            >> INSERT INTO ZIPCODES (id, zipcode, city, state)
            >> VALUES (:newid, :zipcode, :city, :state);
            >> END
            >>END !!
            >>
            >>The idea is to have a single procedure to be used for both inserts &
            >>updates, and have it return the id of the record created/updated. I'm
            >>using ibWebAdmin as my interface. The problem comes when I try to test
            >>the procedure. Executing the following SQL brings these results:
            >>
            >>execute procedure update_zipcode (4,'89102','Las Vegas','NV')
            >>RETURNING_VALUES NEWID
            >>
            >>
            >>>Dynamic SQL Error SQL error code = -104 Token unknown - line 1, char
            >>>
            >>>
            >>63 RETURNING_VALUES
            >>
            >>execute procedure update_zipcode (4,'89102','Las Vegas','NV')
            >>arithmetic exception, numeric overflow, or string truncation
            >>
            >>I tried this in isql also - same results. What am I doing wrong?
            >>
            >>
            >
            >RETURNING_VALUES belongs to PSQL - it's how you bring back return values in
            >variables when you execute one SP inside another. From a client, just call:
            >
            >execute procedure update_zipcode (4,'89102','Las Vegas','NV')
            >
            >and read the return value(s) in the XSQLDA structure. ISQL does this for
            >you: just try it and you should get the result.
            >
            >Depends on your data access interface how you read it in your application,
            >e.g. in IBO, you read it in the Fields[] array of the TIB_Statement
            >object. Jaybird, ODBC, FIBPlus, etc. will each have its own way of
            >encapsulating what comes back.
            >
            >./hb
            >
            >
            Thank you for the reply.

            As I noted - I tried this in ISQL already. I tried it again - same
            arithmetic exception. I tried running a select procedure from ISQL (and
            ibWebAdmin) - no problem. So I tried creating a new procedure like this
            one for another table - and it worked! So it appears to be some kind of
            corruption, either in that particular procedure or in the table (I've
            seen this before, particularly when dealing with views - which is one
            reason I have replaced all my views with stored procedures). So I ran a
            sweep/data repair cycle, and tried again - and now it's working.

            For some reason, my metadata keeps getting corrupted - particularly when
            I make numerous changes to a given table or accessory. I dunno why.

            Daniel
          • Daniel L. Miller
            This is the text of a select procedure I have written to replace a view. It works - so I can continue doing this in this manner, particularly if this how I m
            Message 5 of 23 , Nov 1, 2004
            • 0 Attachment
              This is the text of a select procedure I have written to replace a
              view. It works - so I can continue doing this in this manner,
              particularly if this how I'm "supposed" to do it. But it seems a little
              clumsy, and I don't know how this compiles compared to a different
              implementation.

              Given a table "addresss", with a foreign key zipcode_id in the table
              zipcode, and the function get_zipinfo(), which returns a zipcode, city,
              and state based on an id -

              CREATE PROCEDURE ADDRESSES (ADDRESS_ID INTEGER)
              RETURNS (ID INTEGER, ADDRESS VARCHAR (10), STREET VARCHAR (50),
              STREET_TYPE VARCHAR (30), SUITE VARCHAR (20), CITY VARCHAR (30), STATE
              CHARACTER (2), ZIPCODE CHARACTER (10))
              AS
              DECLARE VARIABLE zipcode_id INTEGER;
              BEGIN
              FOR SELECT address.id, address, street, street_type, suite, zipcode_id
              FROM address, street_types
              WHERE address.id = :address_id and street_type_id = street_types.id
              INTO :id, :address, :street, :street_type, :suite, :zipcode_id
              DO BEGIN
              FOR SELECT city, state, zipcode FROM get_zipinfo(:zipcode_id)
              INTO :city, :state, :zipcode
              DO
              SUSPEND;
              END
              END !!

              The other way I tried (but could not get sucessfully created) was -

              CREATE PROCEDURE ADDRESSES (ADDRESS_ID INTEGER)
              RETURNS (ID INTEGER, ADDRESS VARCHAR (10), STREET VARCHAR (50),
              STREET_TYPE VARCHAR (30), SUITE VARCHAR (20), CITY VARCHAR (30), STATE
              CHARACTER (2), ZIPCODE CHARACTER (10))
              AS
              BEGIN
              FOR SELECT address.id, address, street, street_type, suite,
              (SELECT city, state, zipcode FROM get_zipinfo(:zipcode_id) )
              FROM address, street_types
              WHERE address.id = :address_id and street_type_id = street_types.id
              INTO :id, :address, :street, :street_type, :suite, :city, :state,
              :zipcode
              DO
              SUSPEND;
              END !!

              I kept getting an error on the first comma in the second select -
              (SELECT city,

              Dunno why - syntax SEEMS reasonable . . .

              Daniel
            • Alan McDonald
              ... The syntax in your second statement is, unfortunately, not reasonable, since you are trying to select 3 fields in a select statement slot which is
              Message 6 of 23 , Nov 1, 2004
              • 0 Attachment
                > This is the text of a select procedure I have written to replace a
                > view. It works - so I can continue doing this in this manner,
                > particularly if this how I'm "supposed" to do it. But it seems a little
                > clumsy, and I don't know how this compiles compared to a different
                > implementation.
                >
                > Given a table "addresss", with a foreign key zipcode_id in the table
                > zipcode, and the function get_zipinfo(), which returns a zipcode, city,
                > and state based on an id -
                >
                > CREATE PROCEDURE ADDRESSES (ADDRESS_ID INTEGER)
                > RETURNS (ID INTEGER, ADDRESS VARCHAR (10), STREET VARCHAR (50),
                > STREET_TYPE VARCHAR (30), SUITE VARCHAR (20), CITY VARCHAR (30), STATE
                > CHARACTER (2), ZIPCODE CHARACTER (10))
                > AS
                > DECLARE VARIABLE zipcode_id INTEGER;
                > BEGIN
                > FOR SELECT address.id, address, street, street_type, suite, zipcode_id
                > FROM address, street_types
                > WHERE address.id = :address_id and street_type_id = street_types.id
                > INTO :id, :address, :street, :street_type, :suite, :zipcode_id
                > DO BEGIN
                > FOR SELECT city, state, zipcode FROM get_zipinfo(:zipcode_id)
                > INTO :city, :state, :zipcode
                > DO
                > SUSPEND;
                > END
                > END !!
                >
                > The other way I tried (but could not get sucessfully created) was -
                >
                > CREATE PROCEDURE ADDRESSES (ADDRESS_ID INTEGER)
                > RETURNS (ID INTEGER, ADDRESS VARCHAR (10), STREET VARCHAR (50),
                > STREET_TYPE VARCHAR (30), SUITE VARCHAR (20), CITY VARCHAR (30), STATE
                > CHARACTER (2), ZIPCODE CHARACTER (10))
                > AS
                > BEGIN
                > FOR SELECT address.id, address, street, street_type, suite,
                > (SELECT city, state, zipcode FROM get_zipinfo(:zipcode_id) )
                > FROM address, street_types
                > WHERE address.id = :address_id and street_type_id = street_types.id
                > INTO :id, :address, :street, :street_type, :suite, :city, :state,
                > :zipcode
                > DO
                > SUSPEND;
                > END !!
                >
                > I kept getting an error on the first comma in the second select -
                > (SELECT city,
                >
                > Dunno why - syntax SEEMS reasonable . . .
                >
                > Daniel

                The syntax in your second statement is, unfortunately, not reasonable, since
                you are trying to select 3 fields in a select statement slot which is
                expecting one and only one field value. No wonder it barfs.

                FOR SELECT address.id, address, street, street_type, suite,
                (SELECT city FROM get_zipinfo(:zipcode_id) as city,
                (SELECT state FROM get_zipinfo(:zipcode_id) as state,
                (SELECT zipcode FROM get_zipinfo(:zipcode_id) as zipcode

                would have worked.
                Alan
              • Daniel L. Miller
                ... Thank you! That makes sense - even if I think it s unreasonable for an otherwise perfectly good parser to not be able figure that out . Sheesh. Now
                Message 7 of 23 , Nov 1, 2004
                • 0 Attachment
                  Alan McDonald wrote:

                  >>This is the text of a select procedure I have written to replace a
                  >>view. It works - so I can continue doing this in this manner,
                  >>particularly if this how I'm "supposed" to do it. But it seems a little
                  >>clumsy, and I don't know how this compiles compared to a different
                  >>implementation.
                  >>
                  >>Given a table "addresss", with a foreign key zipcode_id in the table
                  >>zipcode, and the function get_zipinfo(), which returns a zipcode, city,
                  >>and state based on an id -
                  >>
                  >>CREATE PROCEDURE ADDRESSES (ADDRESS_ID INTEGER)
                  >>RETURNS (ID INTEGER, ADDRESS VARCHAR (10), STREET VARCHAR (50),
                  >>STREET_TYPE VARCHAR (30), SUITE VARCHAR (20), CITY VARCHAR (30), STATE
                  >>CHARACTER (2), ZIPCODE CHARACTER (10))
                  >>AS
                  >>DECLARE VARIABLE zipcode_id INTEGER;
                  >>BEGIN
                  >> FOR SELECT address.id, address, street, street_type, suite, zipcode_id
                  >> FROM address, street_types
                  >> WHERE address.id = :address_id and street_type_id = street_types.id
                  >> INTO :id, :address, :street, :street_type, :suite, :zipcode_id
                  >> DO BEGIN
                  >> FOR SELECT city, state, zipcode FROM get_zipinfo(:zipcode_id)
                  >> INTO :city, :state, :zipcode
                  >> DO
                  >> SUSPEND;
                  >> END
                  >>END !!
                  >>
                  >>The other way I tried (but could not get sucessfully created) was -
                  >>
                  >>CREATE PROCEDURE ADDRESSES (ADDRESS_ID INTEGER)
                  >>RETURNS (ID INTEGER, ADDRESS VARCHAR (10), STREET VARCHAR (50),
                  >>STREET_TYPE VARCHAR (30), SUITE VARCHAR (20), CITY VARCHAR (30), STATE
                  >>CHARACTER (2), ZIPCODE CHARACTER (10))
                  >>AS
                  >>BEGIN
                  >> FOR SELECT address.id, address, street, street_type, suite,
                  >> (SELECT city, state, zipcode FROM get_zipinfo(:zipcode_id) )
                  >> FROM address, street_types
                  >> WHERE address.id = :address_id and street_type_id = street_types.id
                  >> INTO :id, :address, :street, :street_type, :suite, :city, :state,
                  >>:zipcode
                  >> DO
                  >> SUSPEND;
                  >>END !!
                  >>
                  >>I kept getting an error on the first comma in the second select -
                  >> (SELECT city,
                  >>
                  >>Dunno why - syntax SEEMS reasonable . . .
                  >>
                  >>Daniel
                  >>
                  >>
                  >
                  >The syntax in your second statement is, unfortunately, not reasonable, since
                  >you are trying to select 3 fields in a select statement slot which is
                  >expecting one and only one field value. No wonder it barfs.
                  >
                  > FOR SELECT address.id, address, street, street_type, suite,
                  > (SELECT city FROM get_zipinfo(:zipcode_id) as city,
                  > (SELECT state FROM get_zipinfo(:zipcode_id) as state,
                  > (SELECT zipcode FROM get_zipinfo(:zipcode_id) as zipcode
                  >
                  >would have worked.
                  >Alan
                  >
                  >
                  Thank you! That makes sense - even if I think it's unreasonable for an
                  otherwise perfectly good parser to not be able figure that out <g>. Sheesh.

                  Now that I know how - is there a preferred method for this kind of thing
                  - inline selects versus forselect constructs? Or is it another example
                  of Firebird flexibility and it's just personal preference?

                  Daniel
                • Alan McDonald
                  ... inline vs for select? there is no Vs here... what do you mean? inline versus join syntax.. there is a Vs here. I personally like inline selects because
                  Message 8 of 23 , Nov 1, 2004
                  • 0 Attachment
                    > >The syntax in your second statement is, unfortunately, not
                    > reasonable, since
                    > >you are trying to select 3 fields in a select statement slot which is
                    > >expecting one and only one field value. No wonder it barfs.
                    > >
                    > > FOR SELECT address.id, address, street, street_type, suite,
                    > > (SELECT city FROM get_zipinfo(:zipcode_id) as city,
                    > > (SELECT state FROM get_zipinfo(:zipcode_id) as state,
                    > > (SELECT zipcode FROM get_zipinfo(:zipcode_id) as zipcode
                    > >
                    > >would have worked.
                    > >Alan
                    > >
                    > >
                    > Thank you! That makes sense - even if I think it's unreasonable for an
                    > otherwise perfectly good parser to not be able figure that out
                    > <g>. Sheesh.
                    >
                    > Now that I know how - is there a preferred method for this kind of thing
                    > - inline selects versus forselect constructs? Or is it another example
                    > of Firebird flexibility and it's just personal preference?
                    >
                    > Daniel

                    inline vs for select? there is no Vs here... what do you mean?
                    inline versus join syntax.. there is a Vs here.
                    I personally like inline selects because they are easier to read than joins
                    but I understand they are less efficient. They may be, but they are still
                    well within acceptable limits for me.
                    Joins are by far the most efficient and they should be SQL99 compliant
                    rather than the SQL92 you have
                    e.g.
                    SELECT address.id, address, street, street_type, suite, zipcode_id
                    FROM address, street_types
                    WHERE address.id = :address_id and street_type_id = street_types.id

                    should be
                    SELECT address.id, address.address, address.street,
                    street_types.street_type, address.suite, address.zipcode_id
                    FROM address join street_types on address.street_type_id =
                    street_types.id
                    WHERE address.id = :address_id

                    better still to use aliases for readability

                    SELECT a.id, a.address, a.street, a.suite, a.zipcode_id, s.street_type
                    FROM address a join street_types s on a.street_type_id = s.id
                    WHERE a.id = :address_id

                    The FOR SELECT ... INTO is still required to read those values into return
                    parameters.
                    FOR SELECT a.id, a.address, a.street, a.suite, a.zipcode_id, s.street_type
                    FROM address a join street_types s on a.street_type_id = s.id
                    WHERE a.id = :address_id
                    INTO .. whatever
                    DO SUSPEND

                    Alan
                  • Ann W. Harrison
                    ... From a performance point of view, first choice is a join. Second choice is nested FOR SELECT. Third choice is lots of subselects. The query optimizer
                    Message 9 of 23 , Nov 1, 2004
                    • 0 Attachment
                      At 02:39 PM 11/1/2004, Alan McDonald wrote:
                      > >
                      > > Now that I know how - is there a preferred method for this kind of thing
                      > > - inline selects versus forselect constructs? Or is it another example
                      > > of Firebird flexibility and it's just personal preference?

                      From a performance point of view, first choice is a join. Second choice
                      is nested FOR SELECT. Third choice is lots of subselects. The query
                      optimizer can do good things with a join. If you list your tables in
                      a reasonable order, the FOR SELECT is almost as good. Each subselect
                      query is executed separately, gaining from lots of cache hits, but still
                      leaving a long code path to get each field value.

                      >Joins are by far the most efficient and they should be SQL99 compliant
                      >rather than the SQL92


                      Err... you mean SQL-92 rather than SQL-89. And absolutely yes, do use
                      explicit aliases. Optional aliases are fine for an interactive language.
                      If you're writing a program, take the time to be explicit about the
                      source of each column.

                      Regards,


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