1 /******************************************************************************
2 *
3 * Copyright (C) 2006-2008, The Gentee Group. All rights reserved.
4 * This file is part of the Gentee open source project - http://www.gentee.com.
5 *
6 * THIS FILE IS PROVIDED UNDER THE TERMS OF THE GENTEE LICENSE ("AGREEMENT").
7 * ANY USE, REPRODUCTION OR DISTRIBUTION OF THIS FILE CONSTITUTES RECIPIENTS
8 * ACCEPTANCE OF THE AGREEMENT.
9 *
10 * Author: Alexander Krivonogov ( algen )
11 *
12 ******************************************************************************/
13 /*-----------------------------------------------------------------------------
14 * Id: odbc L "ODBC (SQL)"
15 *
16 * Summary: Data Access (SQL queries) Using ODBC. This library is applied for
17 running SQL queries on a database using ODBC. The queries with
18 parameters are not supported by the current version. Read
19 #a(odbc_desc) for more details. For using this
20 library, it is required to specify the file odbc.g (from lib\odbc
21 subfolder) with include command. #srcg[
22 |include : $"...\gentee\lib\odbc\odbc.g"]
23 *
24 * List: *,odbc_desc,
25 *#lng/methods#,odbc_connect,odbc_disconnect,odbc_geterror,
26 odbc_newquery,
27 *SQL query methods,odbcquery_active,odbcquery_close,
28 odbcquery_fieldbyname,odbcquery_first,odbcquery_geterror,
29 odbcquery_getrecordcount,odbcquery_last,odbcquery_moveby,
30 odbcquery_next,odbcquery_prior,odbcquery_run,odbcquery_settimeout,
31 *Field methods,odbcfield_getbuf,odbcfield_getdatetime,
32 odbcfield_getdouble,odbcfield_getindex,odbcfield_getint,
33 odbcfield_getlong,odbcfield_getname,odbcfield_getnumeric,
34 odbcfield_getstr,odbcfield_gettype,odbcfield_isnull
35 *
36 -----------------------------------------------------------------------------*/
37
38 import "odbc32.dll"
39 {
40 uint SQLAllocHandle( uint, uint, uint )
41 uint SQLBindCol( uint, uint, uint, uint, uint, uint )
42 uint SQLCloseCursor( uint )
43 uint SQLConnect( uint, uint, uint, uint, uint, uint, uint )
44 uint SQLDescribeCol( uint, uint, uint, uint, uint, uint, uint, uint, uint )
45 uint SQLDisconnect( uint )
46 uint SQLDriverConnect( uint, uint, uint, uint, uint, uint, uint, uint )
47 uint SQLExecDirect( uint, uint, uint )
48 uint SQLFetch( uint )
49 uint SQLFetchScroll( uint, uint, uint )
50 uint SQLFreeHandle( uint, uint )
51 uint SQLFreeStmt( uint, uint )
52 uint SQLGetConnectAttr( uint, uint, uint, uint, uint )
53 uint SQLGetData( uint, uint, uint, uint, uint, uint )
54 uint SQLGetDiagRec( uint, uint, uint, uint, uint, uint, uint, uint )
55 uint SQLGetStmtAttr( uint, uint, uint, uint, uint )
56 uint SQLGetInfo( uint, uint, uint, uint, uint )
57 uint SQLNumResultCols( uint, uint )
58 uint SQLRowCount( uint, uint )
59 uint SQLSetEnvAttr( uint, uint, uint, uint )
60 uint SQLSetStmtAttr( uint, uint, uint, uint )
61 uint SQLSetConnectAttr( uint, uint, uint, uint )
62 }
63
64 define
65 {
66 ODBC_POCKET_SIZE = 65535//1500000//2097152
67 //Установка версии
68 SQL_ATTR_ODBC_VERSION = 200
69 SQL_OV_ODBC3 = 3
70
71 //Типы дескрипторов
72 SQL_HANDLE_ENV = 1
73 SQL_HANDLE_DBC = 2
74 SQL_HANDLE_STMT = 3
75 SQL_HANDLE_DESC = 4
76
77 //Возвращаемые значения функций SQL*
78 SQL_SUCCESS = 0
79 SQL_SUCCESS_WITH_INFO = 1
80 SQL_NO_DATA = 100
81 SQL_ERROR = 0xFFFF//-1
82 SQL_INVALID_HANDLE = 0xFFFE//-2
83 SQL_STILL_EXECUTING = 2
84 SQL_NEED_DATA = 99
85
86 SQL_DRIVER_COMPLETE=1
87 SQL_DRIVER_NOPROMPT=0
88
89 SQL_CLOSE = 0
90 SQL_UNBIND = 2
91
92 //Установка статичного курсора
93 SQL_ATTR_CURSOR_TYPE = 6
94 SQL_ATTR_QUERY_TIMEOUT = 0
95 SQL_CURSOR_STATIC = 3
96
97 //Типы данных
98 SQL_UNKNOWN_TYPE = 0
99 SQL_CHAR = 1
100 SQL_NUMERIC = 2
101 SQL_DECIMAL = 3
102 SQL_INTEGER = 4
103 SQL_SMALLINT = 5
104 SQL_FLOAT = 6
105 SQL_REAL = 7
106 SQL_DOUBLE = 8
107 SQL_VARCHAR = 12
108 SQL_LONGVARCHAR = 0xFFFF//-1
109 SQL_BINARY = 0xFFFE//-2
110 SQL_VARBINARY = 0xFFFD//-3
111 SQL_LONGVARBINARY = 0xFFFC//-4
112 SQL_BIGINT = 0xFFFB//-5
113 SQL_TINYINT = 0xFFFA//-6
114 SQL_BIT = 0xFFF9//-7
115 SQL_WCHAR = 0xFFF8//-8
116 SQL_WVARCHAR = 0xFFF7//-9
117 SQL_WLONGVARCHAR = 0xFFF6//-10
118 SQL_TYPE_DATE = 91
119 SQL_TYPE_TIME = 92
120 SQL_TYPE_TIMESTAMP = 93
121
122
123 SQL_FETCH_NEXT = 1
124 SQL_FETCH_FIRST = 2
125 SQL_FETCH_LAST = 3
126 SQL_FETCH_PRIOR = 4
127 SQL_FETCH_ABSOLUTE = 5
128 SQL_FETCH_RELATIVE = 6
129 SQL_FETCH_BOOKMARK = 7
130
131 //Передача значений
132 SQL_ATTR_ROW_NUMBER = 14
133 SQL_IS_POINTER = -4
134 SQL_IS_UINTEGER = -5
135 SQL_IS_INTEGER = -6
136 SQL_NTS = -3
137
138 NUMERIC_SIZE = 19
139
140 SQL_PACKET_SIZE = 112
141 SQL_ATTR_PACKET_SIZE = 112
142 SQL_ATTR_ROW_ARRAY_SIZE = 27
143
144 MAXFIELDSIZE = 0x400000//255//1024
145 //SQL_DIAG_CURSOR_ROW_COUNT =-1249
146 }
147
148 type odbc {
149 //private
150 uint henv //Дескриптор ODBC
151 uint hconn //Дескриптор соединения
152 uint connected //Флаг есть соединение
153 //public
154 str connectstr //Отформатированная строка для соединения
155 str dsn //Название источника данных созданного в ODBC
156 str user //Пользователь для dsn
157 str psw //Пароль для dsn
158 uint hwnd //Дескриптор окна для запроса дополнительных данных
159 uint fprompt //Флаг возможности запроса дополнительных данных
160 uint packetsize
161 arr arrqueries of uint
162 }
163
164 method uint odbc.err ( uint type_handle handle, str state msg )
165 {
166 uint err
167 uint ret, res
168
169 state.reserve( 11 )
170 msg.reserve( 1025 )
171 SQLGetDiagRec( type_handle, handle, 1, state.ptr(), &err, msg.ptr(), 1024, &ret ) &0xFFFF
172 state.setlenptr( )
173 msg.setlen( ret & 0xFFFF )
174 //s = "Message: ErrCode=\(err), State=\( state ), Msg=\"\( msg )\" "
175 return err
176 }
177
178 /*-----------------------------------------------------------------------------
179 * Id: odbc_geterror F2
180 *
181 * Summary: Get the last error message. Gets the message if the last error
182 occured while connecting to the database.
183 *
184 * Params: state - This string will contain the current state.
185 message - This string will contain an error message.
186 *
187 * Return: Returns the last error code.
188 *
189 -----------------------------------------------------------------------------*/
190
191 method uint odbc.geterror( str state, str message )
192 {
193 return this.err( $SQL_HANDLE_DBC, this.hconn, state, message )
194 }
195
196 func uint chsql( uint res )
197 {
198 res &= 0xFFFF
199 if res == $SQL_SUCCESS || res == $SQL_SUCCESS_WITH_INFO : return 1
200 return 0
201 }
202
203 include
204 {
205 "odbcfield.g"
206 "odbcquery.g"
207 }
208
209 /*-----------------------------------------------------------------------------
210 * Id: odbc_disconnect F3
211 *
212 * Summary: Disconnect from a database.
213 *
214 -----------------------------------------------------------------------------*/
215
216 method odbc.disconnect()
217 {
218 if this.connected : SQLDisconnect( this.hconn )
219 if this.hconn : SQLFreeHandle( $SQL_HANDLE_DBC, this.hconn )
220 if this.henv : SQLFreeHandle( $SQL_HANDLE_ENV, this.henv )
221 this.connected = this.henv = this.hconn = 0
222 }
223
224 method uint odbc.connect()
225 {
226 uint res
227
228 this.henv = 0
229 this.hconn = 0
230 if ( chsql(
231 SQLAllocHandle( $SQL_HANDLE_ENV, this.henv , &this.henv )) &&
232 chsql(
233 SQLSetEnvAttr( this.henv, $SQL_ATTR_ODBC_VERSION, $SQL_OV_ODBC3, 0 )) &&
234 chsql( SQLAllocHandle( $SQL_HANDLE_DBC, this.henv, &this.hconn )))
235 {
236 SQLSetConnectAttr( this.hconn, $SQL_ATTR_PACKET_SIZE, $ODBC_POCKET_SIZE, $SQL_IS_UINTEGER )
237
238 if *this.connectstr
239 {
240 str out
241 uint outsize
242 out.reserve( 1024 )
243 res = SQLDriverConnect( this.hconn, ?( this.fprompt, this.hwnd, 0 ),
244 this.connectstr.ptr(), *this.connectstr, out.ptr(),
245 out->buf.size, &out->buf.use,
246 ?( this.fprompt, $SQL_DRIVER_COMPLETE , $SQL_DRIVER_NOPROMPT))
247 }
248 else
249 {
250 res = SQLConnect( this.hconn, this.dsn.ptr(), *this.dsn,
251 this.user.ptr(), *this.user, this.psw.ptr(), *this.psw )
252 }
253 /*res = SQLGetConnectAttr( this.hconn, $SQL_ATTR_PACKET_SIZE, &this.packetsize, $SQL_IS_POINTER, &res )
254 print( "packetsize=\(this.packetsize) \(res&0xFFFF)\n" )
255 str s
256 this.err( $SQL_HANDLE_DBC, this.hconn, s )
257 print( "res =\(res&0xffff) "+s +"\n") */
258 if chsql( res )
259 {
260 this.connected = 1
261 return 1
262 }
263 }
264 this.disconnect()
265 return 0
266 }
267
268 /*-----------------------------------------------------------------------------
269 * Id: odbc_connect F2
270 *
271 * Summary: Create a database connection. You can connect to a database using
272 a string connection or a DSN name. #p[ The method is called in
273 order to connect to the database with the help of the string
274 connection. Use The ODBC connection string for this purpose, that
275 contains a driver type, a database name and some additional
276 parameters. The example below shows a type of the string connected
277 to the SQL server: #b( '"Driver={SQL Server};Server=MSSQLSERVER;
278 Database=mydatabase;Trusted_Connection=yes;"') ]
279 *
280 * Params: connectstr - Connection string.
281 *
282 * Return: Returns 1 if the connection is successful; otherwise, returns 0.
283 *
284 -----------------------------------------------------------------------------*/
285
286 method uint odbc.connect( str connectstr )
287 {
288 this.connectstr = connectstr
289 return this.connect()
290 }
291
292 /*-----------------------------------------------------------------------------
293 * Id: odbc_connect_1 FA
294 *
295 * Summary: This method is used to connect to the database through the
296 previously defined connection (the DSN name).
297 *
298 * Params: dsn - Name of a previously defined connection - DSN.
299 user - User name.
300 psw - User password.
301 *
302 * Return: Returns 1 if the connection is successful; otherwise, returns 0.
303 *
304 -----------------------------------------------------------------------------*/
305
306 method uint odbc.connect( str dsn, str user, str psw )
307 {
308 this.dsn = dsn
309 this.user = user
310 this.psw = psw
311 return this.connect()
312 }
313
314 /*-----------------------------------------------------------------------------
315 * Id: odbc_newquery F3
316 *
317 * Summary: Create a new ODBC query. Creates a new ODBC query for the
318 particular ODBC connection. Several queries are likely to be
319 created for one connection. Queries are created inside the ODBC
320 object and deleted in case of its deletion.
321 *
322 * Return: A new ODBC query.
323 *
324 -----------------------------------------------------------------------------*/
325
326 method odbcquery odbc.newquery()
327 {
328 uint nq = *this.arrqueries
329 this.arrqueries.expand(1)
330 this.arrqueries[nq] = new( odbcquery )
331 this.arrqueries[nq]->odbcquery.setodbc( this )
332 return this.arrqueries[nq]->odbcquery
333 }
334
335 method odbc.delete()
336 {
337 foreach q, this.arrqueries
338 {
339 destroy( q )
340 }
341 }
342
343 /*-----------------------------------------------------------------------------
344 ** Id: odbc_desc F1
345 *
346 * Summary: A brief description of ODBC library. The object of the #b(odbc)
347 type provides connection to a database. The objects of the #b(odbcquery) type
348 are used to run SQL queries and move the cursor through a result set. This
349 object has got the #b(arr fields[] of odbcfield) array that contains result
350 set fields #b(odbcfield); furthermore, the number of elements of the array
351 equals the number of the fields.
352
353 #p[The objects of the #b(odbcfield) type make it possible to get the required
354 information of the field as well as the field's value (depending on the
355 current position of the cursor in the result set).]
356
357 #p[The sequence of operations for working with the database:]
358 #ul[
359 |create an ODBC connection to the database using the #a(odbc_connect) method;
360 create a new ODBC query using the #a(odbc_newquery) method. Note that several
361 |queries are likely to be created for one connection;
362 run a SQL query using the #a(odbcquery_run) method; the query may retrieve the
363 result set (the #b(SELECT) command) or no data (the #b(INSERT) command, the
364 | #b(UPDATE) command etc.);
365 move the cursor through the result set using the following methods:
366 #a(odbcquery_first), #a(odbcquery_next) etc. if necessary. The access is
367 gained to the fields through the fields array #b(odbcquery.fields[i]),
368 where i - a field number begining from 0, or with the
369 | #a(odbcquery_fieldbyname) method;
370 use the #a(odbcfield_getstr) method, the #a(odbcfield_getint) method etc.
371 |in order to get field values;
372 |run the next SQL query after processing if necessary;
373 |disconnect from the database using the ODBC method #a(odbc_disconnect).
374 ]
375 #p[There are some peculiarities to keep in mind when working with ODBC
376 drivers:#br#
377 while running a SQL query with the help of multiple sequential
378 statements of the "INSERT ..." type, only some of the query statements are
379 being executed (there can be from 300 to 1000 statements used for the "SQL
380 server" driver) and no error message is displayed. In this case, you had
381 better divide such queries into several parts;#br#
382 some types of drivers do not make it possible to calculate the total number
383 of messages received by the SQL query.]
384 *
385 * Title: ODBC description
386 *
387 * Define:
388 *
389 -----------------------------------------------------------------------------*/
390
391 //----------------------------------------------------------------------------
392