EnglishРусский  

   ..

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