CODEWITHSUNDEEP
pythonfirebird
Paulo Freitas
Paulo Freitas

Reputation: 13649

Importing a large SQL file into a local Firebird database

I'm trying to import a large SQL file (15k+ statements) into a new local Firebird database with Python, through fdb module.

import fdb

db = fdb.create_database("CREATE DATABASE 'test.fdb'")
sql_str = open('test.sql').read()
in_trans = False

for stmt in sql_str.split(';'):
    if stmt.startswith('INSERT') and not in_trans:
        in_trans = True
        db.begin()
    else:
        in_trans = False
        db.commit()

    db.execute_immediate(stmt)

I haven't found any better way to do this... (like using a sort of executescript() in sqlite3 module which executes multiple statements per call).

It worked for first few statements, than it stopped and raised that exception:

fdb.fbcore.DatabaseError: ('Error while executing SQL statement:\n- SQLCODE: -104\n- Dynamic SQL Error\n- SQL error code = -104\n- Client SQL dialect 0 does not support reference to BIGINT datatype', -104, 335544569)

From what I read in Firebird documentation, I've to use SQL dialect 3 to be able to use BIGINT datatype in table schemas. I'm already correctly setting database SQL dialect 3 with fdb.create_database() default arguments, but I'm still need to set client SQL dialect and I've no idea where and how I do that. I'm using Firebird 2.5.2 and Python 2.7.2 on OS X.

Upvotes: 3

Views: 2486

Answers (3)

NickUpson
NickUpson

Reputation: 146

isql -user sysdba -pass

not 100% sure above is exactly right syntax

isql -user sysdba -pass test.fdb < test.sql

Upvotes: 0

Mark Rotteveel
Mark Rotteveel

Reputation: 109138

As far as I know, fdb should default to dialect 3, but you can specify it explicitly using connection property dialect=3, see example 2 in Connecting to a database. The weird thing is though that there is no such thing as a dialect 0 in Firebird.

If that does solve the problem, I'd suggest you file a bug in the issue tracker as the driver should default to dialect 3: http://tracker.firebirdsql.org/browse/PYFB

Upvotes: 1

rstrelba
rstrelba

Reputation: 1964

Better way to import large sql file into firebird database is to use isql

Upvotes: 0

Related Questions