1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
|
package main
import (
"database/sql"
"fmt"
_ "github.com/denisenkom/go-mssqldb"
)
type SQLConnection struct {
db *sql.DB
}
func (conn *SQLConnection) init() error {
//query := url.Values{}
//query.Add("app name", "MyAppName")
//
//u := &url.URL{
// Scheme: "sqlserver",
// User: url.UserPassword("sa", "Datab@sed1337"),
// Host: fmt.Sprintf("%s:%d", "", 1431),
// // Path: instance, // if connecting to an instance instead of a port
// RawQuery: query.Encode(),
//}
//var err error
//conn.db, err = sql.Open("sqlserver", u.String())
//return err
var err error
conn.db, err = sql.Open("sqlserver", "server=localhost;user id=sa;password=Datab@sed1337;")
return err
}
func (conn *SQLConnection) getTables() ([]string, error) {
result, err := conn.db.Query(
`SELECT TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME as TABLE_NAME
FROM shipping.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'`)
if err != nil {
return nil, err
}
var tables []string
for result.Next() {
var row string
_ = result.Scan(&row)
tables = append(tables, row)
}
return tables, nil
}
func (conn *SQLConnection) getViews() ([]string, error) {
result, err := conn.db.Query(
`select name from sys.views where is_ms_shipped=0;`)
if err != nil {
return nil, err
}
var tables []string
for result.Next() {
var row string
_ = result.Scan(&row)
tables = append(tables, row)
}
return tables, nil
}
func (conn *SQLConnection) getColumns(table string) ([]string, error) {
result, err := conn.db.Query(fmt.Sprintf("SELECT TOP 1 * FROM %s", table))
if err != nil {
return nil, nil
}
return result.Columns()
}
func (conn *SQLConnection) getTable(table string) ([][]string, error) {
var err error
var msg [][]string
result, err := conn.db.Query(fmt.Sprintf("SELECT * FROM %s", table))
if err != nil {
return msg, err
}
cols, _ := result.Columns()
for result.Next() {
boxes := make([]string, len(cols))
dest := make([]interface{}, len(cols))
for i, _ := range dest {
dest[i] = &boxes[i]
}
_ = result.Scan(dest...)
msg = append(msg, boxes)
}
_ = result.Close()
return msg, err
}
func (conn *SQLConnection) getOneToManyTable(manyTable string, manyColumn string, oneValue string) ([][]string, error) {
result, err := conn.db.Query(fmt.Sprintf(
"SELECT * FROM %s WHERE %s=%s", manyTable, manyColumn, oneValue))
if err != nil {
return nil, err
}
var msg [][]string
cols, _ := result.Columns()
for result.Next() {
boxes := make([]string, len(cols))
dest := make([]interface{}, len(cols))
for i, _ := range dest {
dest[i] = &boxes[i]
}
_ = result.Scan(dest...)
msg = append(msg, boxes)
}
return msg, nil
}
|
