I have an access db that fully work

[vz]

I have an access db that fully works with my coldfusion interface. I just ported the db over to postgresql one of the insert forms for one of the two tables works and the other one does not for the other table does not. The difference between the 2 tables is that the table that works with its form has only one primary key that is not an autonumber and the other table that doesn't work with its form has 2 primary keys one of which is and auto number. I left the autonumber field hidden when I was using cf with access but when I used cf with coldfusion the error message that I was getting was saying that I was passing a null value, so I created a field for the user to input some number and now I get an illegal operation program will shut down error and I lose connection to my cf server. Does anyone know why? Or what I can do?

 

[DeZiner]

When I am trying to add a record that has a primary key I leave out the primary key field completely in my INSERT statement. It seems all db's should auto add it. DeZiner

When the gears stop turning,
we all stop learning.

 

[vz]

I don't have an actual insert statement on my form I have only one insert statement that has my datasource and table name. Is this correct?
Thanks

 

[strantheman]

hi vz

I like postgres, and hope we can work this out.

Where are you running this 'insert form' ? Command line through a telnet session, or in a SQL query you run using a CFM file ?

Im not sure what you mean by insert form, but i can assume its an import process. If you'd like the best method, export the tables as TAB delimited files named table1.tab and table2.tab. Make sure you store these files somewhere the linux server can access. Copy the contents of the file directly into a temporary replica of your database table. Then insert direct from table to table. The file must be formatted correctly with primary key must be displayed, file must be tab delimited, columns must be in the same order, do not include the column names in the first row.

Using Access 2000, close any windows for tables you're exporting. Right click on the first table, choose export. Name the file table1.tab, put it somewhere the linux server can get to, use the first option "delimited", choose tab, finish.

Repeat for table2.tab.

Create a temporary table with the exact same column order, and column names using the same SQL create script you used for the real table. This temp table will be cleaned before every import, and will allow records to be appended to another table. Name it tmp_table1

Repeat for tmp_table2

In command line linux enter the following where dev/db/ is the location of table1.tab and table2.tab:

\copy tmp_table1 FROM /home/dev/db/table1.tab
INSERT INTO table1
SELECT * FROM tmp_table1;

\copy tmp_table2 FROM /home/dev/db/table2.tab
INSERT INTO table2
SELECT * FROM tmp_table2;

Hope this helps.

 

[vz]

The insert form is just a cold fusion form that a user can input information into and click submit which would put the infor into the database. I have realized that it must be the fact that one of my primary keys is a autonumber in access, since i ported it over to postgres it has no idea what to do with it. Someone told me that i need to create a sequence, so i set one up using pgadmin and it still didn't work. I am not sure how to set one up for a perticular variable. If anyone knows please help.
Thanks

 

[strantheman]

You need to create a sequence field for each table; postgres does not inherently assume that tables will have auto incrementing columns, therefor you must specify a sequence field for each table, even temp tables.

Before creating tables, its important to drop the table and the sequence. The name shown here may not be the actual name of the sequence created. When new tables are created, a sequence is created with the name of the table, underscore, the sequenced field, underscore the word seq. If this name is too long, it may be truncated.

DROP TABLE table1;
DROP SEQUENCE table1_myUniqueid_seq;

CREATE TABLE table1 (
myUniqueid SERIAL PRIMARY KEY,
myName varchar(32)
);


Now that you've defined which keys are sequenced, your import form might work.

Are you still planning to use your method of submitting through a form?

 

[vz]

Thank you for your help. I ported this table over from an access database and if I drop it and create a new one with a sequence how do I get my data into the postgres table? Yes I do plan on using a coldfusion form. Do you think I should do it another way, is there another way?
Thanks

 

[strantheman]

See my first post:

...
\copy tmp_table1 FROM /home/dev/db/table1.tab
INSERT INTO table1
SELECT * FROM tmp_table1;

\copy tmp_table2 FROM /home/dev/db/table2.tab
INSERT INTO table2
SELECT * FROM tmp_table2;
...

You need to have a tab delimited txt file. Follow the export process I explained, and then make sure that the server can access this text file. Then either run these commands from the command line or put it in a CFQUERY tag and execute that template.

good luck