1 /*******************************************************************************
2 <fieldsql>
3 <fieldsql.g>
4 <copyright author="Alexander Krivonogov" year=2006
5 file="This file is part of the Gentee ODBC library."></>
6 <place root=Libraries curgroup="ODBC Library"></>
7 <description>
8 Definition of 'win' type.
9 </>
10 </>
11 *******************************************************************************/
12
13 include {
14 "odbcfield.g" }
15
16 type odbcquery {
17 //private
18 uint hstmt //Дескриптор курсора
19 uint hconn
20 uint podbc //Указатель на odbc
21 //public
22 uint rowcount //Количество строк результата
23 uint fieldcount //Количество полей
24 uint open //Флаг открытого запроса
25 str sqlstr //Строка запроса
26 arr fields of odbcfield
27 uint timeout
28 }
29 extern {
30 method uint odbcquery.first()
31 method uint odbcquery.geterror( str state message )
32 }
33
34 /*-----------------------------------------------------------------------------
35 * Id: odbcquery_close F3
36 *
37 * Summary: Close a result set. Closes a result set. This method is used after
38 the SQL query of the #b(SELECT...) type has been executed. While
39 calling the #a(odbcquery_run) method, the given method is
40 automatically called.
41 *
42 -----------------------------------------------------------------------------*/
43
44 method odbcquery.close()
45 {
46 if this.open
47 {
48 this.open = 0
49 SQLFreeStmt( this.hstmt, $SQL_UNBIND )
50 SQLFreeStmt( this.hstmt, $SQL_CLOSE )
51 }
52 }
53
54 method odbcquery.freeodbc()
55 {
56 this.close()
57 if this.hstmt
58 {
59 SQLFreeHandle( $SQL_HANDLE_STMT, this.hstmt )
60 this.hstmt = 0
61 }
62 this.hconn = 0
63 this.podbc = 0
64
65 }
66
67 method odbcquery.free()
68 {
69 this.freeodbc()
70 }
71
72 method uint odbcquery.setodbc( odbc podbc )
73 {
74 this.freeodbc()
75 this.podbc = &podbc
76 return 0
77 }
78
79 /*-----------------------------------------------------------------------------
80 * Id: odbcquery_settimeout F2
81 *
82 * Summary: Set query timeout. Sets the number of seconds to wait for a
83 SQL query execution.
84 *
85 * Params: timeout - The number of seconds to wait for a SQL query execution. /
86 If it is equal to 0, then there is no timeout.
87 *
88 -----------------------------------------------------------------------------*/
89
90 method odbcquery.settimeout( uint timeout )
91 {
92 this.timeout = timeout
93 if this.hstmt
94 {
95 SQLSetStmtAttr( this.hstmt, 0,//$SQL_ATTR_QUERY_TIMEOUT,
96 timeout, $SQL_IS_UINTEGER )
97 }
98 }
99
100 method uint odbcquery.run()
101 {
102 uint codbc
103 uint ret
104
105 codbc as this.podbc->odbc
106 this.close()
107 if codbc->uint && codbc.connected
108 {
109 if !this.hstmt || codbc.hconn != this.hconn
110 {
111 if this.hstmt : SQLFreeHandle( $SQL_HANDLE_STMT, this.hstmt )
112 this.hconn = codbc.hconn
113 if !chsql( SQLAllocHandle( $SQL_HANDLE_STMT, this.hconn, &this.hstmt ))
114 {
115 return 0
116 }
117 SQLSetStmtAttr( this.hstmt, $SQL_ATTR_CURSOR_TYPE,
118 $SQL_CURSOR_STATIC, $SQL_IS_INTEGER )
119 this.settimeout( this.timeout )
120 //SQLSetStmtOption( this.hstmt, $SQL_CURSOR_TYPE, $SQL_CURSOR_STATIC )
121 }
122 //if *this.sqlstr >= $ODBC_POCKET_SIZE : return 0
123
124 ret = SQLExecDirect( this.hstmt, this.sqlstr.ptr(), *this.sqlstr/*$SQL_NTS*/ ) & 0xFFFF
125 if ret == $SQL_NO_DATA : return 1
126 if ret == $SQL_SUCCESS || ret == $SQL_SUCCESS_WITH_INFO
127 {
128
129 //uint colnums
130 uint i
131 uint f
132 uint ctype
133 SQLNumResultCols( this.hstmt, &this.fieldcount )
134 /*
135 SQLFetchScroll( this.hstmt, $SQL_FETCH_LAST, 0 )
136 SQLGetStmtAttr( this.hstmt, $SQL_ATTR_ROW_NUMBER, &this.rowcount,
137 $SQL_IS_INTEGER, &ret )
138 */
139 //print( "ROWNCOUNT = \(this.rowcount)\n")
140 this.fields.expand( this.fieldcount )
141
142 fornum i = 0, this.fieldcount
143 {
144 uint collenname, coltype, coldec, colnull
145 f = &this.fields[i]
146 f as odbcfield
147 collenname = 256
148 f.name.reserve( collenname )
149 SQLDescribeCol( this.hstmt, i + 1, f.name.ptr(), collenname,
150 &collenname, &f.sqltype, &f.sqlsize, &f.sqldecdig, &f.sqlind )
151 f.name.setlen( collenname )
152 switch f.sqltype
153 {
154 case $SQL_INTEGER, $SQL_SMALLINT, $SQL_TINYINT, $SQL_BIT {
155 f.vtype = int
156 f.sqlsize = sizeof( int )
157 ctype = $SQL_INTEGER
158 }
159 case $SQL_CHAR, $SQL_VARCHAR, $SQL_LONGVARCHAR,
160 $SQL_WCHAR, $SQL_WVARCHAR, $SQL_WLONGVARCHAR {
161 f.vtype = str
162 ctype = $SQL_CHAR
163 //print( "col=\( i ); name=\( f.name ); sqlsize=\(f.sqlsize); sqlind=\(f.sqlind)\n" )
164 if f.sqlsize > $MAXFIELDSIZE
165 {
166 f.sqlind = f.sqlsize
167 f.sqlsize = 0
168 }
169 }
170 case $SQL_FLOAT, $SQL_REAL, $SQL_DOUBLE {
171 f.vtype = double
172 f.sqlsize = sizeof( double )
173 ctype = $SQL_DOUBLE
174 }
175 case $SQL_BIGINT {
176 f.vtype = long
177 f.sqlsize = sizeof( long )
178 ctype = $SQL_BIGINT
179 }
180 case $SQL_NUMERIC, $SQL_DECIMAL {
181 f.vtype = numeric
182 f.sqlsize = $NUMERIC_SIZE
183 ctype = $SQL_CHAR
184 }
185 case $SQL_BINARY, $SQL_VARBINARY, $SQL_LONGVARBINARY {
186 f.vtype = buf
187 ctype = $SQL_BINARY
188 if f.sqlsize > $MAXFIELDSIZE
189 {
190 f.sqlind = f.sqlsize
191 f.sqlsize = 0
192 }
193 }
194 case $SQL_TYPE_DATE, $SQL_TYPE_TIME, $SQL_TYPE_TIMESTAMP {
195 f.vtype = datetime
196 f.sqlsize = sizeof( timestamp )
197 ctype = $SQL_TYPE_TIMESTAMP
198 }
199 default {
200 f.vtype = buf
201 ctype = f.sqltype
202 }
203 }
204 f.val->buf.expand( f.sqlsize + 1 )
205 f.hstmt = this.hstmt
206 f.index = i
207 if f.sqlsize
208 {
209 SQLBindCol( this.hstmt, i + 1, ctype, f.val.ptr(),
210 f.sqlsize + 1, &f.sqlind )
211 }
212 }
213 //SQLFetchScroll( this.hstmt, $SQL_FETCH_FIRST, 0 )
214 this.open = 1
215 this.first()
216 return 1
217 }
218 }
219 return 0
220 }
221
222 /*-----------------------------------------------------------------------------
223 * Id: odbcquery_run F2
224 *
225 * Summary: SQL query execution.
226 *
227 * Params: sqlstr - String that contains the SQL query.
228 *
229 * Return: #lng/retf#
230 *
231 -----------------------------------------------------------------------------*/
232
233 method uint odbcquery.run( str sqlstr )
234 {
235 this.sqlstr = sqlstr
236 return this.run()
237 }
238
239 method odbcquery.fieldsnull()
240 {
241 uint i
242 fornum i=0, this.fieldcount
243 {
244 if !this.fields[i].sqlsize
245 {
246 SQLGetData( this.hstmt, i + 1, this.fields[i].sqltype, this.fields[i].val.ptr(),
247 0, &this.fields[i].sqlind )
248 this.fields[i].val.setlen( 0 )
249 }
250 }
251 }
252
253 /*-----------------------------------------------------------------------------
254 * Id: odbcquery_next F3
255 *
256 * Summary: Move the cursor to the next record in the result set.
257 *
258 * Return: If the cursor has been moved, it returns nonzero; otherwise,
259 it returns zero. If the current record is the last, it returns zero.
260 *
261 -----------------------------------------------------------------------------*/
262
263 method uint odbcquery.next()
264 {
265 uint res = chsql( SQLFetch( this.hstmt ))
266 if res : this.fieldsnull()
267 return res
268 }
269
270 /*-----------------------------------------------------------------------------
271 * Id: odbcquery_first F3
272 *
273 * Summary: Move the cursor to the first record in the result set.
274 *
275 * Return: If the cursor has been moved, it returns nonzero.
276 *
277 -----------------------------------------------------------------------------*/
278
279 method uint odbcquery.first()
280 {
281 uint res = chsql( SQLFetchScroll( this.hstmt, $SQL_FETCH_FIRST, 0 ))
282 if res : this.fieldsnull()
283 return res
284 }
285
286 /*-----------------------------------------------------------------------------
287 * Id: odbcquery_last F3
288 *
289 * Summary: Move the cursor to the last record in the result set.
290 *
291 * Return: If the cursor has been moved, it returns nonzero.
292 *
293 -----------------------------------------------------------------------------*/
294
295 method uint odbcquery.last()
296 {
297 uint res = chsql( SQLFetchScroll( this.hstmt, $SQL_FETCH_LAST, 0 ))
298 if res : this.fieldsnull()
299 return res
300 }
301
302 /*-----------------------------------------------------------------------------
303 * Id: odbcquery_prior F3
304 *
305 * Summary: Move the cursor to the prior record in the result set.
306 *
307 * Return: If the cursor has been moved, it returns nonzero.
308 *
309 -----------------------------------------------------------------------------*/
310
311 method uint odbcquery.prior()
312 {
313 uint res = chsql( SQLFetchScroll( this.hstmt, $SQL_FETCH_PRIOR, 0 ))
314 if res : this.fieldsnull()
315 return res
316 }
317
318 /*-----------------------------------------------------------------------------
319 * Id: odbcquery_moveby F2
320 *
321 * Summary: Move the cursor to a position relative to its current position.
322 *
323 * Params: off - Indicates the number of records to move the cursor. If the /
324 number is negative, the cursor is moved backward.
325 *
326 * Return: If the cursor has been moved, it returns nonzero.
327 *
328 -----------------------------------------------------------------------------*/
329
330 method uint odbcquery.moveby( int off )
331 {
332 uint res = chsql( SQLFetchScroll( this.hstmt, $SQL_FETCH_RELATIVE, off ))
333 if res : this.fieldsnull()
334 return res
335 }
336
337 /*-----------------------------------------------------------------------------
338 * Id: odbcquery_fieldbyname F2
339 *
340 * Summary: Find a field based on a specified field name.
341 *
342 * Params: name - Field name.
343 *
344 * Return: Returns the field or zero if fields with the same name are
345 not found.
346 *
347 -----------------------------------------------------------------------------*/
348
349 method odbcfield odbcquery.fieldbyname( str name )
350 {
351 uint i
352 fornum i = 0, *this.fields
353 {
354 if this.fields[i].name == name : return this.fields[i]
355 }
356 return 0->odbcfield
357 }
358
359 /*-----------------------------------------------------------------------------
360 * Id: odbcquery_geterror F2
361 *
362 * Summary: Get the last error message. Gets the message if the last
363 error occured while running the SQL query.
364 *
365 * Params: state - This string will contain the current state.
366 message - This string will contain an error message.
367 *
368 * Return: Returns the last error code.
369 *
370 -----------------------------------------------------------------------------*/
371
372 method uint odbcquery.geterror( str state, str message )
373 {
374 return this.podbc->odbc.err( $SQL_HANDLE_STMT, this.hstmt, state, message )
375 }
376
377 /*-----------------------------------------------------------------------------
378 * Id: odbcquery_active F3
379 *
380 * Summary: Checks whether a result set exists after the SQL query execution.
381 If the SQL query of the #b('"SELECT ..."') type has been executed
382 successfully, this method returns nonzero.
383 *
384 * Return: Returns nonzero if a result set exists.
385 *
386 -----------------------------------------------------------------------------*/
387
388 method uint odbcquery.active()
389 {
390 return this.open
391 }
392
393 /*-----------------------------------------------------------------------------
394 ** Id: odbcquery_getrecordcount F3
395 *
396 * Summary: Get the total number of records in a result set. Gets the total
397 number of records in a result set when the SQL query of the
398 #b('"SELECT ..."') type has been executed.
399 *
400 * Return: Returns the the total number of records; if the total number
401 of records is not determined, it returns -1.
402 *
403 -----------------------------------------------------------------------------*/
404
405 method uint odbcquery.getrecordcount()
406 {
407 uint curpos, rowcount = -1
408 uint res, ret
409
410 if res = chsql( SQLGetStmtAttr( this.hstmt, $SQL_ATTR_ROW_NUMBER, &curpos,
411 $SQL_IS_INTEGER, &ret ))
412 {
413 if res = chsql( SQLFetchScroll( this.hstmt, $SQL_FETCH_LAST, 0 ) )
414 {
415 res = chsql( SQLGetStmtAttr( this.hstmt, $SQL_ATTR_ROW_NUMBER, &rowcount,
416 $SQL_IS_INTEGER, &ret ))
417 SQLFetchScroll( this.hstmt, $SQL_FETCH_ABSOLUTE, curpos )
418 }
419 }
420 if res : return rowcount
421 return -1
422 }
423