mysql

[Jeremy Villalobos]

Hello:

I am working on a small project to learn more about go.  Can someone point me to a good basic mysql tutorial.  Do I need extra libraries ?  Or is everything included in go 1 ?

I get confused by google's answers to these questions.  There seems to be about 5 open source mysql connectors, I am not sure which one is the mainstream ( the one "everybody" is using.  

Thanks for the help

[Kyle Lemons]

I recommend database/sql with one of the drivers known to work with it: https://github.com/bradfitz/go-sql-test

[Brad Fitzpatrick]

I don't think a good "tutorial" exists, but I use and have tested this driver:

https://github.com/ziutek/mymysql/tree/master/godrv

There's another new one lately, but I can't vouch for it yet.

You just empty import it (import _ "github.com/ziutek/mymysql/godrv") and then import "database/sql" and:

db, err := sql.Open("mymysql", dbname+"/"+user+"/"+password)

[Julien Schmidt]

I would appreciate if you would try my driver (which i guess is the new one which Bard meant): http://code.google.com/p/go-mysql-driver/

It is currently in beta since it is out just for a few days and not used in production much.

But it is well tested and until now i had neither a single error with the current beta release (i use it in productuion) nor were issues reported.

In contrast to mymysql its not a wrapper around a stand-alone MySQL library, it is a native pure Go driver implementation for the database/sql package.

I guess in some cases its even more stable than mymysql, since mymysql does not handle all input types and mysql packet types.

But in general there is not much difference in usage between all the single drivers, thats what the database/sql package is for. You don't need to bother with the driver (besides choosing one).

PS: Brad, don't forget to give me write access to the community wiki ;)

[DisposaBoy]

Are you sure MyMySQL is a wrapper?

[Julien Schmidt]

Its not a wrapper around a C-Library, if this is what you meant. MyMySQL was written before the database/sql interface (resp. exp/sql) was out. Later an Adapter (maybe that term suits better) was added, the godrv sub-package.

[Brad Fitzpatrick]

Done. Just gave you wiki edit access.

Could you send me a github pull request (or just a patch) to add support for testing your driver to go-sql-test?

[Jeremy Villalobos]

I used your connector thanks.  I posted a short tutorial at 

http://goseeds.blogspot.com/2012/05/google-go-and-mysql-tutorial.html

[Julien Schmidt]

At "I'll skip setting up Google Go since (1) make a job at that." (1) points to the wrong site. 

Besides that, your tutorial looks really good! I guess it will help beginners a lot since neither the go documentation nor my package documentation has examples right now :)

@Brad: I'm really busy with project work right now but i will make a pull request ASAP ;-)

[T.J. Yang]

Any pointer why I am getting " undefined: driver.ErrBadConn" error message ?

I updated to go weekly to the latest.

Thanks

weekly@inspiron531:~/gocode/src$ go get -x code.google.com/p/go-mysql-driver/mysql

WORK=/tmp/go-build128267085

mkdir -p $WORK/code.google.com/p/go-mysql-driver/mysql/_obj/

cd /home/weekly/gocode/src/code.google.com/p/go-mysql-driver/mysql

/home/weekly/go/pkg/tool/linux_386/8g -o $WORK/code.google.com/p/go-mysql-driver/mysql/_obj/_go_.8 -p code.google.com/p/go-mysql-driver/mysql -D _/home/weekly/gocode/src/code.google.com/p/go-mysql-driver/mysql -I $WORK ./connection.go ./const.go ./driver.go ./packets.go ./result.go ./rows.go ./statement.go ./transaction.go ./utils.go

# code.google.com/p/go-mysql-driver/mysql

code.google.com/p/go-mysql-driver/mysql/packets.go:52: undefined: driver.ErrBadConn

code.google.com/p/go-mysql-driver/mysql/packets.go:79: undefined: driver.ErrBadConn

code.google.com/p/go-mysql-driver/mysql/packets.go:94: undefined: driver.ErrBadConn

code.google.com/p/go-mysql-driver/mysql/packets.go:416: undefined: driver.ErrBadConn

weekly@inspiron531:~/gocode/src$

[Julien Schmidt]

The latest weekly release is from 2012-03-27, right?

You should try Go1.0.1 which is newer ;)

[T.J. Yang]

On Sunday, May 13, 2012 8:13:09 AM UTC-5, Julien Schmidt wrote:

The latest weekly release is from 2012-03-27, right?

You should try Go1.0.1 which is newer ;)

Thanks for the pointer. I was assuming  "lastest weekly" = release + weeks after the release.

tj 

[Aaron Groves]

Do stored procedures work using this driver? I keep getting:

Error 1312: Procedure my_db.procedure can't return a result set in the given context

Cheers

[T.J. Yang]

I was able to finish the tutorial example after I use go 1.0.1. on Windows 7.

But I run into another bug and I filed the issue report at 
http://code.google.com/p/go-mysql-driver/issues/detail?id=1&thanks=1&ts=1336999619 

tj

[Julien Schmidt]

Thanks for the bug report. I will inspect the issue later today.

@Aaron: You've got me there.. i didn't test stored procedures until now. I'll inspect that ASAP, too. Do you have any example code?

[ziutek]

On 14 Maj, 18:25, Julien Schmidt <g...@julienschmidt.com> wrote:
> Thanks for the bug report. I will inspect the issue later today.
>
> @Aaron: You've got me there.. i didn't test stored procedures until now.
> I'll inspect that ASAP, too. Do you have any example code?

