LCOV - code coverage report
Current view: top level - clients/odbc/samples - odbcsample1.c (source / functions) Hit Total Coverage
Test: coverage.info Lines: 159 206 77.2 %
Date: 2021-10-13 02:24:04 Functions: 2 3 66.7 %

          Line data    Source code
       1             : /*
       2             :  * This Source Code Form is subject to the terms of the Mozilla Public
       3             :  * License, v. 2.0.  If a copy of the MPL was not distributed with this
       4             :  * file, You can obtain one at http://mozilla.org/MPL/2.0/.
       5             :  *
       6             :  * Copyright 1997 - July 2008 CWI, August 2008 - 2021 MonetDB B.V.
       7             :  */
       8             : 
       9             : #ifdef _MSC_VER
      10             : /* suppress deprecation warning for snprintf */
      11             : #define _CRT_SECURE_NO_WARNINGS
      12             : 
      13             : #include <WTypes.h>
      14             : #endif
      15             : #include <stdio.h>
      16             : #include <stdlib.h>
      17             : #include <stdint.h>
      18             : #include <string.h>
      19             : #include <sql.h>
      20             : #include <sqlext.h>
      21             : 
      22             : static void
      23           0 : prerr(SQLSMALLINT tpe, SQLHANDLE hnd, const char *func, const char *pref)
      24             : {
      25             :         SQLCHAR state[6];
      26             :         SQLINTEGER errnr;
      27             :         SQLCHAR msg[256];
      28             :         SQLSMALLINT msglen;
      29             : 
      30           0 :         switch (SQLGetDiagRec(tpe, hnd, 1, state, &errnr, msg, sizeof(msg), &msglen)) {
      31           0 :         case SQL_SUCCESS_WITH_INFO:
      32           0 :                 if (msglen >= (signed int) sizeof(msg))
      33           0 :                         fprintf(stderr, "(message truncated)\n");
      34             :                 /* fall through */
      35             :         case SQL_SUCCESS:
      36           0 :                 fprintf(stderr, "%s: %s: SQLstate %s, Errnr %d, Message %s\n", func, pref, (char*)state, (int)errnr, (char*)msg);
      37           0 :                 break;
      38           0 :         case SQL_INVALID_HANDLE:
      39           0 :                 fprintf(stderr, "%s: %s, invalid handle passed to error function\n", func, pref);
      40           0 :                 break;
      41           0 :         case SQL_ERROR:
      42           0 :                 fprintf(stderr, "%s: %s, unexpected error from SQLGetDiagRec\n", func, pref);
      43           0 :                 break;
      44             :         case SQL_NO_DATA:
      45             :                 break;
      46           0 :         default:
      47           0 :                 fprintf(stderr, "%s: %s, weird return value from SQLGetDiagRec\n", func, pref);
      48           0 :                 break;
      49             :         }
      50           0 : }
      51             : 
      52             : static void
      53        4047 : check(SQLRETURN ret, SQLSMALLINT tpe, SQLHANDLE hnd, const char *func)
      54             : {
      55        4047 :         switch (ret) {
      56             :         case SQL_SUCCESS:
      57             :                 break;
      58           0 :         case SQL_SUCCESS_WITH_INFO:
      59           0 :                 prerr(tpe, hnd, func, "Info");
      60           0 :                 break;
      61           0 :         case SQL_ERROR:
      62           0 :                 prerr(tpe, hnd, func, "Error");
      63           0 :                 break;
      64           0 :         case SQL_INVALID_HANDLE:
      65           0 :                 fprintf(stderr, "%s: Error: invalid handle\n", func);
      66           0 :                 exit(1);
      67           0 :         default:
      68           0 :                 fprintf(stderr, "%s: Unexpected return value\n", func);
      69           0 :                 break;
      70             :         }
      71        4047 : }
      72             : 
      73             : int
      74           1 : main(int argc, char **argv)
      75             : {
      76             :         SQLHANDLE env;
      77             :         SQLHANDLE dbc;
      78             :         SQLHANDLE stmt, stmt2;
      79             :         char *dsn = "MonetDB";
      80             :         char *user = "monetdb";
      81             :         char *pass = "monetdb";
      82             :         SQLRETURN ret;
      83             :         int i;
      84             :         SQLSMALLINT f1;
      85             :         char f2[30], ff2[30];
      86             :         SQLDOUBLE f3;
      87             :         SQL_DATE_STRUCT f4, ff4;
      88             :         SQL_TIME_STRUCT f5, ff5;
      89             : 
      90           1 :         if (argc > 1)
      91           1 :                 dsn = argv[1];
      92           1 :         if (argc > 2)
      93           0 :                 user = argv[2];
      94           1 :         if (argc > 3)
      95           0 :                 pass = argv[3];
      96           1 :         if (argc > 4 || *dsn == '-') {
      97           0 :                 fprintf(stderr, "Usage: %s [datasource [user [password]]]\n", argv[0]);
      98           0 :                 exit(1);
      99             :         }
     100             : 
     101           1 :         if (SQLAllocHandle(SQL_HANDLE_ENV, NULL, &env) != SQL_SUCCESS) {
     102           0 :                 fprintf(stderr, "Cannot allocate ODBC environment handle\n");
     103           0 :                 exit(1);
     104             :         }
     105             : 
     106           1 :         ret = SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) (uintptr_t) SQL_OV_ODBC3, 0);
     107           1 :         check(ret, SQL_HANDLE_ENV, env, "SQLSetEnvAttr");
     108             : 
     109           1 :         ret = SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
     110           1 :         check(ret, SQL_HANDLE_ENV, env, "SQLAllocHandle (DBC)");
     111             : 
     112           1 :         ret = SQLConnect(dbc, (SQLCHAR *) dsn, SQL_NTS, (SQLCHAR *) user, SQL_NTS, (SQLCHAR *) pass, SQL_NTS);
     113           1 :         check(ret, SQL_HANDLE_DBC, dbc, "SQLConnect");
     114             : 
     115           1 :         ret = SQLSetConnectAttr(dbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER) (uintptr_t) SQL_AUTOCOMMIT_OFF, 0);
     116           1 :         check(ret, SQL_HANDLE_DBC, dbc, "SQLSetConnectAttr");
     117             : 
     118             :         /* create a test table to be filled with values */
     119           1 :         ret = SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
     120           1 :         check(ret, SQL_HANDLE_DBC, dbc, "SQLAllocHandle (STMT 1)");
     121             : 
     122           1 :         ret = SQLExecDirect(stmt, (SQLCHAR *)
     123             :                             "CREATE TABLE odbcsampletest (\n"
     124             :                             "   i INT DEFAULT '0' NOT NULL,\n"
     125             :                             "   s VARCHAR(200),\n"
     126             :                             "   f FLOAT,\n"
     127             :                             "   d DATE,\n"
     128             :                             "   t TIME\n"
     129             :                             ")", SQL_NTS);
     130           1 :         check(ret, SQL_HANDLE_STMT, stmt, "SQLExecDirect 1");
     131             : 
     132             :         /* figure out the type of the columns and bind parameters */
     133             :         {
     134             :                 SQLSMALLINT coltype;
     135             :                 SQLSMALLINT colno;
     136             : 
     137           1 :                 ret = SQLColumns(stmt, NULL, 0, NULL, 0, (SQLCHAR *) "odbcsampletest", SQL_NTS, NULL, 0);
     138           1 :                 check(ret, SQL_HANDLE_STMT, stmt, "SQLColumns");
     139           1 :                 ret = SQLBindCol(stmt, 5, SQL_C_SSHORT, &coltype, 0, NULL);
     140           1 :                 check(ret, SQL_HANDLE_STMT, stmt, "SQLBindCol 1");
     141           1 :                 ret = SQLBindCol(stmt, 17, SQL_C_SSHORT, &colno, 0, NULL);
     142           1 :                 check(ret, SQL_HANDLE_STMT, stmt, "SQLBindCol 2");
     143             : 
     144             :                 for (;;) {
     145           6 :                         ret = SQLFetch(stmt);
     146           6 :                         if (ret == SQL_NO_DATA)
     147             :                                 break;
     148           5 :                         check(ret, SQL_HANDLE_STMT, stmt, "SQLFetch");
     149           5 :                         switch (colno) {
     150           1 :                         case 1:
     151           1 :                                 ret = SQLBindParameter(stmt, 1, SQL_PARAM_INPUT, SQL_C_SSHORT, coltype, 0, 0, &f1, sizeof(f1), NULL);
     152           1 :                                 check(ret, SQL_HANDLE_STMT, stmt, "SQLBindParameter 1");
     153           1 :                                 break;
     154           1 :                         case 2:
     155           1 :                                 ret = SQLBindParameter(stmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, coltype, 0, 0, &f2, sizeof(f2), NULL);
     156           1 :                                 check(ret, SQL_HANDLE_STMT, stmt, "SQLBindParameter 2");
     157           1 :                                 break;
     158           1 :                         case 3:
     159           1 :                                 ret = SQLBindParameter(stmt, 3, SQL_PARAM_INPUT, SQL_C_DOUBLE, coltype, 0, 0, &f3, sizeof(f3), NULL);
     160           1 :                                 check(ret, SQL_HANDLE_STMT, stmt, "SQLBindParameter 3");
     161           1 :                                 break;
     162           1 :                         case 4:
     163           1 :                                 ret = SQLBindParameter(stmt, 4, SQL_PARAM_INPUT, SQL_C_TYPE_DATE, coltype, 0, 0, &f4, sizeof(f4), NULL);
     164           1 :                                 check(ret, SQL_HANDLE_STMT, stmt, "SQLBindParameter 4");
     165           1 :                                 break;
     166           1 :                         case 5:
     167           1 :                                 ret = SQLBindParameter(stmt, 5, SQL_PARAM_INPUT, SQL_C_TYPE_TIME, coltype, 0, 0, &f5, sizeof(f5), NULL);
     168           1 :                                 check(ret, SQL_HANDLE_STMT, stmt, "SQLBindParameter 5");
     169           1 :                                 break;
     170             :                         }
     171             :                 }
     172           1 :                 ret = SQLFreeStmt(stmt, SQL_UNBIND);
     173           1 :                 check(ret, SQL_HANDLE_STMT, stmt, "SQLFreeStmt");
     174           1 :                 ret = SQLCloseCursor(stmt);
     175           1 :                 check(ret, SQL_HANDLE_STMT, stmt, "SQLCloseCursor");
     176             :         }
     177             : 
     178             :         /* prepare for filling the test table */
     179             :         /* we use a single statement with parameters whose values vary */
     180             : 
     181           1 :         ret = SQLPrepare(stmt, (SQLCHAR *)
     182             :                          "INSERT INTO odbcsampletest VALUES (?, ?, ?, ?, ?)", SQL_NTS);
     183           1 :         check(ret, SQL_HANDLE_STMT, stmt, "SQLPrepare 1");
     184             : 
     185             :         /* do the actual filling of the test table */
     186           1 :         f4.year = 2003;
     187           1 :         f4.month = 1;
     188           1 :         f4.day = 1;
     189           1 :         f5.hour = 0;
     190           1 :         f5.minute = 0;
     191           1 :         f5.second = 0;
     192        2001 :         for (i = 0; i < 2000; i++) {
     193        2000 :                 f1 = i;
     194             :                 /* \342\200\230 is the UTF-8 encoding of U+2018 Left Single Quotation Mark;
     195             :                    \342\200\231 is the UTF-8 encoding of U+2019 Right Single Quotation Mark */
     196        2000 :                 snprintf(f2, sizeof(f2), "value \342\200\230%d\342\200\231", i);
     197        2000 :                 f3 = i * 1.5;
     198        2000 :                 f4.day++;
     199        2000 :                 if ((f4.day == 29 && f4.month == 2) || (f4.day == 31 && (f4.month == 4 || f4.month == 6 || f4.month == 9 || f4.month == 11)) || f4.day == 32) {
     200          65 :                         f4.day = 1;
     201          65 :                         f4.month++;
     202          65 :                         if (f4.month == 13) {
     203           5 :                                 f4.month = 1;
     204           5 :                                 f4.year++;
     205             :                         }
     206             :                 }
     207        2000 :                 f5.second++;
     208        2000 :                 if (f5.second == 60) {
     209          33 :                         f5.second = 0;
     210          33 :                         f5.minute++;
     211          33 :                         if (f5.minute == 60) {
     212           0 :                                 f5.minute = 0;
     213           0 :                                 f5.hour++;
     214           0 :                                 if (f5.hour == 25)
     215           0 :                                         f5.hour = 0;
     216             :                         }
     217             :                 }
     218        2000 :                 ret = SQLExecute(stmt);
     219        2000 :                 check(ret, SQL_HANDLE_STMT, stmt, "SQLExecute 1");
     220             :         }
     221             : 
     222           1 :         ret = SQLEndTran(SQL_HANDLE_DBC, dbc, SQL_COMMIT);
     223           1 :         check(ret, SQL_HANDLE_DBC, dbc, "SQLEndTran");
     224             : 
     225           1 :         ret = SQLSetConnectAttr(dbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER) (uintptr_t) SQL_AUTOCOMMIT_ON, 0);
     226           1 :         check(ret, SQL_HANDLE_DBC, dbc, "SQLSetConnectAttr");
     227             : 
     228             :         /* Now we are going to read back the values from the test table.
     229             :            We create two statment handles, one of which will be used
     230             :            to read the even table entries and the other for the odd
     231             :            table entries. */
     232             : 
     233           1 :         check(ret, SQL_HANDLE_DBC, dbc, "SQLAllocHandle 3");
     234             : 
     235             :         /* first the handle for the even entries */
     236             : 
     237             :         /* bind the columns before preparing the statement */
     238           1 :         ret = SQLBindCol(stmt, 1, SQL_C_SSHORT, &f1, sizeof(f1), NULL);
     239           1 :         check(ret, SQL_HANDLE_STMT, stmt, "SQLBindCol 1");
     240           1 :         ret = SQLBindCol(stmt, 2, SQL_C_CHAR, &f2, sizeof(f2), NULL);
     241           1 :         check(ret, SQL_HANDLE_STMT, stmt, "SQLBindCol 2");
     242           1 :         ret = SQLBindCol(stmt, 3, SQL_C_DOUBLE, &f3, sizeof(f3), NULL);
     243           1 :         check(ret, SQL_HANDLE_STMT, stmt, "SQLBindCol 3");
     244           1 :         ret = SQLBindCol(stmt, 4, SQL_C_TYPE_DATE, &f4, sizeof(f4), NULL);
     245           1 :         check(ret, SQL_HANDLE_STMT, stmt, "SQLBindCol 4");
     246           1 :         ret = SQLBindCol(stmt, 5, SQL_C_TYPE_TIME, &f5, sizeof(f5), NULL);
     247           1 :         check(ret, SQL_HANDLE_STMT, stmt, "SQLBindCol 5");
     248             : 
     249           1 :         ret = SQLPrepare(stmt, (SQLCHAR *) "SELECT * FROM odbcsampletest WHERE 2*(i/2) = i", SQL_NTS);
     250           1 :         check(ret, SQL_HANDLE_STMT, stmt, "SQLPrepare 2");
     251             : 
     252           1 :         ret = SQLExecute(stmt);
     253           1 :         check(ret, SQL_HANDLE_STMT, stmt, "SQLExecute 2");
     254             : 
     255             :         /* now the handle for the odd entries */
     256           1 :         ret = SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt2);
     257           1 :         check(ret, SQL_HANDLE_DBC, dbc, "SQLAllocHandle (STMT 2)");
     258             : 
     259           1 :         ret = SQLPrepare(stmt2, (SQLCHAR *) "SELECT * FROM odbcsampletest WHERE 2*(i/2) <> i", SQL_NTS);
     260           1 :         check(ret, SQL_HANDLE_STMT, stmt2, "SQLPrepare 3");
     261             : 
     262             :         /* bind the columns after preparing the statement */
     263           1 :         ret = SQLBindCol(stmt2, 1, SQL_C_SSHORT, &f1, sizeof(f1), NULL);
     264           1 :         check(ret, SQL_HANDLE_STMT, stmt2, "SQLBindCol 6");
     265           1 :         ret = SQLBindCol(stmt2, 2, SQL_C_CHAR, &f2, sizeof(f2), NULL);
     266           1 :         check(ret, SQL_HANDLE_STMT, stmt2, "SQLBindCol 7");
     267           1 :         ret = SQLBindCol(stmt2, 3, SQL_C_DOUBLE, &f3, sizeof(f3), NULL);
     268           1 :         check(ret, SQL_HANDLE_STMT, stmt2, "SQLBindCol 8");
     269           1 :         ret = SQLBindCol(stmt2, 4, SQL_C_TYPE_DATE, &f4, sizeof(f4), NULL);
     270           1 :         check(ret, SQL_HANDLE_STMT, stmt2, "SQLBindCol 9");
     271           1 :         ret = SQLBindCol(stmt2, 5, SQL_C_TYPE_TIME, &f5, sizeof(f5), NULL);
     272           1 :         check(ret, SQL_HANDLE_STMT, stmt2, "SQLBindCol 10");
     273             : 
     274           1 :         ret = SQLExecute(stmt2);
     275           1 :         check(ret, SQL_HANDLE_STMT, stmt2, "SQLExecute 3");
     276             : 
     277             :         i = 0;
     278             :         ff4 = (SQL_DATE_STRUCT) {
     279             :                 .year = 2003,
     280             :                 .month = 1,
     281             :                 .day = 1,
     282             :         };
     283             :         ff5 = (SQL_TIME_STRUCT) {
     284             :                 .hour = 0,
     285             :                 .minute = 0,
     286             :                 .second = 0,
     287             :         };
     288             :         for (;;) {
     289             :                 /* Alternate fetching an even and an odd entry.  The
     290             :                    end result should be that we get all entries in the
     291             :                    correct order. */
     292        1001 :                 ret = SQLFetchScroll(stmt, SQL_FETCH_NEXT, 0);
     293             : 
     294        1001 :                 if (ret == SQL_NO_DATA)
     295             :                         break;
     296        1000 :                 check(ret, SQL_HANDLE_STMT, stmt, "SQLFetch 1");
     297        1000 :                 snprintf(ff2, sizeof(ff2), "value \342\200\230%d\342\200\231", i);
     298        1000 :                 ff4.day++;
     299        1000 :                 if ((ff4.day == 29 && ff4.month == 2) || (ff4.day == 31 && (ff4.month == 4 || ff4.month == 6 || ff4.month == 9 || ff4.month == 11)) || ff4.day == 32) {
     300             :                         ff4.day = 1;
     301          33 :                         ff4.month++;
     302          33 :                         if (ff4.month == 13) {
     303             :                                 ff4.month = 1;
     304           3 :                                 ff4.year++;
     305             :                         }
     306             :                 }
     307        1000 :                 ff5.second++;
     308        1000 :                 if (ff5.second == 60) {
     309             :                         ff5.second = 0;
     310           0 :                         ff5.minute++;
     311           0 :                         if (ff5.minute == 60) {
     312             :                                 ff5.minute = 0;
     313           0 :                                 ff5.hour++;
     314           0 :                                 if (ff5.hour == 25)
     315             :                                         ff5.hour = 0;
     316             :                         }
     317             :                 }
     318        1000 :                 if (f1 != i || strcmp(f2, ff2) != 0 || f3 != i * 1.5 || f4.year != ff4.year || f4.month != ff4.month || f4.day != ff4.day || f5.hour != ff5.hour || f5.minute != ff5.minute || f5.second != ff5.second)
     319           0 :                         printf("%d %s %g %04d:%02d:%02d %02d-%02d-%02d\n", f1, f2, f3, f4.year, f4.month, f4.day, f5.hour, f5.minute, f5.second);
     320        1000 :                 i++;
     321             : 
     322        1000 :                 ret = SQLFetch(stmt2);
     323        1000 :                 if (ret == SQL_NO_DATA)
     324             :                         break;
     325        1000 :                 check(ret, SQL_HANDLE_STMT, stmt2, "SQLFetch 2");
     326        1000 :                 snprintf(ff2, sizeof(ff2), "value \342\200\230%d\342\200\231", i);
     327        1000 :                 ff4.day++;
     328        1000 :                 if ((ff4.day == 29 && ff4.month == 2) || (ff4.day == 31 && (ff4.month == 4 || ff4.month == 6 || ff4.month == 9 || ff4.month == 11)) || ff4.day == 32) {
     329             :                         ff4.day = 1;
     330          32 :                         ff4.month++;
     331          32 :                         if (ff4.month == 13) {
     332             :                                 ff4.month = 1;
     333           2 :                                 ff4.year++;
     334             :                         }
     335             :                 }
     336        1000 :                 ff5.second++;
     337        1000 :                 if (ff5.second == 60) {
     338             :                         ff5.second = 0;
     339          33 :                         ff5.minute++;
     340          33 :                         if (ff5.minute == 60) {
     341             :                                 ff5.minute = 0;
     342           0 :                                 ff5.hour++;
     343           0 :                                 if (ff5.hour == 25)
     344             :                                         ff5.hour = 0;
     345             :                         }
     346             :                 }
     347        1000 :                 if (f1 != i || strcmp(f2, ff2) != 0 || f3 != i * 1.5 || f4.year != ff4.year || f4.month != ff4.month || f4.day != ff4.day || f5.hour != ff5.hour || f5.minute != ff5.minute || f5.second != ff5.second)
     348           0 :                         printf("%d %s %g %04d:%02d:%02d %02d-%02d-%02d\n", f1, f2, f3, f4.year, f4.month, f4.day, f5.hour, f5.minute, f5.second);
     349        1000 :                 i++;
     350             : 
     351             :         }
     352             : 
     353           1 :         ret = SQLCloseCursor(stmt);
     354           1 :         check(ret, SQL_HANDLE_STMT, stmt, "SQLCloseCursor");
     355             : 
     356             :         /* cleanup and disconnect */
     357           1 :         ret = SQLFreeHandle(SQL_HANDLE_STMT, stmt2);
     358           1 :         check(ret, SQL_HANDLE_STMT, stmt2, "SQLFreeHandle (STMT 2)");
     359             : 
     360             :         /* drop the test table */
     361           1 :         ret = SQLExecDirect(stmt, (SQLCHAR *) "DROP TABLE odbcsampletest", SQL_NTS);
     362           1 :         check(ret, SQL_HANDLE_STMT, stmt, "SQLExecDirect 3");
     363             : 
     364           1 :         ret = SQLFreeHandle(SQL_HANDLE_STMT, stmt);
     365           1 :         check(ret, SQL_HANDLE_STMT, stmt, "SQLFreeHandle (STMT 1)");
     366             : 
     367           1 :         ret = SQLDisconnect(dbc);
     368           1 :         check(ret, SQL_HANDLE_DBC, dbc, "SQLDisconnect");
     369             : 
     370           1 :         ret = SQLFreeHandle(SQL_HANDLE_DBC, dbc);
     371           1 :         check(ret, SQL_HANDLE_DBC, dbc, "SQLFreeHandle (DBC)");
     372             : 
     373           1 :         ret = SQLFreeHandle(SQL_HANDLE_ENV, env);
     374           1 :         check(ret, SQL_HANDLE_ENV, env, "SQLFreeHandle (ENV)");
     375             : 
     376             :         return 0;
     377             : }

Generated by: LCOV version 1.14