1/* 2 * iodbctest.c 3 * 4 * $Id: iodbctest.c,v 1.26 2007/09/04 06:20:47 source Exp $ 5 * 6 * Sample ODBC program 7 * 8 * The iODBC driver manager. 9 * 10 * Copyright (C) 1996-2006 by OpenLink Software <iodbc@openlinksw.com> 11 * All Rights Reserved. 12 * 13 * This software is released under the terms of either of the following 14 * licenses: 15 * 16 * - GNU Library General Public License (see LICENSE.LGPL) 17 * - The BSD License (see LICENSE.BSD). 18 * 19 * Note that the only valid version of the LGPL license as far as this 20 * project is concerned is the original GNU Library General Public License 21 * Version 2, dated June 1991. 22 * 23 * While not mandated by the BSD license, any patches you make to the 24 * iODBC source code may be contributed back into the iODBC project 25 * at your discretion. Contributions will benefit the Open Source and 26 * Data Access community as a whole. Submissions may be made at: 27 * 28 * http://www.iodbc.org 29 * 30 * 31 * GNU Library Generic Public License Version 2 32 * ============================================ 33 * This library is free software; you can redistribute it and/or 34 * modify it under the terms of the GNU Library General Public 35 * License as published by the Free Software Foundation; only 36 * Version 2 of the License dated June 1991. 37 * 38 * This library is distributed in the hope that it will be useful, 39 * but WITHOUT ANY WARRANTY; without even the implied warranty of 40 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 41 * Library General Public License for more details. 42 * 43 * You should have received a copy of the GNU Library General Public 44 * License along with this library; if not, write to the Free 45 * Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA 46 * 47 * 48 * The BSD License 49 * =============== 50 * Redistribution and use in source and binary forms, with or without 51 * modification, are permitted provided that the following conditions 52 * are met: 53 * 54 * 1. Redistributions of source code must retain the above copyright 55 * notice, this list of conditions and the following disclaimer. 56 * 2. Redistributions in binary form must reproduce the above copyright 57 * notice, this list of conditions and the following disclaimer in 58 * the documentation and/or other materials provided with the 59 * distribution. 60 * 3. Neither the name of OpenLink Software Inc. nor the names of its 61 * contributors may be used to endorse or promote products derived 62 * from this software without specific prior written permission. 63 * 64 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS 65 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT 66 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR 67 * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL OPENLINK OR 68 * CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, 69 * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, 70 * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR 71 * PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF 72 * LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING 73 * NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS 74 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. 75 */ 76 77 78#include <stdlib.h> 79#include <stdio.h> 80#include <string.h> 81#include <locale.h> 82 83#include <sql.h> 84#include <sqlext.h> 85#include <sqlucode.h> 86#include <iodbcext.h> 87 88/* 89 * Prototypes 90 */ 91int ODBC_Connect (char *connStr); 92int ODBC_Disconnect (void); 93int ODBC_Errors (char *where); 94int ODBC_Test (void); 95 96#define MAXCOLS 32 97 98 99#ifdef UNICODE 100 101#define TEXT(x) (SQLWCHAR *) L##x 102#define TEXTC(x) (SQLWCHAR) L##x 103#define TXTLEN(x) wcslen((wchar_t *) x) 104#define TXTCMP(x1,x2) wcscmp((wchar_t *) x1, (wchar_t *) x2) 105 106# ifdef WIN32 107#define OPL_A2W(a, w, cb) \ 108 MultiByteToWideChar(CP_ACP, 0, a, -1, w, cb) 109# else 110#define OPL_A2W(XA, XW, SIZE) mbstowcs(XW, XA, SIZE) 111# endif /* WIN32 */ 112 113#else 114 115#define TEXT(x) (SQLCHAR *) x 116#define TEXTC(x) (SQLCHAR) x 117#define TXTLEN(x) strlen((char *) x) 118#define TXTCMP(x1,x2) strcmp((char *) x1, (char *) x2) 119 120#endif /* UNICODE */ 121 122#define NUMTCHAR(X) (sizeof (X) / sizeof (SQLTCHAR)) 123 124 125/* 126 * Global variables 127 */ 128HENV henv = SQL_NULL_HANDLE; 129HDBC hdbc = SQL_NULL_HANDLE; 130HSTMT hstmt = SQL_NULL_HANDLE; 131 132int connected = 0; 133 134 135/* 136 * Unicode conversion routines 137 */ 138#ifdef UNICODE 139static SQLWCHAR * 140strcpy_A2W (SQLWCHAR * destStr, char *sourStr) 141{ 142 size_t length; 143 144 if (!sourStr || !destStr) 145 return destStr; 146 147 length = strlen (sourStr); 148 if (length > 0) 149 OPL_A2W (sourStr, destStr, length); 150 destStr[length] = L'\0'; 151 152 return destStr; 153} 154#endif 155 156 157 158/* 159 * Connect to the datasource 160 * 161 * The connect string can have the following parts and they refer to 162 * the values in the odbc.ini file 163 * 164 * DSN=<data source name> [mandatory] 165 * HOST=<server host name> [optional - value of Host] 166 * SVT=<database server type> [optional - value of ServerType] 167 * DATABASE=<database path> [optional - value of Database] 168 * OPTIONS=<db specific opts> [optional - value of Options] 169 * UID=<user name> [optional - value of LastUser] 170 * PWD=<password> [optional] 171 * READONLY=<N|Y> [optional - value of ReadOnly] 172 * FBS=<fetch buffer size> [optional - value of FetchBufferSize] 173 * 174 * Examples: 175 * 176 * HOST=star;SVT=SQLServer 2000;UID=demo;PWD=demo;DATABASE=pubs 177 * 178 * DSN=pubs_sqlserver;PWD=demo 179 */ 180 181SQLTCHAR outdsn[4096]; /* Store completed DSN for later use */ 182 183int 184ODBC_Connect (char *connStr) 185{ 186 short buflen; 187 SQLCHAR dataSource[1024]; 188 SQLTCHAR dsn[33]; 189 SQLTCHAR desc[255]; 190 SQLTCHAR driverInfo[255]; 191 SQLSMALLINT len1, len2; 192 int status; 193#ifdef UNICODE 194 SQLWCHAR wdataSource[1024]; 195#endif 196 197#if (ODBCVER < 0x0300) 198 if (SQLAllocEnv (&henv) != SQL_SUCCESS) 199 return -1; 200 201 if (SQLAllocConnect (henv, &hdbc) != SQL_SUCCESS) 202 return -1; 203#else 204 if (SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv) != SQL_SUCCESS) 205 return -1; 206 207 SQLSetEnvAttr (henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, 208 SQL_IS_UINTEGER); 209 210 if (SQLAllocHandle (SQL_HANDLE_DBC, henv, &hdbc) != SQL_SUCCESS) 211 return -1; 212#endif 213 214 215 /* 216 * Set the application name 217 */ 218 SQLSetConnectOption (hdbc, SQL_APPLICATION_NAME, 219 (SQLULEN) TEXT ("odbctest")); 220 221 222 /* 223 * Show the version number of the driver manager 224 */ 225 status = SQLGetInfo (hdbc, SQL_DM_VER, 226 driverInfo, sizeof (driverInfo), &len1); 227 if (status == SQL_SUCCESS) 228 { 229#ifdef UNICODE 230 printf ("Driver Manager: %S\n", driverInfo); 231#else 232 printf ("Driver Manager: %s\n", driverInfo); 233#endif 234 } 235 236 237 /* 238 * Either use the connect string provided on the command line or 239 * ask for one. If an empty string or a ? is given, show a nice 240 * list of options 241 */ 242 if (connStr && *connStr) 243 strcpy ((char *) dataSource, connStr); 244 else 245 while (1) 246 { 247 /* 248 * Ask for the connect string 249 */ 250 printf ("\nEnter ODBC connect string (? shows list): "); 251 if (fgets ((char *) dataSource, sizeof (dataSource), stdin) == NULL) 252 return 1; 253 254 /* 255 * Remove trailing '\n' 256 */ 257 dataSource[strlen ((char *) dataSource) - 1] = '\0'; 258 259 /* 260 * Check if the user wants to quit 261 */ 262 if (!strcmp ((char *)dataSource, "quit") || !strcmp ((char *)dataSource, "exit")) 263 return -1; 264 265 /* 266 * If the user entered something other than a ? 267 * break out of the while loop 268 */ 269 if (*dataSource && *dataSource != '?') 270 break; 271 272 273 /* 274 * Print headers 275 */ 276 fprintf (stderr, "\n%-32s | %-40s\n", "DSN", "Driver"); 277 fprintf (stderr, 278 "------------------------------------------------------------------------------\n"); 279 280 /* 281 * Goto the first record 282 */ 283 if (SQLDataSources (henv, SQL_FETCH_FIRST, 284 dsn, NUMTCHAR (dsn), &len1, 285 desc, NUMTCHAR (desc), &len2) != SQL_SUCCESS) 286 continue; 287 288 /* 289 * Show all records 290 */ 291 do 292 { 293#ifdef UNICODE 294 fprintf (stderr, "%-32S | %-40S\n", dsn, desc); 295#else 296 fprintf (stderr, "%-32s | %-40s\n", dsn, desc); 297#endif 298 } 299 while (SQLDataSources (henv, SQL_FETCH_NEXT, 300 dsn, NUMTCHAR (dsn), &len1, 301 desc, NUMTCHAR (desc), &len2) == SQL_SUCCESS); 302 } 303 304#ifdef UNICODE 305 strcpy_A2W (wdataSource, (char *) dataSource); 306 status = SQLDriverConnectW (hdbc, 0, (SQLWCHAR *) wdataSource, SQL_NTS, 307 (SQLWCHAR *) outdsn, NUMTCHAR (outdsn), &buflen, SQL_DRIVER_COMPLETE); 308 if (status != SQL_SUCCESS) 309 ODBC_Errors ("SQLDriverConnectW"); 310#else 311 status = SQLDriverConnect (hdbc, 0, (SQLCHAR *) dataSource, SQL_NTS, 312 (SQLCHAR *) outdsn, NUMTCHAR (outdsn), &buflen, SQL_DRIVER_COMPLETE); 313 if (status != SQL_SUCCESS) 314 ODBC_Errors ("SQLDriverConnect"); 315#endif 316 317 if (status != SQL_SUCCESS && status != SQL_SUCCESS_WITH_INFO) 318 return -1; 319 320 connected = 1; 321 322 323 /* 324 * Print out the version number and the name of the connected driver 325 */ 326 status = SQLGetInfo (hdbc, SQL_DRIVER_VER, 327 driverInfo, NUMTCHAR (driverInfo), &len1); 328 if (status == SQL_SUCCESS) 329 { 330#ifdef UNICODE 331 printf ("Driver: %S", driverInfo); 332#else 333 printf ("Driver: %s", driverInfo); 334#endif 335 336 status = SQLGetInfo (hdbc, SQL_DRIVER_NAME, 337 driverInfo, NUMTCHAR (driverInfo), &len1); 338 if (status == SQL_SUCCESS) 339 { 340#ifdef UNICODE 341 printf (" (%S)", driverInfo); 342#else 343 printf (" (%s)", driverInfo); 344#endif 345 } 346 printf ("\n"); 347 } 348 349 350 /* 351 * Show the list of supported functions in trace log 352 */ 353#if (ODBCVER < 0x0300) 354 { 355 SQLUSMALLINT exists[100]; 356 357 SQLGetFunctions (hdbc, SQL_API_ALL_FUNCTIONS, exists); 358 } 359#else 360 { 361 SQLUSMALLINT exists[SQL_API_ODBC3_ALL_FUNCTIONS_SIZE]; 362 363 SQLGetFunctions (hdbc, SQL_API_ODBC3_ALL_FUNCTIONS, exists); 364 } 365#endif 366 367 368 369 /* 370 * Allocate statement handle 371 */ 372#if (ODBCVER < 0x0300) 373 if (SQLAllocStmt (hdbc, &hstmt) != SQL_SUCCESS) 374 return -1; 375#else 376 if (SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &hstmt) != SQL_SUCCESS) 377 return -1; 378#endif 379 380 return 0; 381} 382 383 384/* 385 * Disconnect from the database 386 */ 387int 388ODBC_Disconnect (void) 389{ 390#if (ODBCVER < 0x0300) 391 if (hstmt) 392 SQLFreeStmt (hstmt, SQL_DROP); 393 394 if (connected) 395 SQLDisconnect (hdbc); 396 397 if (hdbc) 398 SQLFreeConnect (hdbc); 399 400 if (henv) 401 SQLFreeEnv (henv); 402#else 403 if (hstmt) 404 { 405 SQLCloseCursor (hstmt); 406 SQLFreeHandle (SQL_HANDLE_STMT, hstmt); 407 } 408 409 if (connected) 410 SQLDisconnect (hdbc); 411 412 if (hdbc) 413 SQLFreeHandle (SQL_HANDLE_DBC, hdbc); 414 415 if (henv) 416 SQLFreeHandle (SQL_HANDLE_ENV, henv); 417#endif 418 419 return 0; 420} 421 422 423/* 424 * Perform a disconnect/reconnect using the DSN stored from the original 425 * SQLDriverConnect 426 */ 427int 428ODBC_Reconnect (void) 429{ 430 SQLRETURN status; 431 SQLTCHAR buf[4096]; 432 SQLSMALLINT len; 433 434 /* 435 * Free old statement handle 436 */ 437#if (ODBCVER < 0x0300) 438 SQLFreeStmt (hstmt, SQL_DROP); 439#else 440 SQLFreeHandle (SQL_HANDLE_STMT, hstmt); 441#endif 442 443 /* 444 * Disconnect 445 */ 446 SQLDisconnect (hdbc); 447 448 /* 449 * Reconnect 450 */ 451 status = SQLDriverConnect (hdbc, 0, outdsn, SQL_NTS, 452 buf, sizeof (buf), &len, SQL_DRIVER_NOPROMPT); 453 454 /* 455 * Allocate new statement handle 456 */ 457 if (SQL_SUCCEEDED (status)) 458 { 459#if (ODBCVER < 0x0300) 460 status = SQLAllocStmt (hdbc, &hstmt); 461#else 462 status = SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &hstmt); 463#endif 464 } 465 466 /* 467 * Show why we where unsuccessful and return an error 468 */ 469 if (!SQL_SUCCEEDED (status)) 470 { 471 ODBC_Errors ("DriverConnect (reconnect)"); 472 return -1; 473 } 474 475 /* 476 * Success 477 */ 478 return 0; 479} 480 481 482/* 483 * Show all the error information that is available 484 */ 485int 486ODBC_Errors (char *where) 487{ 488 SQLTCHAR buf[512]; 489 SQLTCHAR sqlstate[15]; 490 SQLINTEGER native_error = 0; 491 int force_exit = 0; 492 SQLRETURN sts; 493 494#if (ODBCVER < 0x0300) 495 /* 496 * Get statement errors 497 */ 498 while (hstmt) 499 { 500 sts = SQLError (henv, hdbc, hstmt, sqlstate, &native_error, 501 buf, NUMTCHAR (buf), NULL); 502 if (!SQL_SUCCEEDED (sts)) 503 break; 504 505#ifdef UNICODE 506 fprintf (stderr, "%s = %S (%ld) SQLSTATE=%S\n", 507 where, buf, (long) native_error, sqlstate); 508#else 509 fprintf (stderr, "%s = %s (%ld) SQLSTATE=%s\n", 510 where, buf, (long) native_error, sqlstate); 511#endif 512 513 /* 514 * If the driver could not be loaded, there is no point in 515 * continuing, after reading all the error messages 516 */ 517 if (!TXTCMP (sqlstate, TEXT ("IM003"))) 518 force_exit = 1; 519 } 520 521 /* 522 * Get connection errors 523 */ 524 while (hdbc) 525 { 526 sts = SQLError (henv, hdbc, SQL_NULL_HSTMT, sqlstate, &native_error, 527 buf, NUMTCHAR (buf), NULL); 528 if (!SQL_SUCCEEDED (sts)) 529 break; 530 531#ifdef UNICODE 532 fprintf (stderr, "%s = %S (%ld) SQLSTATE=%S\n", 533 where, buf, (long) native_error, sqlstate); 534#else 535 fprintf (stderr, "%s = %s (%ld) SQLSTATE=%s\n", 536 where, buf, (long) native_error, sqlstate); 537#endif 538 539 /* 540 * If the driver could not be loaded, there is no point in 541 * continuing, after reading all the error messages 542 */ 543 if (!TXTCMP (sqlstate, TEXT ("IM003"))) 544 force_exit = 1; 545 } 546 547 /* 548 * Get environment errors 549 */ 550 while (henv) 551 { 552 sts SQLError (henv, SQL_NULL_HDBC, SQL_NULL_HSTMT, sqlstate, 553 &native_error, buf, NUMTCHAR (buf), NULL); 554 if (!SQL_SUCCEEDED (sts)) 555 break; 556 557#ifdef UNICODE 558 fprintf (stderr, "%s = %S (%ld) SQLSTATE=%S\n", 559 where, buf, (long) native_error, sqlstate); 560#else 561 fprintf (stderr, "%s = %s (%ld) SQLSTATE=%s\n", 562 where, buf, (long) native_error, sqlstate); 563#endif 564 565 /* 566 * If the driver could not be loaded, there is no point in 567 * continuing, after reading all the error messages 568 */ 569 if (!TXTCMP (sqlstate, TEXT ("IM003"))) 570 force_exit = 1; 571 } 572#else /* ODBCVER */ 573 int i; 574 575 /* 576 * Get statement errors 577 */ 578 i = 0; 579 while (hstmt && i < 5) 580 { 581 sts = SQLGetDiagRec (SQL_HANDLE_STMT, hstmt, ++i, 582 sqlstate, &native_error, buf, NUMTCHAR (buf), NULL); 583 if (!SQL_SUCCEEDED (sts)) 584 break; 585 586#ifdef UNICODE 587 fprintf (stderr, "%d: %s = %S (%ld) SQLSTATE=%S\n", 588 i, where, buf, (long) native_error, sqlstate); 589#else 590 fprintf (stderr, "%d: %s = %s (%ld) SQLSTATE=%s\n", 591 i, where, buf, (long) native_error, sqlstate); 592#endif 593 594 /* 595 * If the driver could not be loaded, there is no point in 596 * continuing, after reading all the error messages 597 */ 598 if (!TXTCMP (sqlstate, TEXT ("IM003"))) 599 force_exit = 1; 600 } 601 602 /* 603 * Get connection errors 604 */ 605 i = 0; 606 while (hdbc && i < 5) 607 { 608 sts = SQLGetDiagRec (SQL_HANDLE_DBC, hdbc, ++i, 609 sqlstate, &native_error, buf, NUMTCHAR (buf), NULL); 610 if (!SQL_SUCCEEDED (sts)) 611 break; 612 613#ifdef UNICODE 614 fprintf (stderr, "%d: %s = %S (%ld) SQLSTATE=%S\n", 615 i, where, buf, (long) native_error, sqlstate); 616#else 617 fprintf (stderr, "%d: %s = %s (%ld) SQLSTATE=%s\n", 618 i, where, buf, (long) native_error, sqlstate); 619#endif 620 621 /* 622 * If the driver could not be loaded, there is no point in 623 * continuing, after reading all the error messages 624 */ 625 if (!TXTCMP (sqlstate, TEXT ("IM003"))) 626 force_exit = 1; 627 } 628 629 /* 630 * Get environment errors 631 */ 632 i = 0; 633 while (henv && i < 5) 634 { 635 sts = SQLGetDiagRec (SQL_HANDLE_ENV, henv, ++i, 636 sqlstate, &native_error, buf, NUMTCHAR (buf), NULL); 637 if (!SQL_SUCCEEDED (sts)) 638 break; 639 640#ifdef UNICODE 641 fprintf (stderr, "%d: %s = %S (%ld) SQLSTATE=%S\n", 642 i, where, buf, (long) native_error, sqlstate); 643#else 644 fprintf (stderr, "%d: %s = %s (%ld) SQLSTATE=%s\n", 645 i, where, buf, (long) native_error, sqlstate); 646#endif 647 648 /* 649 * If the driver could not be loaded, there is no point in 650 * continuing, after reading all the error messages 651 */ 652 if (!TXTCMP (sqlstate, TEXT ("IM003"))) 653 force_exit = 1; 654 } 655#endif /* ODBCVER */ 656 657 /* 658 * Force an exit status 659 */ 660 if (force_exit) 661 exit (-1); 662 663 return -1; 664} 665 666 667/* 668 * Test program to run on the connected database 669 */ 670int 671ODBC_Test () 672{ 673 SQLTCHAR request[4096]; 674 SQLTCHAR fetchBuffer[1024]; 675 char buf[4096]; 676 size_t displayWidths[MAXCOLS]; 677 size_t displayWidth; 678 short numCols; 679 short colNum; 680 SQLTCHAR colName[50]; 681 SQLSMALLINT colType; 682 SQLULEN colPrecision; 683 SQLLEN colIndicator; 684 SQLSMALLINT colScale; 685 SQLSMALLINT colNullable; 686 unsigned long totalRows; 687 unsigned long totalSets; 688 int i; 689 SQLRETURN sts; 690 691 while (1) 692 { 693 /* 694 * Ask the user for a dynamic SQL statement 695 */ 696 printf ("\nSQL>"); 697 if (fgets (buf, sizeof (buf), stdin) == NULL) 698 break; 699 700#ifndef UNICODE 701 strcpy ((char *) request, (char *) buf); 702#else 703 strcpy_A2W (request, buf); 704#endif 705 706 request[TXTLEN (request) - 1] = TEXTC ('\0'); 707 708 if (request[0] == TEXTC ('\0')) 709 continue; 710 711 /* 712 * If the user just types tables, give him a list 713 */ 714 if (!TXTCMP (request, TEXT ("tables"))) 715 { 716 if (SQLTables (hstmt, NULL, 0, NULL, 0, NULL, 0, 717 NULL, 0) != SQL_SUCCESS) 718 { 719 ODBC_Errors ("SQLTables(tables)"); 720 continue; 721 } 722 } 723 /* 724 * If the user just types qualifiers, give him a list 725 */ 726 else if (!TXTCMP (request, TEXT ("qualifiers"))) 727 { 728 if (SQLTables (hstmt, TEXT ("%"), SQL_NTS, TEXT (""), 0, 729 TEXT (""), 0, TEXT (""), 0) != SQL_SUCCESS) 730 { 731 ODBC_Errors ("SQLTables(qualifiers)"); 732 continue; 733 } 734 } 735 /* 736 * If the user just types owners, give him a list 737 */ 738 else if (!TXTCMP (request, TEXT ("owners"))) 739 { 740 if (SQLTables (hstmt, TEXT (""), 0, TEXT ("%"), SQL_NTS, 741 TEXT (""), 0, TEXT (""), 0) != SQL_SUCCESS) 742 { 743 ODBC_Errors ("SQLTables(owners)"); 744 continue; 745 } 746 } 747 /* 748 * If the user just types "types", give him a list 749 */ 750 else if (!TXTCMP (request, TEXT ("types"))) 751 { 752 if (SQLTables (hstmt, TEXT (""), 0, TEXT (""), 0, 753 TEXT (""), 0, TEXT ("%"), SQL_NTS) != SQL_SUCCESS) 754 { 755 ODBC_Errors ("SQLTables(types)"); 756 continue; 757 } 758 } 759 /* 760 * If the user just types "datatypes", give him a list 761 */ 762 else if (!TXTCMP (request, TEXT ("datatypes"))) 763 { 764 if (SQLGetTypeInfo (hstmt, 0) != SQL_SUCCESS) 765 { 766 ODBC_Errors ("SQLGetTypeInfo"); 767 continue; 768 } 769 } 770 else if (!TXTCMP (request, TEXT ("reconnect"))) 771 { 772 if (ODBC_Reconnect()) 773 return -1; 774 775 continue; 776 } 777#if defined (unix) 778 else if (!TXTCMP (request, TEXT ("environment"))) 779 { 780 extern char **environ; 781 int i; 782 783 for (i = 0; environ[i]; i++) 784 fprintf (stderr, "%03d: [%s]\n", i, environ[i]); 785 786 continue; 787 } 788#endif 789 else if (!TXTCMP (request, TEXT ("quit")) 790 || !TXTCMP (request, TEXT ("exit"))) 791 break; /* If you want to quit, just say so */ 792 else 793 { 794 /* 795 * Prepare & Execute the statement 796 */ 797 if (SQLPrepare (hstmt, (SQLTCHAR *) request, 798 SQL_NTS) != SQL_SUCCESS) 799 { 800 ODBC_Errors ("SQLPrepare"); 801 continue; 802 } 803 if ((sts = SQLExecute (hstmt)) != SQL_SUCCESS) 804 { 805 ODBC_Errors ("SQLExec"); 806 807 if (sts != SQL_SUCCESS_WITH_INFO) 808 continue; 809 } 810 } 811 812 /* 813 * Loop through all the result sets 814 */ 815 totalSets = 1; 816 do 817 { 818 /* 819 * Get the number of result columns for this cursor. 820 * If it is 0, then the statement was probably a select 821 */ 822 if (SQLNumResultCols (hstmt, &numCols) != SQL_SUCCESS) 823 { 824 ODBC_Errors ("SQLNumResultCols"); 825 goto endCursor; 826 } 827 if (numCols == 0) 828 { 829 SQLLEN nrows = 0; 830 831 SQLRowCount (hstmt, &nrows); 832 printf ("Statement executed. %ld rows affected.\n", 833 nrows > 0 ? (long) nrows : 0L); 834 goto endCursor; 835 } 836 837 if (numCols > MAXCOLS) 838 { 839 numCols = MAXCOLS; 840 fprintf (stderr, 841 "NOTE: Resultset truncated to %d columns.\n", MAXCOLS); 842 } 843 844 /* 845 * Get the names for the columns 846 */ 847 putchar ('\n'); 848 for (colNum = 1; colNum <= numCols; colNum++) 849 { 850 /* 851 * Get the name and other type information 852 */ 853 if (SQLDescribeCol (hstmt, colNum, 854 (SQLTCHAR *) colName, NUMTCHAR (colName), NULL, 855 &colType, &colPrecision, &colScale, 856 &colNullable) != SQL_SUCCESS) 857 { 858 ODBC_Errors ("SQLDescribeCol"); 859 goto endCursor; 860 } 861 862 /* 863 * Calculate the display width for the column 864 */ 865 switch (colType) 866 { 867 case SQL_VARCHAR: 868 case SQL_CHAR: 869 case SQL_WVARCHAR: 870 case SQL_WCHAR: 871 case SQL_GUID: 872 displayWidth = colPrecision; 873 break; 874 875 case SQL_BINARY: 876 displayWidth = colPrecision * 2; 877 break; 878 879 case SQL_LONGVARCHAR: 880 case SQL_WLONGVARCHAR: 881 case SQL_LONGVARBINARY: 882 displayWidth = 30; /* show only first 30 */ 883 break; 884 885 case SQL_BIT: 886 displayWidth = 1; 887 break; 888 889 case SQL_TINYINT: 890 case SQL_SMALLINT: 891 case SQL_INTEGER: 892 case SQL_BIGINT: 893 displayWidth = colPrecision + 1; /* sign */ 894 break; 895 896 case SQL_DOUBLE: 897 case SQL_DECIMAL: 898 case SQL_NUMERIC: 899 case SQL_FLOAT: 900 case SQL_REAL: 901 displayWidth = colPrecision + 2; /* sign, comma */ 902 break; 903 904#ifdef SQL_TYPE_DATE 905 case SQL_TYPE_DATE: 906#endif 907 case SQL_DATE: 908 displayWidth = 10; 909 break; 910 911#ifdef SQL_TYPE_TIME 912 case SQL_TYPE_TIME: 913#endif 914 case SQL_TIME: 915 displayWidth = 8; 916 break; 917 918#ifdef SQL_TYPE_TIMESTAMP 919 case SQL_TYPE_TIMESTAMP: 920#endif 921 case SQL_TIMESTAMP: 922 displayWidth = 19; 923 if (colScale > 0) 924 displayWidth = displayWidth + colScale + 1; 925 break; 926 927 default: 928 displayWidths[colNum - 1] = 0; /* skip other data types */ 929 continue; 930 } 931 932 if (displayWidth < TXTLEN (colName)) 933 displayWidth = TXTLEN (colName); 934 if (displayWidth > NUMTCHAR (fetchBuffer) - 1) 935 displayWidth = NUMTCHAR (fetchBuffer) - 1; 936 937 displayWidths[colNum - 1] = displayWidth; 938 939 /* 940 * Print header field 941 */ 942#ifdef UNICODE 943 printf ("%-*.*S", displayWidth, displayWidth, colName); 944#else 945 printf ("%-*.*s", displayWidth, displayWidth, colName); 946#endif 947 if (colNum < numCols) 948 putchar ('|'); 949 } 950 putchar ('\n'); 951 952 /* 953 * Print second line 954 */ 955 for (colNum = 1; colNum <= numCols; colNum++) 956 { 957 for (i = 0; i < displayWidths[colNum - 1]; i++) 958 putchar ('-'); 959 if (colNum < numCols) 960 putchar ('+'); 961 } 962 putchar ('\n'); 963 964 /* 965 * Print all the fields 966 */ 967 totalRows = 0; 968 while (1) 969 { 970#if (ODBCVER < 0x0300) 971 int sts = SQLFetch (hstmt); 972#else 973 int sts = SQLFetchScroll (hstmt, SQL_FETCH_NEXT, 1); 974#endif 975 976 if (sts == SQL_NO_DATA_FOUND) 977 break; 978 979 if (sts != SQL_SUCCESS) 980 { 981 ODBC_Errors ("Fetch"); 982 break; 983 } 984 for (colNum = 1; colNum <= numCols; colNum++) 985 { 986 /* 987 * Fetch this column as character 988 */ 989#ifdef UNICODE 990 sts = SQLGetData (hstmt, colNum, SQL_C_WCHAR, fetchBuffer, 991 NUMTCHAR (fetchBuffer), &colIndicator); 992#else 993 sts = SQLGetData (hstmt, colNum, SQL_C_CHAR, fetchBuffer, 994 NUMTCHAR (fetchBuffer), &colIndicator); 995#endif 996 if (sts != SQL_SUCCESS_WITH_INFO && sts != SQL_SUCCESS) 997 { 998 ODBC_Errors ("SQLGetData"); 999 goto endCursor; 1000 } 1001 1002 /* 1003 * Show NULL fields as **** 1004 */ 1005 if (colIndicator == SQL_NULL_DATA) 1006 { 1007 for (i = 0; i < displayWidths[colNum - 1]; i++) 1008 fetchBuffer[i] = TEXTC ('*'); 1009 fetchBuffer[i] = TEXTC ('\0'); 1010 } 1011 1012#ifdef UNICODE 1013 printf ("%-*.*S", displayWidths[colNum - 1], 1014 displayWidths[colNum - 1], fetchBuffer); 1015#else 1016 printf ("%-*.*s", displayWidths[colNum - 1], 1017 displayWidths[colNum - 1], fetchBuffer); 1018#endif 1019 if (colNum < numCols) 1020 putchar ('|'); 1021 } 1022 putchar ('\n'); 1023 totalRows++; 1024 } 1025 1026 printf ("\n result set %lu returned %lu rows.\n\n", 1027 totalSets, totalRows); 1028 totalSets++; 1029 } 1030 while ((sts = SQLMoreResults (hstmt)) == SQL_SUCCESS); 1031 1032 if (sts == SQL_ERROR) 1033 ODBC_Errors ("SQLMoreResults"); 1034 1035 endCursor: 1036#if (ODBCVER < 0x0300) 1037 SQLFreeStmt (hstmt, SQL_CLOSE); 1038#else 1039 SQLCloseCursor (hstmt); 1040#endif 1041 } 1042 1043 return 0; 1044} 1045 1046 1047int 1048main (int argc, char **argv) 1049{ 1050 /* 1051 * Set locale based on environment variables 1052 */ 1053 setlocale (LC_ALL, ""); 1054 1055 /* 1056 * Show welcome message 1057 */ 1058#ifdef UNICODE 1059 printf ("iODBC Unicode Demonstration program\n"); 1060#else 1061 printf ("iODBC Demonstration program\n"); 1062#endif 1063 printf ("This program shows an interactive SQL processor\n"); 1064 1065 /* 1066 * Show a usage string when the user asks for this 1067 */ 1068 if (argc > 2 || (argc == 2 && argv[1][0] == '-')) 1069 { 1070 fprintf (stderr, 1071 "\nUsage:\n iodbctest [\"DSN=xxxx;UID=xxxx;PWD=xxxx\"]\n"); 1072 exit (0); 1073 } 1074 1075 /* 1076 * If we can connect to this datasource, run the test program 1077 */ 1078 if (ODBC_Connect (argv[1]) != 0) 1079 { 1080 ODBC_Errors ("ODBC_Connect"); 1081 } 1082 else if (ODBC_Test () != 0) 1083 { 1084 ODBC_Errors ("ODBC_Test"); 1085 } 1086 1087 /* 1088 * End the connection 1089 */ 1090 ODBC_Disconnect (); 1091 1092 printf ("\nHave a nice day."); 1093 1094 return 0; 1095} 1096