Link to home
Create AccountLog in
Avatar of OTSSolutions
OTSSolutionsFlag for India

asked on

Inserting data from a .txt file

Hi,
I am very new to FireBird2.1. I have a situation wher I need to insert records more than 80000  at a time. For that i used jdbc prepare statement with positional parameter and batch update. But it is too slow and takes time almost 5min. By examining i found that time taking portion is creating batch query. When I search on google one sollution is found. That is First i need to write the 80000 record to .txt file in  organised format after that insert all records from .txt file to  a temporary database table. From the temporary table i can inser the records to our desire table. I approached accordingly but one exception arise like " Access to external file "D:\EXT.TXT" is denied by server administrator. "  .
The steps of my approach is as follows:
1. Create .txt file with only one valu string"
2.Execution of the following query in Firebird query browse:
CREATE TABLE ext1 EXTERNAL FILE 'D:\EXT.txt'
(
field1 varchar(40)
);

The above steps creats only table name in the database. when i want to acess the tabe i found the above message Access to external file "D:\EXT.TXT" is denied by server administrator.

I set the paramete ExternalFileAccess = Full in firebird.conf file . Still it showing the error message.

Can anybody help me where i a wrong? More over i need the data format in the .txt file.

ASKER CERTIFIED SOLUTION
Avatar of Nick Upson
Nick Upson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of OTSSolutions

ASKER

I have tested as per your suggestion.. Still it is getting the same message
you say you get the error message when you attempt to access the table, what exactly are you doing to access it?

which version of firebird 2.1 are you using, embedded, classic or superserver?

place the file (and change the definition) in a different location, which must be a local harddrive, a network drive will not work
I have php class written for that. Backup is txt or sql file in same format as phpMyAdmin,
also there are script that provide restore, download, upload to separate server etc...

Default backup for tables
SELECT * INTO OUTFILE

restore
LOAD DATA INFILE
Check the "security" on that data file, and make sure it is readable by the Firebird owner, or easier yet for testing it, make it readable by everybody.  Remember Firebird would be running as it's own user (ideally, anyway; or could be local service).