PrepareContext: when does it re-prepare?

[Tamás Gulácsi]

If ResetSession releases the underlying Oracle session, and re-acquires it, then the prepared statement's first execution

    db.SetMaxIdleConns(1)
    db.SetMaxOpenConns(3)
    ctx, cancel := context.WithCancel(testContext("OpenClose"))
    defer cancel()
    const module = "godror.v2.test-OpenClose "
    const countQry = "SELECT COUNT(0) FROM v$session WHERE module LIKE '" + module + "%'"
    stmt, err := db.PrepareContext(ctx, countQry)
    if err != nil {
        t.Fatal(err)
    }
    defer stmt.Close()

    sessCount := func() (int, error) {
        var n int
        qErr := stmt.QueryRowContext(ctx).Scan(&n)
        return n, qErr
    }
    n, err := sessCount()     // <--- here panics in NumInput

panics in NumInput:

$ go version
go version go1.14.2 linux/amd64
$ go test -parallel=4 -run=OpenClose
Client: 19.3.0.0.0 Timezone: Local
Server: 11.2.0.4.0 [Oracle Database 11g Release 11.2.0.4.0 - 64bit Production] Timezone: Local
--- FAIL: TestOpenClose (0.14s)
    z_test.go:1164: oracle://bruno:SECRET-MsTEvpLDUq8=@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=cig_tst.unosoft.local)))?connectionClass=POOLED&enableEvents=1&heterogeneousPool=0&poolIncrement=0&poolMaxSessions=5&poolMinSessions=1&poolSessionMaxLifetime=5m0s&poolSessionTimeout=10s&poolWaitTimeout=5s&prelim=0&standaloneConnection=0&sysasm=0&sysdba=0&sysoper=0&timezone=local
panic: ORA-00000: DPI-1039: statement was already closed [recovered]
        panic: ORA-00000: DPI-1039: statement was already closed

goroutine 19 [running]:
testing.tRunner.func1.1(0x741460, 0xc000090900)
        /usr/local/go/src/testing/testing.go:940 +0x2f5
testing.tRunner.func1(0xc00009e360)
        /usr/local/go/src/testing/testing.go:943 +0x3f9
panic(0x741460, 0xc000090900)
        /usr/local/go/src/runtime/panic.go:969 +0x166
github.com/godror/godror.(*statement).NumInput(0xc0000ae3c0, 0x0)
        /home/tgulacsi/src/github.com/godror/godror/stmt.go:622 +0x407
database/sql.driverArgsConnLocked(0x7e9e20, 0xc0000e2000, 0xc000098940, 0x0, 0x0, 0x0, 0xc000098940, 0xc000185978, 0x56647c, 0x0, ...)
        /usr/local/go/src/database/sql/convert.go:119 +0x890
database/sql.rowsiFromStatement(0x7eb7a0, 0xc000098740, 0x7e9e20, 0xc0000e2000, 0xc000098940, 0x0, 0x0, 0x0, 0x0, 0x0, ...)
        /usr/local/go/src/database/sql/sql.go:2600 +0xc8
database/sql.(*Stmt).QueryContext(0xc0000921b0, 0x7eb7a0, 0xc000098740, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0)
        /usr/local/go/src/database/sql/sql.go:2556 +0x210
database/sql.(*Stmt).QueryRowContext(0xc0000921b0, 0x7eb7a0, 0xc000098740, 0x0, 0x0, 0x0, 0x0)
        /usr/local/go/src/database/sql/sql.go:2614 +0x6d
github.com/godror/godror_test.TestOpenClose.func2(0xc0000a40c0, 0x7eb7a0, 0xc000098740)
        /home/tgulacsi/src/github.com/godror/godror/z_test.go:1191 +0x82
github.com/godror/godror_test.TestOpenClose(0xc00009e360)
        /home/tgulacsi/src/github.com/godror/godror/z_test.go:1194 +0x530
testing.tRunner(0xc00009e360, 0x7834c8)
        /usr/local/go/src/testing/testing.go:991 +0xdc
created by testing.(*T).Run
        /usr/local/go/src/testing/testing.go:1042 +0x357
exit status 2
FAIL    github.com/godror/godror        1.866s

 With Go tip:

