nakagami/firebirdsql

Firebird RDBMS sql driver for Go (golang)

firebirdsql's Issues

github.com/nyarla/go-crypt dependency has gone missing

The github.com/nyarla/go-crypt import in wireprotocol.go isn't available anymore and breaks building anything using this library. The author has removed the repository and seems to have moved it to GitLab: https://gitlab.com/nyarla/go-crypt

The homepage of the author (https://nyarla.net/) lists

GitLab: @nyarla (開発・移住した)
GitHub: @nyarla (開発・停止中)

Google Translate:

GitLab: @nyarla (developed / immigrated)
GitHub: @nyarla (under development / stop)

Error op_response inserting rows with large blobs

Inserting rows larger than 64K including blobs results in an error. It appears that blobs are being written like VARCHARs rather than a blob being created and its ID included in the row.

func blobOfSize(size int) []byte {
    var buf bytes.Buffer
    for i := 0; i < size; i++ {
        buf.WriteRune('A')
    }
    return buf.Bytes()
}

func TestInsertLargeBlob(t *testing.T) {
    conn, _ := sql.Open("firebirdsql_createdb", "sysdba:masterkey@localhost:3050/tmp/go_test_insert_large_blob.fdb")
    conn.Exec("CREATE TABLE test_blobs (f1 BLOB SUB_TYPE 1)")
    defer conn.Close()

    b0 := blobOfSize(65533)
    if _, err := conn.Exec("INSERT INTO test_blobs (f1) values (?)", b0); err != nil {
        t.Fatalf("Error inserting blobs with params: %v", err)
    }

    var b []byte
    err := conn.QueryRow("SELECT f1 from test_blobs").Scan(&b)
    if err != nil {
        t.Fatalf("Error in query: %v", err)
    }
    if !reflect.DeepEqual(b, b0) {
        t.Fatalf("Binary blob: expected <%v>, got <%v> (%s)", b0, b, string(b))
    }
}

Embedded DB

Hey, is there some mode to connect an embedded DB?

go1.8: support for additional features

Many new important features will land in go1.8. Please see document:
https://docs.google.com/document/d/1F778e7ZSNiSmbju3jsEWzShcb8lIO4kDyfKDNm4PNd8/edit?usp=sharing for the full list.

Because these features are new we want drivers to implement them before they are released. Ideally they should be implemented by 2016-12-12 to allow for any driver provided feedback from implementation if necessary.

Query with string parameter

Which way should I use for string querying?
For example, I need select from db something like this:

SELECT word FROM words WHERE word LIKE '%suggest%';

In Go program I do

queryString := "SELECT word FROM words WHERE word LIKE '%?%'" 
rows, err := db.Query(queryString, "suggest")

and that does not work. Selected words contains question mark not "suggest" substring.

Fmt works fine:

queryString := fmt.Sprintf("SELECT word FROM words WHERE word LIKE '%%%s%%'", "suggest")  

but I don't like string manipulation for tihis case...

This question is firebirdsql driver related?

Selecting DECIMAL always returns 0

Hi,

rows, _ := conn.Query("SELECT DEC FROM MYTABLE")
for rows.Next() {
    var dec sql.NullFloat64
    rows.Scan(&dec)
    fmt.Println(dec)
}

output: {0 true}

Firebird 2.1.2 (Windows)

Am I doing it wrong?

Interbase 2007 Connection

Hi

Please, when I try to connect to a remote Interbase 2007 using the example in README.md, I get no errors, but always return count = 0.
If I use fmt.Println(conn.Ping()), I get this error:
opAccept() protocol error

I know that this driver is not specificaly for Interbase, but Firebird and Interbase are usualy interchangable.
And if i use this driver https://github.com/rowland/go-fb it's work, but the driver does not currently conform to database/sql/driver interfaces.

Thanks in advance

PS: I use ubuntu 14.04 x64 with packages:
firebird-dev
firebird2.5-classic-common
firebird2.5-common
firebird2.5-common-doc
firebird2.5-server-common

Connection doesn't return errors!

Don't know if I'm doing something wrong but when I try to run this example code:

package main

import (
    "fmt"
    "database/sql"
    _ "github.com/nakagami/firebirdsql"
)

func main() {
    var n int
    conn, _ := sql.Open("firebirdsql", "user:password@servername/foo/bar.fdb")
    defer conn.Close()
    conn.QueryRow("SELECT Count(*) FROM rdb$relations").Scan(&n)
    fmt.Println("Relations count=", n)
}

It always returns "Relations count=0" even thou the connection string is invalid (I don't have any firebird sql server running in my test VM).

Error op_response:0 in next query

Hi. I test rewrite this simple web-app https://grisha.org/blog/2017/04/27/simplistic-go-web-app/ for Firebird. I have DB with some data. I have project writing with article analog https://gitlab.com/krak/test.
If I get http://127.0.0.1/carrier I recieve json string with data. If I refresh this page - take this data again I get error Error op_response:0.
This error I can see in debug https://gitlab.com/krak/test/blob/master/db/firebird.go#L69
I see this error in other issue #29, but I don't sure that's equal problem.

Wrong logic for handling large BLOBs?

Shouldn't line 441 of wireprotocol.go be

for more_data == 1 {

instead of

for more_data != 2 {

I'm finding that the protocol will return a 0 instead of a 1 every 507873 bytes (strange number).
As a result of this logic, my BLOB is being truncated.

I haven't been able to prove that 0 is an OK response as I can't find the spec and reading the Firebird Server C++ code isn't my strong suit. But changing it seems to work.

Your input would help a lot.

P.S: I noticed that the same logic is also found in your Python version
I've filed a similar issue there.

Using "github.com/jmoiron/sqlx"

I'm trying to use the library "github.com/jmoiron/sqlx", but returns nothing. There is a known incompatibility?

error handling/messages Problem

Hello,
I tried the following (should return something like : Artithmetic Overflow or Division / 0)

package main
import (
"database/sql"
"fmt"
"log"
_ "github.com/nakagami/firebirdsql"
)
func main() {
var n int
conn, err := sql.Open("firebirdsql", "sysdba:[email protected]/wetten")
if err != nil {
log.Fatal(err)
}
defer conn.Close()
err = conn.QueryRow("SELECT 5 / 0 FROM rdb$database").Scan(&n)
if err != nil {
log.Fatal(err)
}
fmt.Println("Result=", n)
}

And I get:
2017/06/15 23:05:51 sql: no rows in result set
This is correct, but why I'm not getting the real error message?

On Server Side (log from firebirdsql) I get
Thu Jun 15 23:05:09 2017
INET/Inet_error: read errno = 104

if I change the query to
err = conn.QueryRow("SELECT 5 / 2 FROM rdb$database").Scan(&n)
(this produces no error, of course)
anything is ok and on server side there is not INET/Inet_error anymore

How can I get (possible) runtime Errors?

If i change from QueryRow to Query and loop over row.next then I never get an error
If i use Transactions then on commit (or rollback) I get
opcode=0

What I'm doing wrong?

Unavailable database using wire_crypt=false

I changed my firebird.conf file to "WireCrypt = Disabled" and restarted my server. From python, I could connect to database as usual, but from "GO", even using "wire_crypt=false" as a parameter, it says: "unavailable database".

If i change firebird.conf to "WireCrypt = Enabled" and GO to "wire_crypt=true" it works, cuz that's the default for firebird 3.

Thanks.

Cannot disconnect database with open transactions

package main

import (
	"database/sql"
	"fmt"

	_ "github.com/nakagami/firebirdsql"
)

func main() {
	var n int
	conn, _ := sql.Open("firebirdsql", "sysdba:masterkey@localhost/test")

	conn.QueryRow("SELECT Count(*) FROM rdb$relations").Scan(&n)
	fmt.Println("Relations count=", n)
	err := conn.Close()
	if err != nil {
		fmt.Println(err)
	}
}

return

Relations count= 42
cannot disconnect database with open transactions (1 active)

RETURNING clause in INSERT statements

It seems that Statement.Exec returns an empty Result instance with the LastInsertId and RowsAffected set explicitly to 0. Is there any chance to make this work with the RETURNING statement? E.g.:

res, err := db.Exec("INSERT INTO foo(a, b) VALUES(?, ?) RETURNING id", a, b)
...
_ = res.LastInsertId()

Error inserting null values to DB

Almost every null value that I try to insert gives an ERROR now. I suggest a new test with every single type of var using "null".

This time u don't even need to ask for an example... Here it go ;)

sqlCreate := `
    CREATE TABLE NullTest (
        id INTEGER generated by default as identity primary key,
        name VARCHAR(60) NOT NULL,
        nullname VARCHAR(10),
        nullDate DATE,
        bug1 SMALLINT,
        bug2 INTEGER
    )
`
_, err := dbconfig.DBConn.Exec(sqlCreate)
if err != nil { fmt.Println(err) }

// FAIL
sqlTest1 := `
    insert into NullTest (name, bug1, bug2)
    values ('Giovanni', ?, ?)
`
_, err = dbconfig.DBConn.Exec(sqlTest1, nil, nil)
if err != nil {
    fmt.Println("Test 1: ", err)
} else { fmt.Println("Test 1: OK") }

// WORK
sqlTest2 := `
    insert into NullTest (name, nullname, nullDate)
    values ('Giovanni', ?, ?)
`
_, err = dbconfig.DBConn.Exec(sqlTest2, nil, nil)
if err != nil {
    fmt.Println("Test 2: ", err)
} else { fmt.Println("Test 2: OK") }

// WORK
var strDate = "2017-10-24"
var nullName *string
sqlTest3 := `
    insert into NullTest (name, nullname, nullDate)
    values ('Giovanni', ?, ?)
`
_, err = dbconfig.DBConn.Exec(sqlTest3, nullName, strDate)
if err != nil {
    fmt.Println("Test 3: ", err)
} else { fmt.Println("Test 3: OK") }

// FAIL
var nullDate *string // Since it's just a "null" value, it should work
sqlTest4 := `
    insert into NullTest (name, nullDate)
    values ('Giovanni', ?)
`
_, err = dbconfig.DBConn.Exec(sqlTest4, nullDate)
//_, err = dbconfig.DBConn.Exec(sqlTest4, nil) // also fails
if err != nil {
    fmt.Println("Test 4: ", err)
} else { fmt.Println("Test 4: OK") }

// WORK
sqlTest5 := `
    insert into NullTest (name, nullDate)
    values ('Giovanni', null)
`
_, err = dbconfig.DBConn.Exec(sqlTest5)
if err != nil {
    fmt.Println("Test 5: ", err)
} else { fmt.Println("Test 5: OK") }

sqlDrop := `
    drop table NullTest
`
dbconfig.DBConn.Exec(sqlDrop)

Thank You again for ur time.

Selecting blobs always returns nil

rows, _ := conn.Query("SELECT BLOB_FIELD FROM MY_TABLE")
for rows.Next() {
    var blob sql.RawBytes
    rows.Scan(&blob)
    println(blob)
}

This outputs: [0/0]0x0

Am I doing it wrong?

Firebird 2.5.2 on Windows.

No results and no error in query select with 21 columns or more

Hi,

Whe you have a query with two tables and in the select you put specific columns of both tables, the Query function return no result, if I use and select * from both tables then it works.

I attach a program and a example database where I reproduce this error.

All the querys of the file where tested with flamerobin and run correctly, however I don't know why in the go program doesn't work.

Any comment to where I can look for the error will be appreciated.

PRUEBAS.zip
main.zip

Error inserting blob

If I try to insert a huge string the database freezes. It won't give any error, but the connection to the database gets kind of "bugged". This is not happening in python with firebird, just in GO. It's not related to GO cuz I can insert the same string in a PostgreSQL database using GO. So, It's related to the firebird driver ...

I can select a string of the same size and put it onto a var and print it (that works). The problem is just on insert/update...
Example:

func HugeBlobTest() {
sqlCreate := CREATE TABLE BlobTest ( id INTEGER generated by default as identity primary key, bugField blob sub_type text )
_, err := dbconfig.DBConn.Exec(sqlCreate)
if err != nil { fmt.Println(err) }

tx, err := dbconfig.DBConn.Begin()

var hugeBlob bytes.Buffer
for i:= 0; i < 671633; i ++ { // if you change to 670000, it works
    hugeBlob.WriteString("F")
}

if err != nil { fmt.Println(err) }
sqlTest1 := insert into BlobTest(bugField) values(?)
_, err = tx.Exec(sqlTest1, hugeBlob.String())
if err != nil {
fmt.Println("Test: ", err)
} else { fmt.Println("Test: OK") }
tx.Commit()
}

Thanks.

srp256

Support srp256 authentication for FB4.

nakagami/pyfirebirdsql@61e1f35

tx.Rollback() returns "op_response:0"

I have the following table created:

CREATE TABLE my_table (
  id  INTEGER NOT NULL
  key VARCHAR(64) NOT NULL
  value VARCHAR(64)
);

Respectively in go:

// db.go
package utils
import (
	"database/sql"
	_ "github.com/nakagami/firebirdsql"
	"log"
)

var DB *sql.DB

func InitDB(dataSourceName string) {
	var err error
	DB, err = sql.Open("firebirdsql", dataSourceName)
	if err != nil {
		log.Panic(err)
	}

	if err = DB.Ping(); err != nil {
		log.Panic(err)
	}
}

//main.go
package main

import (
	"log"
	"test/utils"
	"database/sql"
)

utils.InitDB("sysdba:[email protected]/TEST")

trans, err := utils.DB.Begin()
if err != nil {
	log.Println(err)
}
var rowId = sql.NullInt64{}

err = trans.QueryRow(
	"INSERT INTO my_table (id, key, value) VALUES (?, ?, ?) RETURNING id",
	1, "testKey", "testValue").
	Scan(&rowId)

if (err != nil) || (!rowId.Valid) || (rowId.Int64 <= 0) {
	log.Println(err)
	err = trans.Rollback()  // returns "op_response:0"
	if err != nil {
		log.Println(err.Error())
	}
} else {
       trans.Commit()
}

• If correct values are passed as arguments to tx.QueryRow, everything is ok and the query is committed correctly.
• If for some reason the query fails (eg if invalid arguments are passed), tx.QueryRow returns an error - thats normal. But after if I try to rollback the transaction - it also fails with an error message "op_return:0". The more frustrating is that each next call to DB.Begin() returns the same transaction.

I tried x86,x64 versions of Firebird 2.5, different ways to execute the query - via stored procedure; just a tx.Execute() without a returning clause.

github.com/nyarla/go-crypt vanished

This package uses github.com/nyarla/go-crypt, but that is not available anymore.

firebirdsql/wireprotocol.go

Line 43 in da4be41

"github.com/nyarla/go-crypt"

Firebird Events

Hi there!

Is it possible to implement Firebird Events??

"invalid transaction handle" after transaction and subsequent select statement

Maybe this is related to #16.

After a successful transaction, if I select something afterwards, I get the following error (on windows and linux, with Go 1.4.2 and 1.5 RC1):

invalid transaction handle (expecting explicit transaction start)

Here is a minimal example:

tx, err := db.Begin()
if err != nil {
    log.Fatal(err)
}

sqlStr := "INSERT INTO MYTABLE (NAME) VALUES (?)"
if _, err := tx.Exec(sqlStr, "test"); err != nil {
    tx.Rollback()
    log.Fatal(err)
}

if err := tx.Commit(); err != nil {
    log.Fatal(err)
}

var name string
sqlStr = "SELECT NAME FROM MYTABLE WHERE ID = 1"
if err = db.QueryRow(sqlStr).Scan(&name); err != nil {
    log.Fatal(err) // <- invalid transaction handle
}

The same code works with other drivers.
Really helpful library, btw! 👍

Date & Timestamp Timezone

Hi,
Great library, Thank you.

I have bumped up against an issue.
Using firebirdsql all date & timestamp values are returned as UTC
When writing to DB, If all Go time.Time values are converted to UTC before passing to firebirdsql, then results are as expected.

When inserting dates & timestamp using a time.Time with a non UTC timezone problems can occur

---

In driver_test.go ,
func TestInsertTimestamp(t *testing.T)
if change
dt1 := time.Date(2015, 2, 9, 19, 25, 50, 740500000, time.UTC)
to

loc, _ := time.LoadLocation("America/New_York")  //any non UTC location will do
dt1 := time.Date(2015, 2, 9, 19, 25, 50, 740500000, loc)

test will fail

---

Perhaps this could be solved by taking time=time.UTC() before writing to DB?
ie. in utils.go (sorry i'm not set up to do a pr at the moment)

func _convert_date(t time.Time) []byte {
        t = t.UTC()
    i := int(t.Month()) + 9
    jy := t.Year() + (i / 12) - 1
    jm := i % 12
    c := jy / 100
    jy -= 100 * c
    j := (146097*c)/4 + (1461*jy)/4 + (153*jm+2)/5 + t.Day() - 678882
    return bint32_to_bytes(int32(j))
}

func _convert_time(t time.Time) []byte {
        t = t.UTC()
    v := (t.Hour()*3600+t.Minute()*60+t.Second())*10000 + t.Nanosecond()/100000
    return bint32_to_bytes(int32(v))
}

Thanks again for the great library.

support for embedded database

I need to deploy firebirdsql in embedded mode. using isql we can connect just using database filename without host+port. is this feature supported in near future?

Not properly work after transaction

`package main

import (
"database/sql"
"log"

_ "github.com/nakagami/firebirdsql"

)

func main() {

conn, err := sql.Open("firebirdsql", "SYSDBA:[email protected]/d:/Temp/TestDatabases/TEST.FDB")
defer conn.Close()

if err != nil {
    log.Fatal(err)
}

// контроль связи с БД
err = conn.Ping()
if err != nil {
    log.Fatal(err)
}

tx, err := conn.Begin()

if err != nil {
    log.Fatal(err)
}

err = tx.Commit()

if err != nil {
    log.Fatal(err)
}

const query = `CREATE TABLE SensorLog(SN INTEGER NOT NULL);`

_, err = conn.Exec(query)

if err != nil {
    log.Fatal(err)
}

}`

panic when SELECT

I have such table

CREATE TABLE TSTING3
(
  f1 integer NOT NULL,
  f2 integer,
  f3 integer NOT NULL,
  f4 integer NOT NULL,
  f5 integer NOT NULL,
  f6 integer NOT NULL,
  f7 varchar(255) NOT NULL,
  f8 varchar(255) NOT NULL,
  f9 varchar(255) NOT NULL,
  f10 varchar(255) NOT NULL,
  f11 varchar(255) NOT NULL,
  f12 varchar(255) NOT NULL,
  f13 varchar(255) NOT NULL,
  f14 varchar(255) NOT NULL,
  f15 integer,
  f16 integer,
  f17 integer,
  f18 integer,
  f19 integer,
  f20 integer,
  f21 integer,
  f22 varchar(1),
  f23 varchar(255),
  f24 integer,
  f25 varchar(64),
  f26 integer  
);

and try

rows, err := conn.Query("SELECT * FROM TSTING3")

i've got error

panic: runtime error: slice bounds out of range

goroutine 1 [running]:
runtime.panic(0x770ec0, 0xc2fb8a)
        C:/Users/ADMINI~1/AppData/Local/Temp/2/makerelease250988475/go/src/pkg/runtime/panic.c:266 +0xc8
github.com/nakagami/firebirdsql.(*wireProtocol).parse_xsqlda(0xc0840362d0, 0xc0840d9800, 0x400, 0x400, 0x2, ...)
        Z:/Projects/GO/src/github.com/nakagami/firebirdsql/wireprotocol.go:398 +0x820
github.com/nakagami/firebirdsql.newFirebirdsqlStmt(0xc0840da930, 0x898230, 0x4f, 0xc0840d39b0, 0x0, ...)
        Z:/Projects/GO/src/github.com/nakagami/firebirdsql/statement.go:108 +0x168
github.com/nakagami/firebirdsql.(*firebirdsqlConn).Prepare(0xc0840da930, 0x898230, 0x4f, 0x59b565, 0xc0840dc4e8, ...)
        Z:/Projects/GO/src/github.com/nakagami/firebirdsql/connection.go:57 +0x43
github.com/nakagami/firebirdsql.(*firebirdsqlConn).Query(0xc0840da930, 0x898230, 0x4f, 0xc3f3f8, 0x0, ...)
        Z:/Projects/GO/src/github.com/nakagami/firebirdsql/connection.go:78 +0x70
database/sql.(*DB).queryConn(0xc08405af00, 0xc0840dc4e0, 0xc0840dfea0, 0x898230, 0x4f, ...)
        C:/Users/ADMINI~1/AppData/Local/Temp/2/makerelease250988475/go/src/pkg/database/sql/sql.go:926 +0x1bc
database/sql.(*DB).query(0xc08405af00, 0x898230, 0x4f, 0x0, 0x0, ...)
        C:/Users/ADMINI~1/AppData/Local/Temp/2/makerelease250988475/go/src/pkg/database/sql/sql.go:913 +0xe0
database/sql.(*DB).Query(0xc08405af00, 0x898230, 0x4f, 0x0, 0x0, ...)        C:/Users/ADMINI~1/AppData/Local/Temp/2/makerelease250988475/go/src/pkg/database/sql/sql.go:899 +0x8e

But when i do

rows, err := conn.Query("SELECT f1, f2, f3, f4, f5, f6, f7, f8, f9, f10, f11, f12, f13 FROM TSTING3")

it works, but

rows, err := conn.Query("SELECT f1, f2, f3, f4, f5, f6, f7, f8, f9, f10, f11, f12, f13,f14 FROM TSTING3")

it fails again (problem not in f14, i try some combinations of select fields, its unpredictable)

Can you help?

Add support for case sensitive user names

nakagami/pyfirebirdsql#76

Large insert queries hang.

I'm executing an insert query that has large field.
Eg:

tx, err := db.Begin()
..
stmt, err := tx.Prepare(`INSERT INTO Test (TestField) VALUES ( ? )`)
..
// hangs on this call
res, err := stmt.Exec(largeText)
..

Where the value of largeText will be a text value > 1024 bytes.

This will cause my exec to hang. Looking into the firebirdsql code, it appears to hang in wireProtocol.opExecute, on the 'appendBytes' call for values.

The 'p.buf' it is only 1024 bytes. We are trying to append in more bytes then 'p.buf' allows. Thus in wireProtocol.appendBytes we will hang once p.bufCount equals 1024.

So it essentially comes down to:

func (p *wireProtocol) appendBytes(bs []byte) {
    for _, b := range bs {
        p.buf[p.bufCount] = b
        p.bufCount++
    }
}

where bs > len(p.buf)

I can modify the code to allow p.buf to expand itself... but I don't know what affects that would have on the sql protocol you are using. Any suggestions?

Transactions automatic commit/begin wrong

system begins automatic Transactions, even if an automatically created transaction is active.

1. connect to Database
   -> System starts automatically a Transaction (TRANS 1)
2. User explicitly starts Transaction(TRANS 2), do something and commit Transaction
   -> System starts automatically Transaction (TRANS 3)
3. User explicitl starts Transaction(Trans 4), do something and commit Transaction
   -> System starts a automatically Transaction (Trans 5)

Now there are three open Transactions (1 , 3, 5)

[Question] Specifying Transaction settings

Hi,
Is it possible to specify the transaction settings/isolation level?
Thanks

Blobs returning incorrectly

I'm actually unsure if this is a problem on my end or the packages.

When getting a blob via a query and trying to save it as a file, the file won't open. Printing the data as plain text shows that all characters are strangely encoded.

var mapa []byte
qRow := conn.QueryRow("select A_PICTURE from A_TABLE where ID = 1")
qRow.Scan(&mapa)
err := ioutil.WriteFile("picture.png", mapa, 0644)
if err != nil {
log.Fatal(err)
}

If I do this for a plain something.txt file, it works perfectly. Anything more complex than that results in a broken file.

The original intent behind this is to get a .sxc file from the database and parse it. File won't open. Tried using a .png file just to test it out with the same end result.

Not sure if this is an issue with the package or my method of saving the file doesn't work with the file types I'm working with. If it's on my end, apologies for submitting this.

Error Select

I get an error "conversion error from string "%s" with some SELECT queries.

query = `select
    ffc.id,
    ffc.AcceptDate,
    ffc.DocumentDate,
    ffc.Credit Summa,
    ffc.Note,
    s_psl.ANumber SourceBudgetPromiseNum,
    s_psl.Summa SourceBudgetPromiseSumma,
    s_a.DocNumber SourceBudgetPromiseAgrNumber,
    s_a.ReestrNumber SourceBudgetPromiseAgrReestrNum,
    s_a.AgreementDate SourceBudgetPromiseAgrDate,
    s_a.Summa SourceBudgetPromiseAgrSumma

  from
    FacialFinCaption          ffc
    inner join FacialFinDetail  ffd on (ffd.RecordIndex = ffc.ID)
     left join PaymentSchedule s_psl on
    (
      (ffd.SourceBudgetPromise = s_psl.InnerPromiseNum)
      and
      (ffd.SourcePromise = s_psl.ANumber)
    )    
     left join Agreements s_a on (s_psl.AgreementRef = s_a.ID)
  where
    (
        (ffc.ProgIndex = 63) AND (ffc.acceptdate is not null) AND (s_psl.anumber is not null)  AND (s_a.ProgIndex = 314)  AND
        (ffc.acceptdate <= '%s'  AND ffc.acceptdate >= '%s')
    );`
rows, err := fireConn.Query(query, today, fromDay)
	if err != nil {
		return nil, err
	}
defer rows.Close()
os := make([]*O, 0)
for rows.Next() {
//...
}
if err = rows.Err(); err != nil {
        //this error!!!!
	return(nil, err)
}

What could be the problem?
Thank you in advance!!!

Prepared statements (Insert, Update, Delete) are not commited on Exec()

I might have found an issue with prepared statements which gets not commited on Exec().
If I try to execute TestIssue7 of your driver_test.go file the code seems to work, but the newly inserted row is not committed. This is the code:

    conn, _ := sql.Open("firebirdsql_createdb", "sysdba:masterkey@localhost:3050/tmp/go_test_issue7.fdb")
    conn.Exec("CREATE TABLE test_issue7 (f1 varchar(2048))")
    defer conn.Close()

    stmt, _ := conn.Prepare("INSERT INTO test_issue7 values (?)")
    stmt.Exec(fmt.Sprintf("%2000d", 1))

After executing this code I use isql to connect to the database and submit a SELECT * FROM test_issue7; query. The result is empty. Is this an issue or expected behavior or should we use prepared statements only within an extra "wrapping" transaction like the following code snipped?

    conn, _ := sql.Open("firebirdsql_createdb", "sysdba:masterkey@localhost:3050/tmp/go_test_issue7.fdb")
    conn.Exec("CREATE TABLE test_issue7 (f1 varchar(2048))")
    defer conn.Close()

    tx, _ := conn.Begin()
    stmt, _ := tx.Prepare("INSERT INTO test_issue7 values (?)")
    stmt.Exec(fmt.Sprintf("%2000d", 1))
    tx.Commit()

Tests with golang drivers for MySQL or PostgreSQL shows, that a prepared statement is commited on Exec without the need of an extra transaction "wrapping" the prepared statement.

Error returning "null" on QueryRow (INSERT RETURNING)

After some testing I ended up with this situation: When I use "db.Exec" to insert boolean values in database, everything works just fine. But, if I use QueryRow (in this case, I am trying to return some data, so that's why I need QueryRow) it fails. The problem is that it only fails when I send "false". If I send "true", it works.

Ex:
db.Exec("insert into table(my_bool) values (false)") -> OK
db.Exec("insert into table(my_bool) values (true)") -> OK
db.QueryRow("insert into table(my_bool) values (true) returning id").scan(&id) -> OK
db.QueryRow("insert into table(my_bool) values (false) returning id").scan(&id) -> FAIL

I am using GO 1.9 and Firebird 3 btw.
Thanks :)

Error handling op_dummy in opAccept function

In:

for opcode == op_dummy {
	b, err = p.recvPackets(4)
}

Must be:

for opcode == op_dummy {
	b, _ = p.recvPackets(4)
	opcode := bytes_to_bint32(b)
}

Implicit support to boolean values

Currently Firebird doesn't support BOOLEAN data type. So, insert a boolean value return op_response error. Is a good idea to convert implicitly boolean values into int values when inserting data (other languages support this behaviour)

Exec: when using transaction - always commit(rollback not working)

Problem code??
file: statement.go
line 80: err = stmt.tx.Commit()

Using conn.Ping() on a DB with an invalid connection string doesn't return error

If I open a connection with a valid connection string but with wrong information (wrong password, wrong db name), it returns an error message as expect.

However, if I try to open a connection with a totally invalid connection string, for example conn, errConn := sql.Open("firebirdsql", "aaaaaaa") and then attempt to test it with errPing := conn.Ping(), the code breaks and I get a bunch of errors in the command line and everything after the Ping() gets ignored.

Log:

017/09/01 10:49:53 http: panic serving 192.168.1.73:63073: runtime error: invalid memory address or nil pointer dereference
goroutine 36 [running]:
net/http.(*conn).serve.func1(0xc420138140)
/usr/local/go/src/net/http/server.go:1721 +0xd0
panic(0x12c5020, 0x1777f00)
/usr/local/go/src/runtime/panic.go:489 +0x2cf
github.com/nakagami/firebirdsql.parseDSN(0x1318288, 0x7, 0xc420047578, 0x103264e, 0xc42014c000, 0x200000003, 0xc42014c000, 0xc420022600, 0xc4200475d8, 0x106f3a1, ...)
/Users/igorsousa/Sites/WorkingStash/go/src/github.com/nakagami/firebirdsql/utils.go:234 +0xb7
github.com/nakagami/firebirdsql.newFirebirdsqlConn(0x1318288, 0x7, 0x0, 0x200000000, 0x20140000)
/Users/igorsousa/Sites/WorkingStash/go/src/github.com/nakagami/firebirdsql/connection.go:103 +0x50
github.com/nakagami/firebirdsql.(*firebirdsqlDriver).Open(0x179e400, 0x1318288, 0x7, 0x0, 0xc420175b00, 0x28, 0x28)
/Users/igorsousa/Sites/WorkingStash/go/src/github.com/nakagami/firebirdsql/driver.go:34 +0x35
database/sql.(*DB).conn(0xc420138280, 0x1468c40, 0xc420072330, 0x1, 0x28, 0x900, 0x1781f60)
/usr/local/go/src/database/sql/sql.go:965 +0x146
database/sql.(*DB).PingContext(0xc420138280, 0x1468c40, 0xc420072330, 0x0, 0x0)
/usr/local/go/src/database/sql/sql.go:593 +0x60
database/sql.(*DB).Ping(0xc420138280, 0x1, 0x1)
/usr/local/go/src/database/sql/sql.go:615 +0x43
main.startConn(0x14684c0, 0xc4201460e0, 0x4, 0xc42013e1b0, 0xa)
/Users/igorsousa/Sites/WorkingStash/go/src/estado_documento/estadodocumento.go:110 +0x1cc
main.listarOpHandler(0x14684c0, 0xc4201460e0, 0xc420180000)
/Users/igorsousa/Sites/WorkingStash/go/src/estado_documento/estadodocumento.go:209 +0xca
net/http.HandlerFunc.ServeHTTP(0x1336500, 0x14684c0, 0xc4201460e0, 0xc420180000)
/usr/local/go/src/net/http/server.go:1942 +0x44
net/http.(*ServeMux).ServeHTTP(0x1781ca0, 0x14684c0, 0xc4201460e0, 0xc420180000)
/usr/local/go/src/net/http/server.go:2238 +0x130
net/http.serverHandler.ServeHTTP(0xc4200b42c0, 0x14684c0, 0xc4201460e0, 0xc420180000)
/usr/local/go/src/net/http/server.go:2568 +0x92
net/http.(*conn).serve(0xc420138140, 0x1468c00, 0xc420142280)
/usr/local/go/src/net/http/server.go:1825 +0x612
created by net/http.(*Server).Serve
/usr/local/go/src/net/http/server.go:2668 +0x2ce

Connection Pool

i try used this library and in my observation "Connection Pool" it does not work properly. I have errors when i use one open db i more go runntimes at the same time:

server  Fri Dec  8 00:26:53 2017
        SERVER/process_packet: broken port, server exiting


server  Fri Dec  8 00:26:53 2017
        INET/inet_error: read errno = 104


server  Fri Dec  8 00:26:53 2017
        INET/inet_error: read errno = 104


server  Fri Dec  8 00:26:53 2017
        SERVER/process_packet: broken port, server exiting


server  Fri Dec  8 00:26:53 2017
        SERVER/process_packet: broken port, server exiting


server  Fri Dec  8 00:26:53 2017
        SERVER/process_packet: broken port, server exiting


server  Fri Dec  8 00:26:54 2017
        SERVER/process_packet: broken port, server exiting


server  Fri Dec  8 00:50:19 2017
        INET/inet_error: read errno = 104

this help.

db.SetMaxOpenConns(1)
db.SetMaxIdleConns(1)

but to do web applications it will be a problem..

Multiple inserts with RETURNING statement fail

I've finally gotten to use the RETURNING statement, in order to get the last insert id, and I noticed that it fails when performing the insert more than once. The code is the same as described in https://github.com/nakagami/firebirdsql/blob/master/driver_test.go#L126

That test passes, because it is performed only once. The test fails as soon as the insert is repeated:

func TestMultiReturning(t *testing.T) {
    conn, _ := sql.Open("firebirdsql_createdb", "SYSDBA:masterkey@localhost:3050/tmp/go_test_returning.fdb")
    defer conn.Close()

    conn.Exec(`
        CREATE TABLE test_returning (
            f1 integer NOT NULL,
            f2 integer default 2,
            f3 varchar(20) default 'abc')`)

    // Repeat insert. This will throw an error.
    for i := 0; i < 2; i++ {
        rows, err := conn.Query("INSERT INTO test_returning (f1) values (1) returning f2, f3")
        if err != nil {
            t.Fatalf("Error Insert returning : %v", err)
        }

        var f2 int
        var f3 string
        rows.Next()
        rows.Scan(&f2, &f3)
        if f2 != 2 || f3 != "abc" {
            t.Fatalf("Bad value insert returning: %v,%v", f2, f3)
        }
    }
}

The error it returns is:

driver_test.go:129: Error Insert returning : Dynamic SQL Error
    SQL error code = -204
    Table unknown
    TEST_RETURNING
    At line 1, column 13

For some reason, the first INSERT causes the table to be deleted.
I checked this by performing a new SELECT afterwards and it too fails with the same error.

DSQL problems

Hi,
There are some problems with DSQL statements like:

Insert into TableName (Name, Phone) values ("Everton", "123 123123") returning ID

Creating or connecting to databases with non-UTF8 character sets

It's not always desired to create databases or to connect to databases with character set UTF8, which is what is currently hard-coded. I would be happy to submit a patch, but the DSN format would need to be extended. The DSN used by the postgresql library lib/pq appears to use space-separated key=value pairs. My go-fb library uses semicolon-separated key=value pairs. I don't mind what schema is used as long as it's extensible. Your library, your call...

Autoreconnect

Hi there!

Is it possible to implement autoreconnection?

When my database disconnect/restart I get error "Error op_response:0" in the next query after it comes online again (app still running and not reloaded inbetween).

This works in lib/pq and as far as I know should be the standard behavior according to https://golang.org/pkg/database/sql/#Open:

The returned DB is safe for concurrent use by multiple goroutines and maintains its own pool of idle connections. Thus, the Open function should be called just once. It is rarely necessary to close a DB.

Thanks in advance, this is a great driver!

driver is 4X time slower compared to C++ and Perl on windows 10 platform, Firebird 2.5

I performed some tests and found out that fetching rows is very slow compared to using C++ or even Perl. The problem is at the wireprotocol.go module - seems that every Write or Read from the socket goes through a operating system syscall creating huge overhead as there are many small (4 bytes) read operations.
I am not familiar with tcp in go and the Firebird protocol, but is it possible to improve the way of communicating with the server ?
In my specific test fetching ~2000 rows that takes 50-70ms in C++ (using IBPP driver) or Perl , took 230-240ms in Go. Even when I limit to 40 records there is a huge penalty in Go.

if there is such a penalty in the tcp then all the internet staff etc would be very slow, but I did not found complains about slow communication, so maybe there is a way around this problem ?

Add available parateter 'auth_method'

auth_plugin_name=Srp
or
auth_plugin_name=Legacy_Auth

wire_crypt=true
or
wire_crypt=false

[question] Support for FB 1.5?

I'm entertaining the idea of writing an integration solution in Go and am wondering how difficult it might be to get this going on an older FB version (1.5). In your opinion, would it be an unreasonable amount of effort? What do you think would be involved?

sql: Scan error on column index X: unsupported Scan, storing driver.Value type time.Time into type *sql.RawBytes

The application I'm developing gets a list of queries from a file, adds the query string to a map and runs the actually when needed. This means that the columns that the query will scan into are unknown before running the query.

To solve this, I've this a gist I've found here (I use the map version). If the query doesn't return a column of type Time, it works perfectly. If it does contain a Time column, I get the error mentioned in the title.

This seems to be a common issue with golang's sql implementation. Most users seem to fix this by adding ?parseTime=true at the end of their connection string. This seems to do nothing for me.

Rows count is constant?

Here a code

rows, err := conn.Query("SELECT * FROM TABLE01")
i := 0
for rows.Next() {
    i++
}
log.Println("Row count: ", i)
rows.Close()

Rows is always 400 (but in table more 3000 rows). It bug or feature?