database/sql: sometimes QueryRow is a bad choice

[Tamás Gulácsi]

Hi,

TL;DR;
QueryRow assumes there will be only one row, and closes the underlying Connection right after Scan.
So don't be lazy, and use Query, and the returned Rows if you need to use the database connection after Scan.

I wanted to share this hard learned lesson suffered during debugging https://github.com/tgulacsi/goracle/issues/26.

Maybe this (underlying connection may be closed after Scan) could be included in the documentation of QueryRow!


Cheers,
Tamás Gulácsi

[ma...@joh.to]

On Friday, February 6, 2015 at 7:48:07 PM UTC+1, Tamás Gulácsi wrote:

QueryRow assumes there will be only one row, and closes the underlying Connection right after Scan.
So don't be lazy, and use Query, and the returned Rows if you need to use the database connection after Scan.

 

This advice seems confused.  The connection is not closed; it's merely returned back to the pool.  But the more important point is that that shouldn't matter.
 

I wanted to share this hard learned lesson suffered during debugging https://github.com/tgulacsi/goracle/issues/26.

Looks like oracle.ExternalLobVar is abusing the interface provided by database/sql.  In this case I think using Query is safe (though don't quote me on that since I didn't spend too much time thinking about it), but it's still an ugly workaround.

QueryRow is fine, even though it might not work with all types of abuse the database/sql API can take.

.m

[Tamás Gulácsi]

2015. február 6., péntek 22:40:19 UTC+1 időpontban ma...@joh.to a következőt írta:

On Friday, February 6, 2015 at 7:48:07 PM UTC+1, Tamás Gulácsi wrote:

QueryRow assumes there will be only one row, and closes the underlying Connection right after Scan.
So don't be lazy, and use Query, and the returned Rows if you need to use the database connection after Scan.

 

This advice seems confused.  The connection is not closed; it's merely returned back to the pool.  But the more important point is that that shouldn't matter.

You're right, the connection is returned to the pool, but that may result in a close, depending on MaxIdleConns/MaxOpenConns.

 

I wanted to share this hard learned lesson suffered during debugging https://github.com/tgulacsi/goracle/issues/26.

Looks like oracle.ExternalLobVar is abusing the interface provided by database/sql.  In this case I think using Query is safe (though don't quote me on that since I didn't spend too much time thinking about it), but it's still an ugly workaround.

Where is the abuse? That a SELECT returns something which needs the connection, and you can modify the database through it? (SELECT clobCol from clobs where id=1 FOR UPDATE;)
Yes, in this case it is an abuse. But that abuse is on Oracle's side.

QueryRow is fine, even though it might not work with all types of abuse the database/sql API can take.

.m

Maybe this is the same as "SELECT CURSOR(select * from all_objects) from dual" - a select returns a resultset, in which a column is a resultset - this is not what database/sql is capable of, but absolutely legal and far from an abuse.