I have written the following code:
ALTER PROCEDURE uspNewCustomer(IN myCustomerName VarChar, OUT myCustomerID INTEGER)
BEGIN
RAISE ERROR CODE 10000 MESSAGE myCustomerName;
EXECUTE IMMEDIATE 'INSERT INTO Customer (CustomerName) VALUES (?)'
                  USING myCustomerName;

EXECUTE IMMEDIATE 'SELECT LASTIDENTITY (''Customer'', ''CustomerID'') as ?'
                  USING myCustomerID;
END!
I am running this in ElevateDB manager 2.31 Build 10. When running it asks for a parameter, I was getting an error that CustomerName could not be NULL when running the INSERT. I added the MESSAGE code and it is also showing myCustomerName as blank even though I type in a name. I am at a loss.

Customer was created as:
CREATE TABLE "Customer" (
   "CustomerID"   integer generated always as IDENTITY (start with 1, increment by 1) not null,  
   "CustomerName" VARCHAR (40) collate "ANSI_CI" NOT NULL,  
   "YTDOrders"    integer default 0 NOT NULL,
   "YTDSales"     integer default 0 NOT NULL  )!

Bill

William


This

ALTER PROCEDURE "uspNewCustomer" (IN "myCustomerName" VARCHAR COLLATE "ANSI", OUT "myCustomerID" INTEGER)
BEGIN
--RAISE ERROR CODE 10000 MESSAGE myCustomerName;
EXECUTE IMMEDIATE 'INSERT INTO Customer (CustomerName) VALUES (?)'
                  USING myCustomerName;

EXECUTE IMMEDIATE 'SELECT LASTIDENTITY (''Customer'', ''CustomerID'') AS myCustomerID';
END
VERSION 1.00

works here

The only way I can replicate what you're getting is to not click the set parameter value button.


Roy Lambert

Thanks, getting much better. The big thing I had missed was that you had to click 'set parameter value' after typing in the parameter. That is what I get for working at midnight. One more question on this, my code is now:

ALTER PROCEDURE "uspNewCustomer" (IN "myCustomerName" VARCHAR COLLATE "ANSI", OUT "myCustomerID" INTEGER)
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO Customer (CustomerName) VALUES (?)'
                 USING myCustomerName;

EXECUTE IMMEDIATE 'SELECT LASTIDENTITY (''Customer'', ''CustomerID'') AS myCustomerID';
RAISE ERROR CODE 10000 MESSAGE CAST(myCustomerID AS varchar);
END!

First, is there a better way to show values then popping an error window, but more important is that this message is showing myCustomerID as blank or NULL if I let the procedure finish and see what ElevateDB manager shows as the return. Nothing is getting put in myCustomerID.
Bill

Got it to work like this:
alter PROCEDURE "uspNewCustomer" (IN "myCustomerName" VARCHAR COLLATE "ANSI", OUT "myCustomerID" INTEGER)
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO Customer (CustomerName) VALUES (?)'
                 USING myCustomerName;

EXECUTE IMMEDIATE 'SELECT LASTIDENTITY (''Customer'', ''CustomerID'') into ?'
                  USING myCustomerID;
END!

Instead of select … as myCustomerID I changed it to into ? with a using. Now getting output in ElevateDB Manager. Now to figure out how to call from C#, making progress.
Bill

Bill,

Just a quick addition here: EDB automatically sets up INSERT parameters so that they are IN-OUT parameters (this is separate from the procedure parameters and only applies to the EXECUTE/EXECUTE IMMEDIATE statements).  So, this means that you can just use this:

CREATE PROCEDURE "uspNewCustomer" (IN "myCustomerName" VARCHAR COLLATE "ANSI", OUT "myCustomerID" INTEGER)
BEGIN
  EXECUTE IMMEDIATE 'INSERT INTO Customer (CustomerID, CustomerName) VALUES (?, ?)'
                    USING myCustomerID, myCustomerName;
END

If you test this out in the EDB Manager, you'll see that the myCustomerID parameter is automatically populated with the generated identity value for the new row's CustomerID column.

Tim Young
Elevate Software
www.elevatesoft.com