Hi,
I have been having difficulty getting firebird to input my sql.

I am doing the following in linux so that my firebird will work
embedded in my application. First I do a static build of firebird.
Then I use isql_static to create
my database. My application links to a static libfbembed.a

Now, the problem I've been running into is creating the database for
my application.

I have my database broken into a set of sql files.

I then construct my database using the following in my makefile:

simo.fdb: (SQL_FILES)
rm -f simo.fdb
rm -f $(OBJDIR)/bin/simo.fdb
@echo "CREATE database 'simo.fdb' user 'simo' password 'simo';" >
__db_tmp__.sql
$(FBISQL) -input __db_tmp__.sql
if [ -e SIMO.FDB ]; then mv -f SIMO.FDB simo.fdb; fi;
rm __db_tmp__.sql
$(FBISQL) simo.fdb -u simo -p simo -input BaseDBDef.sql
$(FBISQL) simo.fdb -u simo -p simo -input PersonDBDef.sql

Contents of PersonDBDef.sql are at the end of this message.
This works perfectly in windows using isql with my fbembed.dll renamed
to fbclient.dll
But in linux I have not got it work with either my static build of
isql or a normal build of isql.

Here's the output from running make:

rm -f /home/taf2/simo2/debug/bin/simo.fdb
sonic~/simo2/components/db> make
rm -f simo.fdb
rm -f /home/taf2/simo2/debug/bin/simo.fdb
/home/taf2/fedoracore3/firebird2/gen/firebird//bin/isql_static -input
__db_tmp__.sql
Use CONNECT or CREATE DATABASE to specify a database
if [ -e SIMO.FDB ]; then mv -f SIMO.FDB simo.fdb; fi;
rm __db_tmp__.sql
/home/taf2/fedoracore3/firebird2/gen/firebird//bin/isql_static
simo.fdb -u simo -p simo -input
/home/taf2/simo2/components/db/BaseDBDef.sql
/home/taf2/fedoracore3/firebird2/gen/firebird//bin/isql_static
simo.fdb -u simo -p simo -input
/home/taf2/simo2/components/db/PersonDBDef.sql
Statement failed, SQLCODE = -206

Dynamic SQL Error
-SQL error code = -206
-Column unknown
-PERSON.ID
-At line 5, column 13.
make: *** [/home/taf2/simo2/debug/bin/simo.fdb] Error 1

The database creates fine. I can open it in isql and I see the Person
table was created:
sonic~/simo2/components/db>
/home/taf2/fedoracore3/firebird2/gen/firebird//bin/isql_static
simo.fdb -u simo -p simo
Database: simo.fdb, User: simo
SQL> show tables;
PERSON
SQL>

The error message from the make is interesting though and I'll get
that error even i enter the trigger manually from isql. I've also
tried putting an extra commit in the PersonDBDef.sql file after the
table is created, thinking maybe the linux version needs me to commit
before it will recongize PERSON.ID as a valid column of the person
table that I just created. I also found that in linux version of isql
if the line endings contain \r isql will fail, which to me just seems
like the parser should be able to handle. I made sure the file has 1.
consistent line endings and 2. unix line endings only \n. by running
dos2unix PersonDBDef.sql

So, i'm really at a loss as to why this should work any different in
linux then in windows. I think I must be doing something wrong in my
sql file that is probably working in windows just by chance and not in
linux because it's broken. Or worse the linux version isql has a bug.
I'm really hoping it's the previous and that it's a bug in my sql :-)

Any help would be great thanks,
Todd

SET WARNINGS ON;
--SET ECHO ON;
-- Person Tables --------------------------------------------------------------
CREATE TABLE person (
id INTEGER NOT NULL,
first_name VARCHAR(30) NOT NULL,
middle_name VARCHAR(30),
last_name VARCHAR(30) NOT NULL,
remove_state INTEGER NOT NULL,
birthday date,
ss_number VARCHAR(11),
contact_id INTEGER,
gender_id INTEGER,
height INTEGER,
weight INTEGER,
blood_type_id INTEGER,
race_id INTEGER,
marital_status_id INTEGER,
employer_name VARCHAR(100),
primary_physician_id INTEGER,
primary_dentist_id INTEGER,
primary_pharmacy_id INTEGER,
primary_plan_id INTEGER,
emergency_contact_name VARCHAR(100),
emergency_contact_phone phone,
relationship_to_person VARCHAR(100)
);

CREATE UNIQUE INDEX XPKperson ON person
(
id
);

ALTER TABLE person ADD PRIMARY KEY (id);

-- Triggers and Generators ---------------------------------------------------
CREATE GENERATOR person_seq;

SET TERM !!;
CREATE TRIGGER person_generator FOR person
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
IF (person.id IS NULL) THEN
person.id = GEN_ID(person_seq,1);
END!!
SET TERM ;!!

-- Commit Changes --------------------------------------------------------------
commit;