At 12:22 PM 27/06/2009, you wrote:

>I have a project that I am planning on using firebird db for hte backend. I have a number of questions
>
>1. MSSQL Primary Keys can autoincrement. What do I do here to get a Primary Key. Looks like I need to create a procedure for but would it be.

No. Declare the column as BigInt (or a BigInt domain if you prefer), create a generator (for general or table-specific use) and write Before Insert triggers on the tables.

>Could someone provide a simple sample of how they solved this problem.

, e.g., for a sample script snippet:

create generator gen_anything;

create table blah (
id BigInt not null,
....,
.....,
constraint pk_blah PRIMARY KEY(id));
commit;
set term ^;
create trigger bi_blah for blah
active before insert position 0
as
begin
if (new.id is null) then
new.id = gen_id(gen_anything, 1);
end ^

>2. Links I see some links but I do not see anything that give concrete examples;
> A. How to insert into a table.

Firebird's SQL is standard - Google for on-line tutorials, avoiding those that use MsSQL or MySQL, neither of which is standard. For the full story visit http://firebirdsql.org/index.php?op=doc - you can grab the InterBase 6 LangRef.pdf plus the incremental Lang. refs for Firebird.

> B. How to Update into a table using prcedures.

If you know how to update a table, doing it via a procedure will become obvious. PSQL is SQL with some extra bits.

>3. I am using FlameRobin is there a better IDE?

Depends on what you want and whether you're prepared to pay. www.ibphoenix.com has a pretty full, classified run-out of links to tools in its Downloads section.

>4. Here is proc I am attempting to write
>
>CREATE PROCEDURE LAYOUTINSERT
> ( IN_TITLE varchar(50), IN_TABLENAME varchar(50) )
>RETURNS ( OUT_LAYOUTID INT )
>AS
>DECLARE VARIABLE VAR_CURRENT_TIME;

missing TIME type in the variable definition

>BEGIN ****This is line 6****
>
> VAR_CURRENT_TIME = current_time;

You don't need this variable. CURRENT_TIME is a context variable in its own right. But CURRENT_TIME seems totally not what you want to store here, anyway. It only stores the time of day. I think you want CURRENT_TIMESTAMP.

> OUT_LAYOUTID = (select max(LAYOUTID) from LAYOUT);

Wrong syntax.
select max(LAYOUTID) from LAYOUT into :OUT_LAYOUTID;

(But this is not a safe practice in a transactional database system).

If you have your autoinc trigger on LAYOUT you just need a DSQL statement straight from your application that omits the PK:

INSERT INTO LAYOUT (LAYOUTTITLE, CREATEDON, MODIFYON, TABLENAME)
VALUES ( :IN_TITLE, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, :IN_TABLENAME)

No need for a SP to do this.

But if you're performing any DML (such as an insert) from a PSQL module, you need to prefix the variables with colons (see above).

> I get an error
>Engine Code : 335544569
>Engine Message :
>Dynamic SQL Error
>SQL error code = -104
>Token unknown - line 6, column 1
>BEGIN

Yup, see above, the parser is expecting a data type in the preceding variable declaration.

>5. Also how do I a comments

Read the language references !!

Also, consider automating your timestamping as well, by including it in your trigger. You can then omit it from your dynamic insert statement.

create trigger bi_blah for blah
active before insert position 0
as
begin
if (new.id is null) then
new.id = gen_id(gen_anything, 1);
if (new.createdon is null) then
new.createdon = current_timestamp;
end

./heLen