2 single: ODBC Functions; Introduction
8 This chapter contains the ODBC functions provided by the Ring programming language.
26 Before using the next function load the odbclib.ring library
34 pair: ODBC Functions; odbc_init()
39 We can create ODBC Handle using the odbc_init() function
45 odbc_init() ---> ODBC Handle
48 pair: ODBC Functions; odbc_drivers()
50 odbc_drivers() Function
51 =======================
53 We can get a list of ODBC drivers using the odbc_drivers() function
59 odbc_drivers(ODBC Handle) ---> List of Drivers
62 pair: ODBC Functions; odbc_datasources()
64 odbc_datasources() Function
65 ===========================
67 We can get a list of ODBC data sources using the odbc_datasources() function
73 odbc_datasources(ODBC Handle) ---> List of Data sources
76 pair: ODBC Functions; odbc_close()
81 After the end of using ODBC functions we can free resources using ODBC_Close() function
87 odbc_close(ODBC Handle)
90 pair: ODBC Functions; Print List of ODBC Drivers
92 Print List of ODBC Drivers
93 ==========================
95 The next example print a list of ODBC drivers.
99 See "ODBC test 1" + nl
102 see odbc_drivers(oODBC)
111 Microsoft Access-Treiber (*.mdb) - SQLLevel=0
112 Driver do Microsoft Paradox (*.db ) - SQLLevel=0
113 Driver do Microsoft Excel(*.xls) - SQLLevel=0
114 Microsoft Text Driver (*.txt; *.csv) - SQLLevel=0
115 Driver da Microsoft para arquivos texto (*.txt; *.csv) - SQLLevel=0
116 Microsoft dBase-Treiber (*.dbf) - SQLLevel=0
117 SQL Server - CPTimeout=60
118 Microsoft Excel Driver (*.xls) - SQLLevel=0
119 Driver do Microsoft dBase (*.dbf) - SQLLevel=0
120 Microsoft Paradox-Treiber (*.db ) - SQLLevel=0
121 Microsoft ODBC for Oracle - CPTimeout=120
122 Microsoft Text-Treiber (*.txt; *.csv) - SQLLevel=0
123 Microsoft Excel-Treiber (*.xls) - SQLLevel=0
124 Microsoft Access Driver (*.mdb) - SQLLevel=0
125 Driver do Microsoft Access (*.mdb) - SQLLevel=0
126 Microsoft Paradox Driver (*.db ) - SQLLevel=0
127 Microsoft dBase Driver (*.dbf) - SQLLevel=0
128 Microsoft Access Driver (*.mdb, *.accdb) - UsageCount=3
129 Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb) - UsageCount=3
130 Microsoft Access Text Driver (*.txt, *.csv) - UsageCount=3
131 SQL Server Native Client 10.0 - UsageCount=1
132 SQL Server Native Client 11.0 - UsageCount=1
133 Microsoft Access dBASE Driver (*.dbf, *.ndx, *.mdx) - UsageCount=3
134 Microsoft Access Paradox Driver (*.db) - UsageCount=3
135 MySQL ODBC 5.3 ANSI Driver - UsageCount=1
136 MySQL ODBC 5.3 Unicode Driver - UsageCount=1
137 ODBC Driver 11 for SQL Server - UsageCount=1
138 Lianja ODBC Driver - CPTimeout=60
139 Microsoft Visual FoxPro Driver - UsageCount=1
140 Microsoft Visual FoxPro-Treiber - UsageCount=1
141 Driver para o Microsoft Visual FoxPro - UsageCount=1
142 Microsoft FoxPro VFP Driver (*.dbf) - UsageCount=1
145 pair: ODBC Functions; Print List of ODBC Data Sources
147 Print List of ODBC Data Sources
148 ===============================
150 The next example print a list of ODBC data sources.
154 See "ODBC test 2" + nl
156 See "Data Sources " + nl
157 see odbc_datasources(pODBC)
166 Excel Files - Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)
167 MS Access Database - Microsoft Access Driver (*.mdb, *.accdb)
168 Customer - Microsoft Access Driver (*.mdb)
169 IdCardData - Microsoft Access Driver (*.mdb)
170 MyProjectData2 - Microsoft Access Driver (*.mdb)
171 MyData - Microsoft Access Driver (*.mdb)
172 MonprojetData - Microsoft Access Driver (*.mdb)
173 dBASE Files - Microsoft Access dBASE Driver (*.dbf, *.ndx, *.mdx)
174 myvfpdata - Microsoft Visual FoxPro Driver
175 FACTORYDATA - Microsoft Access Driver (*.mdb)
176 TRAININGSYSDATA - Microsoft Access Driver (*.mdb)
177 RVCSYSDATASQLDB - SQL Server Native Client 11.0
178 PWCTRVCDATA - Microsoft Access Driver (*.mdb)
179 MyCompany - Microsoft Access Driver (*.mdb)
180 HCS - Microsoft Access Driver (*.mdb)
181 HCS2 - Microsoft Access Driver (*.mdb, *.accdb)
182 MyProjectData - Microsoft Access Driver (*.mdb)
183 Xtreme Sample Database 2008 - Microsoft Access Driver (*.mdb)
184 Lianja_Southwind - Lianja ODBC Driver
185 Visual FoxPro Database - Microsoft Visual FoxPro Driver
186 Visual FoxPro Tables - Microsoft Visual FoxPro Driver
189 pair: ODBC Functions; odbc_connect()
191 odbc_connect() Function
192 =======================
194 We can connect to the database using the odbc_connect() function.
200 odbc_connect(ODBC Handle, cConnectionString)
203 pair: ODBC Functions; odbc_disconnect()
205 odbc_disconnect() Function
206 ==========================
208 We can close the connection to the database using the odbc_disconnect() function.
214 odbc_disconnect(ODBC Handle)
217 pair: ODBC Functions; Open and Close Connection
219 Open and Close Connection
220 =========================
222 The next example connect to the database then close the connection
226 See "ODBC test 3" + nl
228 See "Connect to database" + nl
229 see odbc_connect(pODBC,"DBQ=test.mdb;Driver={Microsoft Access Driver (*.mdb)}") + nl
230 See "disconnect" + nl
231 odbc_disconnect(pODBC)
232 See "Close database..." + nl
246 pair: ODBC Functions; odbc_execute()
248 odbc_execute() Function
249 =======================
251 We can execute SQL Statements on the database using the odbc_execute() function.
257 odbc_execute(ODBC Handle, cSQLStatement)
260 pair: ODBC Functions; odbc_colcount()
262 odbc_colcount() Function
263 ========================
265 We can get columns count in the query result using the odbc_colcount() function.
271 odbc_colcount(ODBC Handle) ---> Columns Count as Number
274 pair: ODBC Functions; odbc_fetch()
276 odbc_fetch() Function
277 =====================
279 We can fetch a row from the query result using the odbc_fetch() function.
285 odbc_fetch(ODBC Handle)
288 pair: ODBC Functions; odbc_getdata()
290 odbc_getdata() Function
291 =======================
293 We can get column value from the fetched row using the odbc_getdata() function.
299 odbc_getdata(ODBC Handle, nColumnNumber) ---> Column Value
302 pair: ODBC Functions; Execute Query and Print Result
304 Execute Query and Print Result
305 ==============================
307 The next example execute query then print the query result.
311 See "ODBC test 4" + nl
313 See "Connect to database" + nl
314 see odbc_connect(pODBC,"DBQ=test.mdb;Driver={Microsoft Access Driver (*.mdb)}") + nl
315 See "Select data" + nl
316 see odbc_execute(pODBC,"select * from person") + nl
317 nMax = odbc_colcount(pODBC)
318 See "Columns Count : " + nMax + nl
319 while odbc_fetch(pODBC)
322 see odbc_getdata(pODBC,x) + " - "
325 See "Close database..." + nl
326 odbc_disconnect(pODBC)
330 pair: ODBC Functions; odbc_tables()
332 odbc_tables() Function
333 ======================
335 We can get a list of tables inside the database using the odbc_tables() function.
337 We can access the result of this function as we get any query result.
343 odbc_tables(ODBC Handle)
349 See "ODBC test - Get Database Tables" + nl
351 See "Connect to database" + nl
352 odbc_connect(pODBC,"DBQ=test.mdb;Driver={Microsoft Access Driver (*.mdb)}") + nl
353 See "Select data" + nl
354 odbc_tables(pODBC) + nl
355 nMax = odbc_colcount(pODBC)
356 See "Columns Count : " + nMax + nl
357 while odbc_fetch(pODBC)
359 see odbc_getdata(pODBC,x)
360 if x != nMax see " - " ok
364 See "Close database..."
365 odbc_disconnect(pODBC)
372 ODBC test - Get Database Tables
376 .\test - NULL - Customer - TABLE - NULL
377 .\test - NULL - employee - TABLE - NULL
378 .\test - NULL - person - TABLE - NULL
379 .\test - NULL - tel - TABLE - NULL
383 pair: ODBC Functions; odbc_columns()
385 odbc_columns() Function
386 =======================
388 We can get a list of columns inside the table using the odbc_columns() function.
394 odbc_columns(ODBC Handle, cTableName)
400 See "ODBC test - Get Table Columns" + nl
402 See "Connect to database" + nl
403 odbc_connect(pODBC,"DBQ=test.mdb;Driver={Microsoft Access Driver (*.mdb)}") + nl
404 See "Get Columns inside the Person Table" + nl
405 odbc_columns(pODBC,"person") + nl
406 while odbc_fetch(pODBC)
407 see odbc_getdata(pODBC,4) + nl
409 See "Close database..." + nl
410 odbc_disconnect(pODBC)
417 ODBC test - Get Table Columns
419 Get Columns inside the Person Table
434 pair: ODBC Functions; odbc_autocommit()
436 odbc_autocommit() Function
437 ==========================
439 We can enable or disable the auto commit feature using the odbc_autocommit() function.
445 odbc_autocommit(ODBC Handle, lStatus) # lStatus can be True or False
449 pair: ODBC Functions; odbc_commit()
451 odbc_commit() Function
452 ======================
454 We can commit updates to the database using the odbc_commit() function.
460 odbc_commit(ODBC Handle)
463 pair: ODBC Functions; odbc_rollback()
465 odbc_rollback() Function
466 ========================
468 We can rollback updates to the database using the odbc_rollback() function.
474 odbc_rollback(ODBC Handle)
477 pair: ODBC Functions; Transactions and Using Commit and Rollback
479 Transactions and Using Commit and Rollback
480 ==========================================
486 See "ODBC Test - Transactions and using Commit and Rollback" + nl
488 See "Connect to database" + nl
489 see odbc_connect(pODBC,"DBQ=test.mdb;Driver={Microsoft Access Driver (*.mdb)}") + nl
490 see "insert data..." + nl
491 odbc_autocommit(pODBC,0)
493 odbc_execute(pODBC,"insert into tel values (" + x + ",'mahmoud')")
495 for x = 10001 to 15000
496 odbc_execute(pODBC,"insert into tel values (" + x + ",'samir')")
500 for x = 15001 to 20000
501 odbc_execute(pODBC,"insert into tel values (" + x + ",'fayed')")
505 odbc_execute(pODBC,"insert into tel values (" + x + ",'fayed')")
508 See "Close database..." + nl
509 odbc_disconnect(pODBC)
516 ODBC Test - Transactions and using Commit and Rollback
523 pair: ODBC Functions; Save and Restore Images
525 Save and Restore images
526 =======================
528 The next example save an image inside the database
532 See "ODBC test - Save image in the database" + nl
534 See "Connect to database" + nl
535 see odbc_connect(pODBC,"DBQ=test.mdb;Driver={Microsoft Access Driver (*.mdb)}") + nl
536 see "Read Image File..." + nl
537 cFile = str2hex(read("tests\mahmoud.jpg"))
538 see "size " + len(CFile)+nl
539 see "Save image in the database..." + nl
540 stmt = "insert into tel values (20000,'mahmoud','" + cFile + "');"
541 odbc_execute(pODBC,stmt)
542 See "Close database..." + nl
543 odbc_disconnect(pODBC)
546 The next example restore the image from the database
550 See "ODBC Test - Restore image from the database" + nl
552 See "Connect to database" + nl
553 see odbc_connect(pODBC,"DBQ=test.mdb;Driver={Microsoft Access Driver (*.mdb)}") + nl
554 See "Select data" + nl
555 see odbc_execute(pODBC,"select * from tel where id = 20000") + nl
556 nMax = odbc_colcount(pODBC)
557 See "Columns Count : " + nMax + nl
559 See "Write image file" + nl
560 write("tests\great.jpg",hex2str( odbc_getdata(pODBC,3) ) )
562 See "Close database..." + nl
563 odbc_disconnect(pODBC)