SQL (MySQL) reading a stored procedure

[Bryan Jarvis]

I have (several) stored procedures that check for existence of a phone number in a table.
Using a regular mySQL client, the syntax is call is_wireless(aaa, eee, nnnn) where aaa is the area code, eee is the exchange and nnnn is then number.
Disclaimer: I'm strictly concerned with US telephone numbers here.

when I do a *db.Query, I get a valid return of 1 or 0 for the first record in my data file.  However,
the second record dies with a [reply is not completely read] error.

Here is my function I'm calling (although I don't believe it makes a difference if my code is in a function or inline):

//should return 1 if found, 0 if not
func scrub_wireless(con *sql.DB, ac int64, exch int64, num int64) string {
    var found string

    fmt.Println("testing -->",ac," ",exch," ",num)

    rows, err := con.Query("call is_wireless(?,?,?)",ac,exch,num)
    if err != nil {
      fmt.Fprintf(os.Stderr, "scrub_wireless Query failed. [%s]\r\n", err)
        os.Exit(1)
    }
    defer rows.Close()
  fmt.Println(rows.Columns())
    for rows.Next() {
            if err := rows.Scan(&found); err != nil {
                 fmt.Fprintf(os.Stderr, "scrub_wireless Query failed. [%s]\r\n", err)
                  os.Exit(1)
            }
     fmt.Printf("found is %s %t \n\r", found, found)
    }
  rows.Close()
    return found
}

There is a fair amount of debug code in the above as I've tried to figure out what is going on.

The stored procedure returns on column, named "found" and that value is either 1 or 0 depending on
whether I have a row in the table that matches.
Also, I can set my "found" variable to be either string or int64 (and modify the return type accordingly) and
I get the same results.

I also tried *db.QueryRow (tried that first, since I only expect one row) with the same results.

I'm suspecting a bug in the driver "github.com/ziutek/mymysql/godrv" but obviously I can't point fingers
since I'm so new at this.

Can anyone help me figure out what is going on here? 
If I have to, I can just rewrite the stored procedure as a prepared statement query, but I'd rather use the
stored procedure if I can.

Thanks in advance!
Bryan

[Harald Weidner]

Hello,

On Mon, Nov 10, 2014 at 02:24:44PM -0800, Bryan Jarvis wrote:

> when I do a *db.Query, I get a valid return of 1 or 0 for the first record
> in my data file. However,
> the second record dies with a [reply is not completely read] error.

rows.Close() returns an error. You should check it before returning.
You can (you should!) also explicitely call rows.Err(), which returns an
error if something went wrong.

NB, your code closes rows up to three times: by explicit call of
rows.Close(), by the defer statement and implicitely at the end
of the rows.Next() loop.

> I'm suspecting a bug in the driver "github.com/ziutek/mymysql/godrv" but
> obviously I can't point fingers since I'm so new at this.

If can verify/falsify this by checking an other driver. E.g.
github.com/go-sql-driver/mysql is a good choice.

Regards,
Harald

[Tim O'Brien]

It seems your stored procedure returns a result set. If this is the case then the driver needs to support an optional part of the MySQL protocol called client-multi-results. I've added this feature to this a MySQL driver here:

https://github.com/timob/go-mysql

Just add the "client-multi-results" parameter to your connection DSN.

Tim

[Bryan Jarvis]

Thank you for your insight, Harald -
A lot of the closes and such were added to see if they made a difference (which they did not)
And I was really trying to use *db.QueryRow for my finished product as opposed to *dbQuery
and I had just substituted one for the other to see if it made a difference (which it did not)
The solution provided below by Tim, however, did work!
I am cleaning up my code back to what is (hopefully) acceptable GO idiomatic code.

[Bryan Jarvis]

Thanks, Tim!
I changed my code to your driver and added the client-multi-results parameter and voila!, success!
Now I'm on to trying to write a version that can use concurrency (I have a dozen or more different queries
where I'm looking to see if a phone number exists in those different tables on one or more mySQL servers.)
If I can figure out a way to overlap those queries, hopefully my code will run faster.