! gotip version
go version devel +ac211c0 Tue Apr 28 02:36:39 2020 +0000 linux/amd64
:tgulacsi@tgulacsi-dell: ~/src/github.com/godror/godror
$ gotip test -parallel=4 -run=OpenClose
Client: 19.3.0.0.0 Timezone: Local
Server: 11.2.0.4.0 [Oracle Database 11g Release 11.2.0.4.0 - 64bit Production] Timezone: Local
--- FAIL: TestOpenClose (0.22s)
    z_test.go:1164: oracle://bruno:SECRET-MsTEvpLDUq8=@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=cig_tst.unosoft.local)))?connectionClass=POOLED&enableEvents=1&heterogeneousPool=0&poolIncrement=0&poolMaxSessions=5&poolMinSessions=1&poolSessionMaxLifetime=5m0s&poolSessionTimeout=10s&poolWaitTimeout=5s&prelim=0&standaloneConnection=0&sysasm=0&sysdba=0&sysoper=0&timezone=local
panic: ORA-00000: DPI-1039: statement was already closed [recovered]
        panic: ORA-00000: DPI-1039: statement was already closed

goroutine 19 [running]:
testing.tRunner.func1.1(0x7411a0, 0xc000202720)
        /home/tgulacsi/sdk/gotip/src/testing/testing.go:984 +0x2e0
testing.tRunner.func1(0xc00021c3c0)
        /home/tgulacsi/sdk/gotip/src/testing/testing.go:987 +0x3f9
panic(0x7411a0, 0xc000202720)
        /home/tgulacsi/sdk/gotip/src/runtime/panic.go:969 +0x166
github.com/godror/godror.(*statement).NumInput(0xc00021c640, 0x0)
        /home/tgulacsi/src/github.com/godror/godror/stmt.go:622 +0x3fe
database/sql.driverArgsConnLocked(0x7ed3c0, 0xc000262000, 0xc000208940, 0x0, 0x0, 0x0, 0xc000208940, 0xc000183978, 0x569d4c, 0x0, ...)
        /home/tgulacsi/sdk/gotip/src/database/sql/convert.go:119 +0x85e
database/sql.rowsiFromStatement(0x7eec40, 0xc000208740, 0x7ed3c0, 0xc000262000, 0xc000208940, 0x0, 0x0, 0x0, 0x0, 0x0, ...)
        /home/tgulacsi/sdk/gotip/src/database/sql/sql.go:2684 +0xc8
database/sql.(*Stmt).QueryContext(0xc0002003f0, 0x7eec40, 0xc000208740, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0)
        /home/tgulacsi/sdk/gotip/src/database/sql/sql.go:2640 +0x20e
database/sql.(*Stmt).QueryRowContext(0xc0002003f0, 0x7eec40, 0xc000208740, 0x0, 0x0, 0x0, 0x0)
        /home/tgulacsi/sdk/gotip/src/database/sql/sql.go:2698 +0x6d
github.com/godror/godror_test.TestOpenClose.func2(0xc000218820, 0x7eec40, 0xc000208740)
        /home/tgulacsi/src/github.com/godror/godror/z_test.go:1191 +0x82
github.com/godror/godror_test.TestOpenClose(0xc00021c3c0)
        /home/tgulacsi/src/github.com/godror/godror/z_test.go:1194 +0x530
testing.tRunner(0xc00021c3c0, 0x783e38)
        /home/tgulacsi/sdk/gotip/src/testing/testing.go:1035 +0xdc
created by testing.(*T).Run
        /home/tgulacsi/sdk/gotip/src/testing/testing.go:1086 +0x355
exit status 2
FAIL    github.com/godror/godror        1.304s

Should I report this? Or I have wrong assumptions?

This is only when I use *sql.DB.Prepare, not when *sql.Conn.Prepare or *sql.Tx.Prepare.

Thanks,

Tamás

[Daniel Theophanes]

Ah, database/sql "stmts", my least favorite part. I may have misunderstood your question, so let me know if I'm missing something.

In database/sql, a Stmt doesn't actually store an individual prepared statement reference. It stores a Stmt Pool of references. The Tx Prepare and Conn Prepare use the same conn that you already have. So probably no sweat. Oh, also we don't actually put the driver.Conn back in the pool for Tx or Conn, so it doesn't get reset (that would defeat the point in both cases).  Does reset session (however you implement it) also close any prepared statement on the connection? 

--
You received this message because you are subscribed to the Google Groups "golang-sql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to golang-sql+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/golang-sql/301e22d2-bf7a-4526-b599-e82a6289a965%40googlegroups.com.

[Tamás Gulácsi]

2020. április 28., kedd 8:16:19 UTC+2 időpontban Daniel Theophanes a következőt írta:

Ah, database/sql "stmts", my least favorite part. I may have misunderstood your question, so let me know if I'm missing something.

