CODEWITHSUNDEEP
sqlstored-proceduresfirebird
Mason Wheeler
Mason Wheeler

Reputation: 84650

Why is my Firebird INSERT procedure failing?

I'm playing around with Web programming and trying to learn the principles involved. I've got a Firebird database, and I'm trying to set up a simple "new user" procedure. It appears to run without error, but it doesn't actually create a new user record.

Here's my procedure:

SET TERM ^ ;

RECREATE PROCEDURE NEW_USER (
 NAME       VARCHAR(32),
 "PASSWORD" VARCHAR(128))
RETURNS (
 ID         INTEGER)
AS 
BEGIN
  if (exists(
    select * from USERS
    where NAME like :name)) then
    id = -1;
   else
     insert into USERS
     (NAME, PASSWORD, SALT)
     values (:name, :password, 's@lt')
     returning ID into :id;
   suspend;
END^

SET TERM ; ^

(Please, no comments about password security. I know. This is only for research purposes right now.)

USERS contains 4 columns: ID (autoincrement int field powered by a generator), NAME, PASSWORD and SALT.

When I try to run it, like so:

select ID from NEW_USER('Test', 'password')

it executes, and returns an empty set. If I then query the table:

select * from USERS

I get another empty set.

Any idea why the procedure isn't creating a new user?

Upvotes: 1

Views: 1775

Answers (1)

ain
ain

Reputation: 22759

What do you mean with "autoincrement int field powered by a generator"? The latest stable release, 2.5 doesn't support autoincrement fields, you have to manually create an trigger to fill in the ID field using the generator... if you haven't done that then this might be the reason nothing is inserted (failing to generate PK field).

Upvotes: 4

Related Questions