Stored procedure can return more than one result set if it contains
more than one select statement (number of result sets can be unknown
if procedure contain selects in loops). The database/sql interface
allows only one result set from query.

mymysql handles stored procedures this way:
https://github.com/ziutek/mymysql#example-8---use-stored-procedures

But what we have to do in driver? Return the first result, last
result, error?

[ziutek]

On 9 Maj, 15:26, Julien Schmidt <g...@julienschmidt.com> wrote:
> I guess in some cases its even more stable than mymysql, since mymysql does
> not handle all input types and mysql packet types.

mymysql handles all types since v4.6a. There were three unhandled
types before v4.2.3 and two before v4.6a (there were forgotten TODO
for them in type handling code).

Can you write more details about unhandled packet types you found in
mymysql?

[Julien Schmidt]

Last time i took a look at your source was definitely before v4.6a. Do you handle time values (both as driver.Value and as packet) well?

[ziutek]

On 14 Maj, 22:39, ziutek <ziu...@Lnet.pl> wrote:
> But what we have to do in driver? Return the first result, last
> result, error?

In fact, without buffering the whole response from the server, we can
only return first result set and discard remaining.

[ziutek]

On 14 Maj, 23:42, Julien Schmidt <g...@julienschmidt.com> wrote:
> Last time i took a look at your source was definitely before v4.6a. Do you
> handle time values (both as driver.Value and as packet) well?

There are no such things as date/time packets in MySQL protocol.
Single Row Data Packet contains whole row. There are many types of
packets in MySQL protocol but any of them doesn't correspond to
specific type (with Long Data Packet as small exception).

There are handlers for four date/time formats in mymysql (for six
MySQL date/time protocol types)::

case MYSQL_TYPE_DATE, MYSQL_TYPE_NEWDATE:
row[ii] = readDate(pr)

case MYSQL_TYPE_DATETIME, MYSQL_TYPE_TIMESTAMP:
row[ii] = readTime(pr)

case MYSQL_TYPE_TIME:
row[ii] = readDuration(pr)

case MYSQL_TYPE_SHORT, MYSQL_TYPE_YEAR:
if unsigned {
row[ii] = readU16(pr)
} else {
row[ii] = int16(readU16(pr))
}

godrv handles time types since mymysql v0.4.2.

[Julien Schmidt]

On Monday, May 14, 2012 2:50:25 PM UTC+2, T.J. Yang wrote:

I was able to finish the tutorial example after I use go 1.0.1. on Windows 7.

But I run into another bug and I filed the issue report at 
http://code.google.com/p/go-mysql-driver/issues/detail?id=1&thanks=1&ts=1336999619 

 

I did exactly what you described in the issue report, but for me it worked without any problems.

Please see my comment on the issue report ;)

On Tuesday, May 15, 2012 11:15:38 AM UTC+2, ziutek wrote:

There are no such things as date/time packets in MySQL protocol.
Single Row Data Packet contains whole row. There are many types of
packets in MySQL protocol but any of them doesn't correspond to
specific type (with Long Data Packet as small exception).

There are handlers for four date/time formats in mymysql (for six
MySQL date/time protocol types)::

From now on I'll call it "segments of a result packet or binary result packet representing a column value of a specific type". 

[Sammi]

Just get the latest go-mysql-server driver, procedure code not work.

product.go

package api

import (

        "strconv"

)

func GetProduct(id int64) (code, name, category, introduction, description, features, specification string, err error) {

        db, err := OpenDB()

        if err!= nil { return }

        ids := strconv.FormatInt(id, 10)

        //sql := "select code, name, category, introduction, description, features, specification from products where id= " + ids

        sql := "call get_product(" + ids + ")"

        defer db.Close()

        err = db.QueryRow(sql).Scan(&code, &name, &category, &introduction, &description, &features, &specification)

        if err != nil { return }

        return

}

product_test.go

package api

import (

        "testing"

        "fmt"

        "strconv"

)

func TestGetProduct(t *testing.T) {

        err := addSampleProduct()

        if err != nil {

                t.Error("Failed to add sample product.")

        }

        code, name, category, introduction, description, features, specification, err := GetProduct(1)

        if err != nil {

                t.Fatalf("%s", err)

        }

        if code != "code" {

                t.Error("code Invalid")

        }

        if name != "name" {

                t.Error("name Invalid")

        }

        if category != "category" {

                t.Error("category Invalid")

        }

        if introduction != "introduction" {

                t.Error("introduction Invalid")

        }

        if description != "description" {

                t.Error("description Invalid")

        }

        if features != "features" {

                t.Error("features Invalid")

        }

        if specification != "specification" {

                t.Error("specification Invalid")

        }

}

get_product.sql

delimiter //

drop procedure if exists get_product;

create procedure get_product(

  in in_id bigint(20)

)

begin

  select 

        code, name, category, introduction, description, features, specification 

  from 

        products

  where 

        id = in_id;

end //

delimiter ;

create_table.sql

drop table if exists products;

create table products (

      id bigint(20) not null auto_increment primary key,

      code          varchar(255) not null,

      name          varchar(255),

      category      varchar(255) not null,

      introduction  text,

      description   text,

      features      text,

      specification text

) engine=innodb;

#test procedure in mysql command line if fine.

call get_product(1);

go test

get error

--- FAIL: TestGetProduct (0.00 seconds)

product_test.go:57: Error 1312: PROCEDURE poreomix.get_product can't return a result set in the given context

Thanks,

Sammi