In database/sql, a Stmt doesn't actually store an individual prepared statement reference. It stores a Stmt Pool of references. The Tx Prepare and Conn Prepare use the same conn that you already have. So probably no sweat. Oh, also we don't actually put the driver.Conn back in the pool for Tx or Conn, so it doesn't get reset (that would defeat the point in both cases).  Does reset session (however you implement it) also close any prepared statement on the connection? 

The ResetSession now is implemented as a "release back to the session pool" and "acquire a new session from the pool",

but that effectively is a close and open.

So yes, it does close all prepared statements.

Ah, now I see: I bet database/sql re-prepares the statement iff the *driverConn has changed.

But ATM it does not change, "only" the *dpiConn two level below...

Can the driver somehow get know the fact that there's a prepared statement on that connection?

When I implement driver.ExecerContext (to differentiate with PrepareContext, assuming if ExecerContext is implemented,

then PrepareContext will be called only when the user calls it), it fails when given an sql.Out parameter with

"sql: converting argument $1 type: unsupported type sql.Out, a struct".

What am I doing wrong?

[Daniel Theophanes]

There isn't a way to list the prepared statements that are within the pool, from the driver. You would need to keep track of the prepared statements within the your driver with every prepare you see.

To handle sql.Out, You need to ensure you have implemented the NamedValueChecker, see mssql: https://github.com/denisenkom/go-mssqldb/blob/master/mssql_go19.go#L68

--

You received this message because you are subscribed to the Google Groups "golang-sql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to golang-sql+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/golang-sql/1020e923-379f-4e2b-bb30-56e9789e2dcc%40googlegroups.com.

[Tamás Gulácsi]

2020. április 28., kedd 15:54:41 UTC+2 időpontban Daniel Theophanes a következőt írta:

There isn't a way to list the prepared statements that are within the pool, from the driver. You would need to keep track of the prepared statements within the your driver with every prepare you see.

To handle sql.Out, You need to ensure you have implemented the NamedValueChecker, see mssql: https://github.com/denisenkom/go-mssqldb/blob/master/mssql_go19.go#L68

Sorry I don't understand.

1. I do implement Stmt.CheckNamedValue - it does nothing, just filters out unwanted (option) arguments.

2. That mssql code leaves sql.Out as sql.Out, changes only the its Dest field.

3. If I don't implement Conn.ExecContext, only Stmt.ExecContext, (and Conn.PrepareContext) than everything works (I just can't track which statement is prepared and which is executed).

What changes when I implement Conn.ExecContext?

[Daniel Theophanes]

1. Send me a pointer to the code in question, I can take a look at it.

2. Fair.

3. If you don't implement ExecContext, when it gets an Exec query, if first calls Prepare, then it executes the statement. Could you point me to the code in question? Maybe reading it would help me better understand.

--

You received this message because you are subscribed to the Google Groups "golang-sql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to golang-sql+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/golang-sql/09aba011-79ac-4f09-a4c7-176e78da0981%40googlegroups.com.

[Tamás Gulácsi]

> On April 28, 2020 4:54 PM Daniel Theophanes <kard...@gmail.com> wrote:
>
>
> 1. Send me a pointer to the code in question, I can take a look at it.
> 2. Fair.
> 3. If you don't implement ExecContext, when it gets an Exec query, if first calls Prepare, then it executes the statement. Could you point me to the code in question? Maybe reading it would help me better understand.

Conn.ExecerContext:
https://github.com/godror/godror/blob/resetsession/conn.go#L128

Call that fails:
https://github.com/godror/godror/blob/resetsession/orahlp.go#L530

For example in this test:
https://github.com/godror/godror/blob/resetsession/z_qrcn_test.go#L36

Like:
! gotip test -run=QRCN
Using default database for tests: /home/tgulacsi/src/github.com/godror/godror/contrib/free.db/env.sh
export TNS_ADMIN=/home/tgulacsi/src/github.com/godror/godror/contrib/free.db
export GODROR_TEST_USERNAME=test
export GODROR_TEST_DB=free_high
export GODROR_TEST_STANDALONE=1
Client: 19.3.0.0.0 Timezone: Local
Server: 18.4.0.0.0 [Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production; Version 18.4.0.0.0] Timezone: +00:00
--- FAIL: TestQRCN (0.48s)
z_qrcn_test.go:38: getConnection: sql: converting argument $1 type: unsupported type sql.Out, a struct
FAIL
exit status 1

Thanks in advance,
Tamás Gulácsi

PS. When I de-implement ExecerContext/QueryerContext (rename the methods), this works!