Vertica driver for Go

[Drew Wells]

Is there any work being done to support execute queries on Vertica in Go?

[japet...@gmail.com]

I've been looking for the same thing, tried connecting with the postgres driver just in case but no luck as of yet.

[Allan Cochrane]

Don't the existing ODBC libraries work in this case?

[Allan Cochrane]

I tried code.google.com/p/odbc against a Vertica DB, without success. On the Vertica forums  (https://community.vertica.com/vertica/topics/syntax_error_at_or_near_at_end_of_the_query) there was a user who commented:

For anyone else who runs into this, the code.google.com/p/odbc driver appends a null termination character to every query. This doesn't play well with vertica.

and he followed up with:

I did manage to get it working by a quick hack tohttps://code.google.com/p/odbc/source/browse/odbcstmt.go - I added this to line 39:
b=b[0:len(b)-1]
I didn't look into this further as to which was is per spec so I don't know where this chagne should be implemented.

The mgodbc driver didn't work for me either with Vertica, claiming a permissions error.

YMMV

[brainman]

On Saturday, 2 August 2014 09:15:00 UTC+10, Allan Cochrane wrote:

> ... I tried code.google.com/p/odbc against a Vertica DB, without success. ...

I've created an issue https://code.google.com/p/odbc/issues/detail?id=46 to track this.

> I did manage to get it working by a quick hack tohttps://code.google.com/p/odbc/source/browse/odbcstmt.go - I added this to line 39:

> b=b[0:len(b)-1]

> I didn't look into this further as to which was is per spec so I don't know where this chagne should be implemented.

I am not sure who is at fault here. But I don't see reason why I cannot change code.google.com/p/odbc to accommodate. I have made appropriate change here https://codereview.appspot.com/120500043/. Would you like to review my change? I don't have "Vertica DB", so I cannot check.

Alex

[Allan Cochrane]

Tested change and it works.

Thanks,

Allan

[Allan Cochrane]

On Friday, 1 August 2014 18:15:00 UTC-5, Allan Cochrane wrote:

The mgodbc driver didn't work for me either with Vertica, claiming a permissions error.

Following up on this: mgodbc works just fine actually on the simple test case; it was a silly user error, trying to select from a table that didn't exist, d'oh!

Allan 

[simon.l...@gmail.com]

Hi Guys,

Can anyone share which connection STRING used to connect to Vertica with odbc driver ??

I tried :

var c := "server=[serverAddress];database=[MainSchema];uid=[user];pwd=[password];driver=sql server;"

sql.Open("odbc", c)

But couldn't query...

Anything wrong with my connection string ?

Thanks for the help.

[Drew Wells]

I'm also not having any luck with the connection string.  Good somebody share a working one?

Mine looks similar to the above but driver=FreeTDS

--
You received this message because you are subscribed to a topic in the Google Groups "golang-nuts" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/golang-nuts/ysqaztTjQaA/unsubscribe.
To unsubscribe from this group and all its topics, send an email to golang-nuts...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[brainman]

On Wednesday, 24 September 2014 02:13:50 UTC+10, simon.l...@gmail.com wrote:

> ... Can anyone share which connection STRING used to connect to Vertica with odbc driver ??

I don't use Vertica myself, but ODBC connection string is defined by the driver manufacturer. You should lookup your driver manual. Maybe google?

> I tried :

> var c := "server=[serverAddress];database=[MainSchema];uid=[user];pwd=[password];driver=sql server;"

> sql.Open("odbc", c)

I doubt this will work, because "driver=sql server;" will choose MS SQL Server driver instead. Is Vertica uses same driver? I doubt. I would google or ask someone who connected to database like that before.

> But couldn't query...

Very little details. It is always a good start to show us what you see yourself - your programs output. You should show text of your program too, so we could try it.

> Anything wrong with my connection string ?

Your connection string looks fine if you want to connected to MS SQL Server. I doubt it will work with anything else.

Alex

[Allan Cochrane]

Hi,

I have had success with this by creating a file (odbc.ini), setting the $ODBCINI environment variable to that file's path and then the ODBC driver picks up the connection info from that file.

        os.Setenv("VERTICAINI",os.Getenv("ODBCINI")) // Needed for i18n warnings?

        db, err := sql.Open("odbc", "dsn=pro_cache")

The dsn= points to the relevant connection info in the $ODBCINI file.

I understand this is not exactly the answer to the question you're asking but it may be a workaround and a route forward.

Allan

[simon.l...@gmail.com]

Hi Allan

Thanks a lot for your answer & sorry I couldn't test before.

This looks promising, but I still don't understand what info to fill into the "odbc.ini" file ?

You are referring to : 

db, err := sql.Open("odbc", "dsn=pro_cache")

The dsn= points to the relevant connection info in the $ODBCINI file.

What relevant connection are you talking about ? Are you able to share the odbc.ini file content ? The only odbc.ini file I found on my computer is empty :

(only contains :

"

[ODBC 32 bit Data Sources]

"

)

Otherwise, you are also mentioning above that the mgodbc driver is working for you for Vertica ?

Would you please share the script you used to connect ?

Thanks a lot again, hopefully you will read this thread...

SL

[Allan Cochrane]

Hi,

an example of a Vertica compatible DSN entry in an ODBC .ini file would be:

[pro_cache]

Driver = /home/allan/vertica/lib64/libverticaodbc_unixodbc.so

Debug = 0

Servername = 192.168.1.67

Database = dbase_ac

UserName = db_user

Password = dbase_ac123

Port = 5433

The .so file in the Driver line is supplied by Vertica, by the way. I think I got it from the download page on the Vertica web site. I'm using Linux and have not used Go on Windows with ODBC so can't help you if that's your situation.

The pro_cache is the name of the section in the odbc.ini file that is referred to in the call to sql.Open().

I haven't used Go for a while now and am not able to get my hands on the example script to share, sorry.

Allan

[simon.l...@gmail.com]

Hey Hi Allan,

Thanks a lot that is already amazing !

I am working on a windows machine, so I'll figure out how to configure the DSN under windows with the ODBC driver provided by Vertica Team.

If I manage to have it work, I'll share here.

Thanks a lot !

[simon.l...@gmail.com]

Hi Allan & all,

I finally managed to have it working very simply with the ODBC driver provided here : https://code.google.com/p/odbc/

I am under windows, and I didn't have to do anything special nor writing any specific .ini file....  I only installed the ODBC driver that one can find on Vertica website : https://my.vertica.com/download-community-edition/#drivers

The code I used is below, for anyone interested :

(inspired from http://www.easysoft.com/support/kb/kb01045.html )

// Example from: http://www.easysoft.com/support/kb/kb01045.html

package main

import (

    _ "code.google.com/p/odbc"

    "database/sql"

    "fmt"

    "log"

)

var (

    driver string

    server string

    dbc    string

    port   string

    user   string

    pwd    string

    DSN    string

)

func prepConnString() {

    /* Connection String parameters */

    driver = "{Vertica}"     /* This is the ODBC driver name, when you installed it */

    server = "blablabla.com" /* Server address */

    dbc = "database_name"    /* Name of database in Vertica */

    port = "1234"            /* Port number */

    user = ""                /* user login : you should do in a safer way ! */

    pwd = ""                 /* user password : you should do in a safer way ! */

    DSN = "Driver=" + driver + ";Servername=" + server + ";Database=" + dbc + ";Port=" + port + ";uid=" + user + ";pwd=" + pwd + ";"

    fmt.Printf("DSN (connection string) : %s\n", DSN)

}

func main() {

    prepConnString()

    var db *sql.DB

    var err error

    db, err = sql.Open("odbc", DSN)

    if err != nil {

        log.Fatal(err)

    }

    // Var for each column to be retrieved by query

    var (

        col1 string

        col2 string

    )

    // QUERY

    rows, err := db.Query("SELECT column1,column2 FROM schema_name.table_name WHERE columnX = ? LIMIT 10", "whatever")

    if err != nil {

        log.Fatal(err)

    }

    defer rows.Close()

    // Read results (line per line)

    for rows.Next() {

        err := rows.Scan(&col1, &col2)

        if err != nil {

            log.Fatal(err)

        }

        log.Println(col1, col2)

    }

    err = rows.Err()

    if err != nil {

        log.Fatal(err)

    }

    defer db.Close()

}

Hope that will help someone !

SL