EnglishРусский  

   ..

   odbc.g

   odbcquery.g

The project is closed! You can look at a new scripting language. It is available on GitHub.
Also, try our open source cross-platform automation software.

Ads

Installer and installation software
Commercial and Freeware installers.

source\lib\odbc\odbcquery.g
  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