Can anybody please suggest how to go about implemented an ODBC adapter for several interfaces like Oracle, DB2, Paradox, Access, FoxPro or any others with Golang.
Will appreciate any material that will guide me and help me understand what I need to do to realize this task.
Thanks.
odbc
odbc driver written in go. Implements database driver interface as used by standard database/sql package. It calls into odbc dll on Windows, and uses cgo (unixODBC) everywhere else.
1 Like
I would “go” (pun intended) with something like the adapter pattern, but this is probably because my many years with OOP
But even in Go I think the approach makes sense, especially if you want to support multiple driver from the same package.
1 Like
Thank you for this. I am actually to write the adapter to support multiple drivers from the package. I am a 2 weeks Gopher and hoping to get some guardians on how to go about this.
I began Go-ofing around back in September last year, but I am happy to help. Just ping me https://github.com/kristiannissen
1 Like
Thanks a million. I will do a lot with a mentor
Am suppose to create an ODBC adaptor by implementing the following interface:
import "io"
// Shape - data shape
type Shape struct {
X int `json:"x"`
Y int `json:"y"`
}
// DatasourceProvider - data source provider interface
type DatasourceProvider interface {
// List - data source listing
List() ([]Table, error)
// Select - select data source table
Select(selector *Selector, options ...Option) (Table, error)
}
// Table - table data interface
type Table interface {
// GetName - returns name
GetName() string
// GetType - return type
GetType() string
// GetSelector - returns selector
GetSelector() *Selector
// Open - open table
Open() error
// Close - close table
Close()
// GetShape - returns data shape
GetShape() (*Shape, error)
// GetHeaders - returns headers
GetHeaders() ([]string, error)
// GetSynteticHeaders - returns syntetic headers
GetSynteticHeaders() ([]string, error)
// GetTypes - returns types
GetTypes() ([]*DataTypeWithOption, error)
// TrySetType - attempt setting type to column
TrySetType(index int, columnType DataType, options ...Option) error
// SetTypes - setting types to column
SetTypes(types []*DataTypeWithOption)
// GetPlainSample - returns plain sample
GetPlainSample() ([][]string, error)
// GetSample - returns sample
GetSample() ([][]interface{}, error)
// GetData - returns data stream
GetData() (chan *DataRow, error)
}
// TableProvider - table data provider
type TableProvider interface {
// Open - open table
Open() error
// Close - close table
Close()
// GetShape - returns data shape
GetShape() (*Shape, error)
// GetHeaders - returns headers
GetHeaders() ([]string, error)
// GetSynteticHeaders - returns syntetic headers
GetSynteticHeaders() ([]string, error)
// GetTypes - returns types
GetTypes() ([]*DataTypeWithOption, error)
// TrySetType - attempt setting type to column
TrySetType(index int, columnType DataType, options ...Option) error
// SetTypes - setting types to column
SetTypes(types []*DataTypeWithOption)
// GetPlainSample - returns plain sample
GetPlainSample() ([][]string, error)
// GetSample - returns sample
GetSample() ([][]interface{}, error)
// GetData - returns data stream
GetData() (chan *DataRow, error)
}
// FileReader - file reader interface
type FileReader interface {
io.ReadSeeker
io.ReadCloser
}
// Option - options interface
type Option interface {
Key() string
Value() interface{}
}
What are the steps am suppose to take?
Do you have the interface methods from somewhere? What is the shape for?
1 Like
Actually this is an example I am suppose to look at to get an idea
import (
"encoding/json"
"fmt"
)
// DataType - data type
type DataType int
const (
// DataTypeString - data type string
DataTypeString DataType = iota
// DataTypeFloat - data type float
DataTypeFloat DataType = iota
// DataTypeInteger - data type integer
DataTypeInteger DataType = iota
// DataTypeUnsignedInteger - data type unsigned integer
DataTypeUnsignedInteger DataType = iota
// DataTypeDateTime - data type datetime
DataTypeDateTime DataType = iota
// DataTypeDate - data type date
DataTypeDate DataType = iota
// OptionDateTimeLayout - option date/time layout
OptionDateTimeLayout = "layout"
// OptionFloatFormat - option float format
OptionFloatFormat = "format"
)
// String - string representation
func (inst DataType) String() string {
switch inst {
case DataTypeDateTime:
return "datetime"
case DataTypeDate:
return "date"
case DataTypeFloat:
return "float"
case DataTypeInteger:
return "integer"
case DataTypeUnsignedInteger:
return "unsigned-integer"
default:
return "string"
}
}
// DataTypeFromString - data type from string
func DataTypeFromString(value string) DataType {
switch value {
case "datetime":
return DataTypeDateTime
case "date":
return DataTypeDate
case "float":
return DataTypeFloat
case "integer":
return DataTypeInteger
case "unsigned-integer":
return DataTypeUnsignedInteger
default:
return DataTypeString
}
}
// Clickhouse - clickhouse type
func (inst DataType) Clickhouse() string {
switch inst {
case DataTypeString:
return "LowCardinality(String)"
case DataTypeDateTime:
return "DateTime64"
case DataTypeDate:
return "Date"
case DataTypeFloat:
return "Float64"
case DataTypeInteger:
return "Int64"
case DataTypeUnsignedInteger:
return "UInt64"
default:
return ""
}
}
// dataTypeOption - data type option struct
type dataTypeOption struct {
key string
value interface{}
}
// Key - data type option key getting
func (inst *dataTypeOption) Key() string {
return inst.key
}
// Value - data type option value getting
func (inst *dataTypeOption) Value() interface{} {
return inst.value
}
// DataTypeOption - data type options
func DataTypeOption(key string, value interface{}) Option {
return &dataTypeOption{key: key, value: value}
}
// DataTypeWithOption - data type with options
type DataTypeWithOption struct {
dataType DataType
options map[string]Option
}
// NewDataTypeWithOption - data type with options constructor
func NewDataTypeWithOption(dataType DataType, options ...Option) *DataTypeWithOption {
inst := &DataTypeWithOption{
dataType: dataType,
options: make(map[string]Option),
}
for _, option := range options {
inst.options[option.Key()] = option
}
return inst
}
// Type - data type getting
func (inst *DataTypeWithOption) Type() DataType {
return inst.dataType
}
// Options - data type optinos getting
func (inst *DataTypeWithOption) Options() []Option {
options := []Option{}
for _, opt := range inst.options {
options = append(options, opt)
}
return options
}
// String - string representation
func (inst *DataTypeWithOption) String() string {
optionsString := ""
for key, value := range inst.options {
optionsString += fmt.Sprintf("%s=%v, ", key, value)
}
optionsString = trimSuffix(optionsString, ", ")
if len(optionsString) > 0 {
return fmt.Sprintf("%s (%s)", inst.dataType.String(), optionsString)
}
return fmt.Sprintf("%s", inst.dataType.String())
}
// MarshalJSON - json marshaling
func (inst *DataTypeWithOption) MarshalJSON() ([]byte, error) {
type optionStruct struct {
Key string `json:"key"`
Value interface{} `json:"value"`
}
type proxyStruct struct {
Type string `json:"type"`
Options []optionStruct `json:"options"`
}
proxy := proxyStruct{
Type: inst.dataType.String(),
Options: []optionStruct{},
}
for _, opt := range inst.options {
proxy.Options = append(proxy.Options, optionStruct{Key: opt.Key(), Value: opt.Value()})
}
return json.Marshal(proxy)
}
// UnmarshalJSON - json unmarshaling
func (inst *DataTypeWithOption) UnmarshalJSON(data []byte) error {
type optionStruct struct {
Key string `json:"key"`
Value interface{} `json:"value"`
}
type proxyStruct struct {
Type string `json:"type"`
Options []optionStruct `json:"options"`
}
proxy := proxyStruct{}
err := json.Unmarshal(data, &proxy)
if err != nil {
return err
}
inst.dataType = DataTypeFromString(proxy.Type)
for k := range inst.options {
delete(inst.options, k)
}
for _, opt := range proxy.Options {
inst.options[opt.Key] = &dataTypeOption{key: opt.Key, value: opt.Value}
}
return nil
}
// Clickhouse - clickhouse type
func (inst *DataTypeWithOption) Clickhouse() string {
return inst.dataType.Clickhouse()
}
// DataRow - data row
type DataRow struct {
Row []interface{}
Error error
}
package datastream
import (
"bufio"
"encoding/csv"
"fmt"
"io"
"math"
"os"
"strconv"
"strings"
"time"
)
const (
// DefaultCSVDelimiter - default csv delimiter
DefaultCSVDelimiter rune = ','
// DefaultCSVComment - default csv comment
DefaultCSVComment rune = '#'
// OptionCSVDelimiter - option csv delimiter
OptionCSVDelimiter string = "csv-delimiter"
// OptionCSVComment - option csv comment
OptionCSVComment string = "csv-comment"
)
// FileCSV - file csv
type FileCSV struct {
filename string
file FileReader
types []*DataTypeWithOption
delimiter rune
comment rune
withHeaders bool
sampleSize int
withOrderColumn bool
}
// NewFileCSV - file csv constructor
func NewFileCSV(filename string, types []*DataTypeWithOption, options ...Option) *FileCSV {
inst := &FileCSV{
filename: filename,
types: types,
delimiter: DefaultCSVDelimiter,
comment: DefaultCSVComment,
withHeaders: DefaultWithHeaders,
sampleSize: DefaultSampleSize,
}
for _, option := range options {
switch option.Key() {
case OptionCSVDelimiter:
inst.delimiter = rune(option.Value().(string)[0])
case OptionCSVComment:
inst.comment = rune(option.Value().(string)[0])
case OptionWithHeaders:
inst.withHeaders = option.Value().(bool)
case OptionSampleSize:
v, ok := option.Value().(int)
if !ok {
v2, ok2 := option.Value().(float64)
if !ok2 {
continue
}
inst.sampleSize = int(v2)
} else {
inst.sampleSize = v
}
case OptionWithOrderColumn:
inst.withOrderColumn = option.Value().(bool)
}
}
return inst
}
// Open - open file
func (inst *FileCSV) Open() error {
file, err := os.Open(inst.filename)
if err != nil {
return err
}
inst.file = file
return nil
}
// Close - close file
func (inst *FileCSV) Close() {
inst.file.Close()
inst.file = nil
inst.types = nil
}
// reader - csv reader getting
func (inst *FileCSV) reader(fromBegin bool) *csv.Reader {
if fromBegin {
inst.file.Seek(0, 0)
bufReader := bufio.NewReader(inst.file)
buf, err := bufReader.Peek(3)
if err == nil {
if buf[0] == 0xef && buf[1] == 0xbb && buf[2] == 0xbf {
inst.file.Seek(3, 0)
} else {
inst.file.Seek(0, 0)
}
}
}
reader := csv.NewReader(inst.file)
reader.Comma = inst.delimiter
reader.Comment = inst.comment
reader.FieldsPerRecord = -1
return reader
}
// GetShape - getting data shape
func (inst *FileCSV) GetShape() (*Shape, error) {
inst.file.Seek(0, 0)
shape := &Shape{}
reader := inst.reader(true)
firstMet := false
var firstRecord []string
maxRow := 0
for {
record, err := reader.Read()
if err == io.EOF {
break
}
if err != nil {
return shape, err
}
if !firstMet {
firstMet = true
firstRecord = record
}
maxRow++
}
if inst.withHeaders {
maxRow--
}
shape.X = maxRow
maxCol := len(firstRecord)
shape.Y = maxCol
return shape, nil
}
// GetHeaders - returns headers
func (inst *FileCSV) GetHeaders() ([]string, error) {
reader := inst.reader(true)
headers, err := reader.Read()
if err != nil {
return nil, err
}
if !inst.withHeaders {
headers = make([]string, len(headers))
}
if inst.withOrderColumn {
headers = append([]string{fieldDataOrder.HeaderName}, headers...)
}
return headers, nil
}
// GetSynteticHeaders - returns syntetic headers
func (inst *FileCSV) GetSynteticHeaders() ([]string, error) {
reader := inst.reader(true)
record, err := reader.Read()
if err != nil {
return nil, err
}
headers := []string{}
i := 0
for range record {
headers = append(headers, fmt.Sprintf("col%d", i+1))
i++
}
if inst.withOrderColumn {
headers = append([]string{fieldDataOrder.ColumnName}, headers...)
}
return headers, nil
}
// GetTypes - returns types
func (inst *FileCSV) GetTypes() ([]*DataTypeWithOption, error) {
if len(inst.types) > 0 {
return inst.types, nil
}
reader := inst.reader(true)
record, err := reader.Read()
if err != nil {
return nil, err
}
types := []*DataTypeWithOption{}
for range record {
types = append(types, NewDataTypeWithOption(DataTypeString))
}
if inst.withOrderColumn {
types = append([]*DataTypeWithOption{fieldDataOrder.Type}, types...)
}
inst.types = types
return types, nil
}
// tryColumnType - attempt to apply type to column
func (inst *FileCSV) tryColumnType(value string, columnType DataType, options ...Option) error {
var err error
switch columnType {
case DataTypeInteger:
_, err = strconv.ParseInt(value, 10, 64)
case DataTypeUnsignedInteger:
_, err = strconv.ParseUint(value, 10, 64)
case DataTypeFloat:
format := DefaultFloatFormat
for _, option := range options {
if option.Key() == OptionFloatFormat {
format = option.Value().(string)
}
}
switch format {
case ExcelFloatFormat:
val := strings.Replace(value, ",", ".", -1)
_, err = strconv.ParseFloat(val, 64)
case CommaSeparatedFloatFormat:
val := strings.Replace(value, ",", "", -1)
_, err = strconv.ParseFloat(val, 64)
case ScientificFloatFormat:
pos := strings.IndexAny(value, "eE")
if pos < 0 {
_, err = strconv.ParseFloat(value, 64)
} else {
_, err2 := strconv.ParseFloat(value[0:pos], 64)
if err2 != nil {
err = err2
} else {
_, err = strconv.ParseInt(value[(pos+1):], 10, 64)
}
}
default:
_, err = strconv.ParseFloat(value, 64)
}
case DataTypeDate:
layout := DefaultDateLayout
for _, option := range options {
if option.Key() == OptionDateTimeLayout {
layout = option.Value().(string)
}
}
_, err = time.Parse(layout, value)
case DataTypeDateTime:
layout := DefaultDateTimeLayout
for _, option := range options {
if option.Key() == OptionDateTimeLayout {
layout = option.Value().(string)
}
}
_, err = time.Parse(layout, value)
default:
err = nil
}
if err != nil {
return err
}
return nil
}
// applyColumnType - applying type to column
func (inst *FileCSV) applyColumnType(value string, columnType DataType, options ...Option) (interface{}, error) {
var newValue interface{}
var err error
switch columnType {
case DataTypeInteger:
newValue, err = strconv.ParseInt(value, 10, 64)
case DataTypeUnsignedInteger:
newValue, err = strconv.ParseUint(value, 10, 64)
case DataTypeFloat:
format := DefaultFloatFormat
for _, option := range options {
if option.Key() == OptionFloatFormat {
format = option.Value().(string)
}
}
switch format {
case ExcelFloatFormat:
val := strings.Replace(value, ",", ".", -1)
newValue, err = strconv.ParseFloat(val, 64)
case CommaSeparatedFloatFormat:
val := strings.Replace(value, ",", "", -1)
newValue, err = strconv.ParseFloat(val, 64)
case ScientificFloatFormat:
pos := strings.IndexAny(value, "eE")
if pos < 0 {
newValue, err = strconv.ParseFloat(value, 64)
} else {
baseVal, err2 := strconv.ParseFloat(value[0:pos], 64)
if err2 != nil {
err = err2
} else {
expVal, err2 := strconv.ParseInt(value[(pos+1):], 10, 64)
if err2 != nil {
err = err2
}
newValue = baseVal * math.Pow10(int(expVal))
}
}
default:
newValue, err = strconv.ParseFloat(value, 64)
}
case DataTypeDateTime:
layout := DefaultDateTimeLayout
for _, option := range options {
if option.Key() == OptionDateTimeLayout {
layout = option.Value().(string)
}
}
newValue, err = time.Parse(layout, value)
case DataTypeDate:
layout := DefaultDateLayout
for _, option := range options {
if option.Key() == OptionDateTimeLayout {
layout = option.Value().(string)
}
}
newValue, err = time.Parse(layout, value)
default:
newValue, err = value, nil
}
if err != nil {
return newValue, err
}
return newValue, nil
}
// TrySetType - attempt setting type to column
func (inst *FileCSV) TrySetType(index int, columnType DataType, options ...Option) error {
if columnType == DataTypeString {
return nil
}
reader := inst.reader(true)
hasMetFirst := false
for {
record, err := reader.Read()
if err == io.EOF {
break
}
if err != nil {
return err
}
if !hasMetFirst {
hasMetFirst = true
if inst.withHeaders {
continue
}
}
value := record[index]
err = inst.tryColumnType(value, columnType, options...)
if err != nil {
return err
}
}
return nil
}
// SetTypes - setting types to column
func (inst *FileCSV) SetTypes(types []*DataTypeWithOption) {
if len(types) > 0 {
if inst.withOrderColumn {
inst.types = append([]*DataTypeWithOption{fieldDataOrder.Type}, types...)
} else {
inst.types = types
}
}
}
// GetPlainSample - returns plain sample
func (inst *FileCSV) GetPlainSample() ([][]string, error) {
rows := [][]string{}
inst.file.Seek(0, 0)
row := bufio.NewScanner(inst.file)
size := 0
for row.Scan() && size < inst.sampleSize {
line := strings.TrimRight(row.Text(), "\r\n")
rows = append(rows, []string{line})
size++
}
return rows, nil
}
// GetSample - returns sample
func (inst *FileCSV) GetSample() ([][]interface{}, error) {
sample := [][]interface{}{}
types, err := inst.GetTypes()
if err != nil {
return sample, err
}
if inst.withOrderColumn {
types = types[1:]
}
reader := inst.reader(true)
if inst.withHeaders {
_, err := reader.Read()
if err == io.EOF {
return sample, nil
}
if err != nil {
return sample, err
}
}
orderCounter := 0
for i := 0; i < inst.sampleSize; i++ {
record, err := reader.Read()
if err == io.EOF {
break
}
if err != nil {
return sample, err
}
data := []interface{}{}
if inst.withOrderColumn {
data = append(data, orderCounter+1)
}
if len(record) != len(types) {
return sample, ErrorIncorrectTypesNumber
}
for j, rv := range record {
v, err := inst.applyColumnType(rv, types[j].Type(), types[j].Options()...)
if err != nil {
return sample, err
}
data = append(data, v)
}
sample = append(sample, data)
orderCounter++
}
return sample, nil
}
// GetData - returns data stream
func (inst *FileCSV) GetData() (chan *DataRow, error) {
dataStream := make(chan *DataRow)
types, err := inst.GetTypes()
if err != nil {
return dataStream, err
}
reader := inst.reader(true)
if inst.withOrderColumn {
types = types[1:]
}
go func(stream chan *DataRow, types []*DataTypeWithOption, withHeaders bool) {
hasMetFirst := false
orderCounter := 0
hasError := false
for {
dr := &DataRow{}
record, err := reader.Read()
if err == io.EOF {
break
}
if err != nil {
dr.Error = err
stream <- dr
hasError = true
break
}
if len(record) != len(types) {
dr.Error = ErrorIncorrectTypesNumber
stream <- dr
hasError = true
break
}
if !hasMetFirst {
hasMetFirst = true
if inst.withHeaders {
continue
}
}
if inst.withOrderColumn {
dr.Row = append(dr.Row, orderCounter+1)
}
for i, rv := range record {
v, err := inst.applyColumnType(rv, types[i].Type(), types[i].Options()...)
if err != nil {
dr.Error = err
stream <- dr
hasError = true
break
}
dr.Row = append(dr.Row, v)
}
if hasError {
break
}
stream <- dr
orderCounter++
}
close(dataStream)
}(dataStream, types, inst.withHeaders)
return dataStream, nil
}
If I understand the project correctly, what you want to achieve is what you find here https://www.php.net/manual/en/function.odbc-connect.php - full documentation https://www.php.net/manual/en/book.uodbc.php
That is somewhat of a project 
1 Like
Thanks for the prompt replies and links. Will surely take a look at them
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.