CODEWITHSUNDEEP
sqldatabasego
Jack
Jack

Reputation: 11

Determine whether to use DB.Exec or DB.Query from unknown query string

Using "database/sql", sql.DB.Exec() is used for queries that do not return rows (insert, delete, update) and sql.DB.Query() for queries that return rows (selects). Assume you have an incoming query string which you wish to execute, however you do not know whether the query intends to return rows. Can you think of a way to find out whether to use Exec or Query?

Upvotes: 1

Views: 2789

Answers (2)

Alex Yu
Alex Yu

Reputation: 3537

Your question is not that simple as it may seem from the first sight.

Let's clarify the problem:

Question is not about particular database as pgsql/mysql/sqlite/other but for any generic database drive.

So the answer must be in database/sql/driver.

Let's go further:

sql/driver. Interfaces: Queryer/QueryerContext & Execer/ExecerContext

Module sql/driver defines interfaces:

  • Queryer/QueryerContext - for ability to (*DB).Query
  • Execer/ExecerContext - for (*DB).Exec
  • others such as Pinger

Note that every interface described in documentation as " an optional interface that may be implemented by a Conn"

Which means that it's possible to implement sql driver without Query and/or Exec.

Experimental verification

Let's try to implement correct database driver that cannot Query and Exec: https://play.golang.org/p/sZiigEghphE

package main

import (
    "database/sql"
    "database/sql/driver"
    "log"
)

type expdrv struct{}
type expconn struct{}

// sql.Driver implementation
func (*expdrv) Open(name string) (driver.Conn, error) {
    return &expconn{}, nil
}

// driver.Conn implementation
func (c *expconn) Prepare(query string) (driver.Stmt, error) {
    return nil, nil
}

func (c *expconn) Close() error {
    return nil
}

func (c *expconn) Begin() (driver.Tx, error) {
    return nil, nil
}

func main() {
    sql.Register("drvexp", &expdrv{})
    log.Printf("Registred drivers: %v\n", sql.Drivers())

    db, err := sql.Open("drvexp", "")
    log.Printf("sql.Open() success: %v, error: %v", db != nil, err)

    log.Println("db.Close() error:", db.Close())
}

2009/11/10 23:00:00 Registred drivers: [drvexp]
2009/11/10 23:00:00 sql.Open() success: true, error: 
2009/11/10 23:00:00 db.Ping()  success: true
2009/11/10 23:00:00 db.Close() success: true
2009/11/10 23:00:00 db.Ping()  result:  sql: database is closed

It works!

This Driver implementation is:

  • correct from database/sql standpoint
  • has no working methods Query and Exec (attempt will throw panic)
  • surprisingly working Ping()

Possibility to implement Exec or Query independently of each other

Let's add Exec:

// driver.Result & driver.Execer
type expresult struct{}

func (*expconn) Exec(query string, args []driver.Value) (driver.Result, error) {
    return &expresult{}, nil
}

func (*expresult) LastInsertId() (int64, error) {
    return 0, nil
}

func (*expresult) RowsAffected() (int64, error) {
    return 0, nil
}

will allow:

_, err = db.Exec("BYE")
fmt.Println("db.Exec() success:", err == nil) // Outputs: true

Conclusion

  • Ability to call Query/QueryContext and/or Exec/ExecContext are optional

  • Behaviour of those methods can vary at great length between drivers

  • In general case there is no correct answer exists

  • For particular cases (with specific driver):

    • it's possible to suppose that db.Query would work for most cases
    • but it's impossible to guarantee that Query will really work without experimental verification

Thank you for your attention!

I suppose that's the final answer.

Upvotes: 0

vincent163
vincent163

Reputation: 398

In any cases, don't try to parse SQL since it's hacky. Just use Query if you are not sure, Query should be able to handle C_UD well and return an empty result set in that case. Both Query and Exec should have exactly the same effect, and they only differ in their response. Only use Exec if the user of your API explicitly does not need the results.

Upvotes: 3

Related Questions