-
Notifications
You must be signed in to change notification settings - Fork 17
/
doc.go
203 lines (144 loc) · 7.41 KB
/
doc.go
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
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
/*
Package nzgo is a pure Go language driver for the database/sql package to work with IBM PDA (aka Netezza)
In most cases clients will use the database/sql package instead of
using this package directly. For example:
import (
"database/sql"
_ "github.com/IBM/nzgo/v12"
)
func main() {
connStr := "user=nz dbname=db1 sslmode=verify-full "
db, err := sql.Open("nzgo", connStr)
if err != nil {
log.Fatal(err)
}
age := 21
rows, err := db.Query("SELECT name FROM users WHERE age = ?", age)
…
}
Logging
nzgo defines a simple logger interface. Set logLevel to control logging verbosity and logPath to specify log file path.
By default logging will be enabled with logLevel=Info and current directory as logPath.
You can configure logLevel and logPath (i.e. log file directory) as per your requirement.
There is one more configuration parameter with logger "additionalLogFile". This parameter can be used to set additional logger file.
additionalLogFile can be used to enable writing logs to stdout, this can be achieved by simply setting
"additionalLogFile=stdout"
Valid values for 'logLevel' are : "OFF" , "DEBUG", "INFO" and "FATAL".
logLevel=OFF can be used to turn off logging. It will turn of both internal and additionalLogFile logs.
These logger configuration parameters should be mentinoed in connection string.
SecurityLevel
The level of security (SSL/TLS) that the driver uses for the connection to the data store.
onlyUnSecured: The driver does not use SSL.
preferredUnSecured: If the server provides a choice, the driver does not use SSL.
preferredSecured: If the server provides a choice, the driver uses SSL.
onlySecured: The driver does not connect unless an SSL connection is available.
Similarly, Netezza server has above securityLevel.
Cases which would fail:
Client tries to connect with 'Only secured' or 'Preferred secured' mode while server is 'Only Unsecured' mode.
Client tries to connect with 'Only secured' or 'Preferred secured' mode while server is 'Preferred Unsecured' mode.
Client tries to connect with 'Only Unsecured' or 'Preferred Unsecured' mode while server is 'Only Secured' mode.
Client tries to connect with 'Only Unsecured' or 'Preferred Unsecured' mode while server is 'Preferred Secured' mode.
Below are the securityLevel you can pass in connection string :
0: Preferred Unsecured session
1: Only Unsecured session
2: Preferred Secured session
3: Only Secured session
Connection String
Use Open to create a database handle with connection parameters:
db, err := sql.Open("nzgo", "<connection string>")
The Go Netezza Driver supports the following connection syntaxes (or data source name formats):
"host=localhost user=admin dbname=db1 port=5480 password=password sslmode=require sslrootcert=C:/Users/root31.crt securityLevel=3 logLevel=Info logPath=./ additionalLogFile=stdout"
In this case, application is running from NPS server itself so using 'localhost'.
Golang driver should connect on port 5480(postgres port). The user is admin,
password is password, database is db1, sslmode is require, and the location of the root
certificate file is C:/Users/root31.crt with securityLevel as 'Only Secured session'
Connection Parameters
When establishing a connection using nzgo you are expected to
supply a connection string containing zero or more parameters.
Below are subset of the connection parameters supported by nzgo.
The following special connection parameters are supported:
* dbname - The name of the database to connect to
* user - The user to sign in as
* password - The user's password
* host - The host to connect to. Values that start with / are for unix
domain sockets. (default is localhost)
* port - The port to bind to. (default is 5480)
* sslmode - Whether or not to use SSL (default is require)
* sslcert - Cert file location. The file must contain PEM encoded data.
* sslkey - Key file location. The file must contain PEM encoded data.
* sslrootcert - The location of the root certificate file. The file
must contain PEM encoded data.
* logLevel - Log Level[Info/Debug/Fatal/Off]
* logPath - Path to write log files
* additionalLogPath - Additional log file can be mentioned here or stdout
Valid values for sslmode are:
* disable - No SSL
* require - Always SSL (skip verification)
* verify-ca - Always SSL (verify that the certificate presented by the
server was signed by a trusted CA)
Use single quotes for values that contain whitespace:
"user=nz password='with spaces'"
A backslash will escape the next character in values:
"user=space\ man password='it\'s valid'"
Note that the connection parameter client_encoding (which sets the
text encoding for the connection) may be set but must be "UTF8",
matching with the same rules as Postgres. It is an error to provide
any other value.
Queries
database/sql does not dictate any specific format for parameter markers
in query strings, but nzgo uses the Netezza-specific parameter markers i.e. '?',
as shown below.
rows, err := db.Query(`SELECT name FROM users WHERE favorite_fruit = ?
OR age = ? `, "orange", 64)
First parameter marker in the query would be replaced by first arguement,
second parameter marker in the query would be replaced by second arguement
and so on.
nzgo supports the RowsAffected() method of the Result type in database/sql.
var row int
result, err := db.Exec(`INSERT INTO users(name, favorite_fruit, age)
VALUES('beatrice', 'starfruit', 93) )
if err == nil {
row, _ := result.RowsAffected()
}
For additional instructions on querying see the documentation for the database/sql package.
nzgo also supports transaction queries as specified in database/sql package https://github.com/golang/go/wiki/SQLInterface.
Transactions are started by calling Begin.
tx, err := conn.Begin()
if err != nil {
return err
}
// Rollback is safe to call even if the tx is already closed, so if
// the tx commits successfully, this is a no-op
defer tx.Rollback()
_, err = tx.Exec("insert into foo(id) values (1)")
if err != nil {
return err
}
err = tx.Commit()
if err != nil {
return err
}
Supported Data Types
This package returns the following types for values from the Netezza backend:
- integer types byteint, smallint, integer, and bigint are returned as int8, int 16, int 32 and int64 respectively
- floating-point types real and double precision are returned as float32 and float64 respectively
- character types char, varchar, nchar and nvarchar are returned as string
- temporal types date, time, timetz, timestamp, interval and timestamptz are
returned as string
- numeric and geometry are returned as string
- the boolean type is returned as bool
External table
You can unload data from an IBM Netezza database table on a Netezza host system to a remote client.
This unload does not remove rows from the database but instead stores the unloaded data in a flat file
(external table) that is suitable for loading back into a Netezza database.
Below query would create a file 'et1.txt' on remote system from Netezza table t2 with data delimeted by '|'.
result, err := db.Exec("create external table et1 'C:\\et1.txt' using (remotesource 'golang' delim '|') as select * from t2;")
if err != nil {
fmt.Println("Error in creating external table", err)
} else {
fmt.Println("External Table created successfully")
}
See https://www.ibm.com/support/knowledgecenter/en/SSULQD_7.2.1/com.ibm.nz.load.doc/t_load_unloading_data_remote_client_sys.html
for more information about external table
*/
package nzgo