sqllogictest
Check-in [2014c5b9c3]
Not logged in

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Added more error checking. Updated to allow specifing DSN and DATABASE name in -odbc option.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 2014c5b9c382fde1b0cf14ef0b48def2b36483f7
User & Date: shaneh 2008-12-02 02:28:04
Context
2008-12-02
04:57
Made the xQuery method of the odbc interface match the SQLite version more closely, honoring type information and formatting in the same manor. check-in: b5897694e8 user: shaneh tags: trunk
02:28
Added more error checking. Updated to allow specifing DSN and DATABASE name in -odbc option. check-in: 2014c5b9c3 user: shaneh tags: trunk
02:24
Updated usage. check-in: dafa60c487 user: shaneh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/slt_odbc3.c.

57
58
59
60
61
62
63

64
65
66
67
68
69
70
..
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
...
160
161
162
163
164
165
166

167
168
169
170
171
172
173
...
215
216
217
218
219
220
221














222
223
224
225

226
227
228
229
230
231
232
...
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
...
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267

268
269
270





271

272
273








274
275



276













277

278
279


280







281
282
283
284
285
286
287


288
289
290
291
292
293
294
295
296







297
298
299
300
301
302
303
304


305
306
307
308
309
310
311
...
480
481
482
483
484
485
486

487
488
489
490
491
492

493
494
495
496
497
498




499
500
501

502
503
504
505
506
507
508
509
/*
** Structure used to hold handles needed for ODBC3 connection.
*/
typedef struct ODBC3_Handles ODBC3_Handles;
struct ODBC3_Handles {
  SQLHENV env;
  SQLHDBC dbc;

};

/*
** Utility function to display the details of an ODBC3 error.
*/
static void ODBC3_perror(char *fn,
                         SQLHANDLE handle,
................................................................................
                        &len );
    if (SQL_SUCCEEDED(ret))
    {
      fprintf(stderr,
              "%s:%s:%ld:%ld:%s\n", fn, state, (long)i, (long)native, text);
    }
  }
  while( ret == SQL_SUCCESS );
}

/*
** Structure used to accumulate a result set.
*/
typedef struct ODBC3_resAccum ODBC3_resAccum;
struct ODBC3_resAccum {
................................................................................
  ret = SQLAllocHandle(SQL_HANDLE_STMT, pODBC3conn->dbc, &stmt);
  if( !SQL_SUCCEEDED(ret) && (ret != SQL_SUCCESS_WITH_INFO) ){
    ODBC3_perror("SQLAllocHandle", pODBC3conn->dbc, SQL_HANDLE_DBC);
    return 1;
  }

  /* Retrieve a list of tables */

  ret = SQLTables(stmt, NULL, 0, NULL, 0, NULL, 0, "TABLE", SQL_NTS);
  if( !SQL_SUCCEEDED(ret) && (ret != SQL_SUCCESS_WITH_INFO) ){
    ODBC3_perror("SQLTables", stmt, SQL_HANDLE_STMT);
    rc = 1;
  }
  
  if( !rc ){
................................................................................
  }
  
  if( stmt != SQL_NULL_HSTMT ){
    SQLFreeHandle(SQL_HANDLE_STMT, stmt);
  }

  if( !rc ){














    /* for each valid table found, drop it */
    for( i=0; !rc && (i+4<res.nUsed); i+=5 ){
      if(    (0 == strcmp(res.azValue[i], SLT_DB))
          && (0 == strcmp(res.azValue[i+1], "dbo"))

          && (0 == strcmp(res.azValue[i+3], "TABLE"))
          && (0 == strcmp(res.azValue[i+4], "NULL")) ){
        sprintf(zSql, "DROP TABLE %s", res.azValue[i+2]);
        rc = ODBC3Statement(pODBC3conn, zSql);
      }
    }
  }
................................................................................
  
  return rc;
}


/*
** This routine is called to open a connection to a new, empty database.
** The zConnectStr argument is the value of the -connection command-line
** option.  This is intended to contain information on how to connect to
** the database engine.  The zConnectStr argument will be NULL if there
** is no -connection on the command-line.  
**
** An object that describes the newly opened and initialized database
** connection is returned by writing into *ppConn.
**
** This routine returns 0 on success and non-zero if there are any errors.
*/
static int ODBC3Connect(
................................................................................
  void *NotUsed,              /* Argument from DbEngine object.  Not used */
  const char *zConnectStr,    /* Connection string */
  void **ppConn               /* Write completed connection here */
){
  int rc = 0;
  SQLRETURN ret; /* ODBC API return status */
  ODBC3_Handles *pODBC3conn = NULL;
  char szSqlConnStr[512];

  /* Allocate a structure to hold all of our ODBC3 handles */
  pODBC3conn = (ODBC3_Handles *)malloc(sizeof(ODBC3_Handles));
  if( !pODBC3conn ){
    fprintf(stderr, "out of memory at %s:%d\n", __FILE__, __LINE__);
    return 1;
  }
  pODBC3conn->env = SQL_NULL_HENV;
  pODBC3conn->dbc = SQL_NULL_HDBC;


  /* Allocate an environment handle */
  SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &pODBC3conn->env);





  /* We want ODBC 3 support */

  SQLSetEnvAttr(pODBC3conn->env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
  /* Allocate a connection handle */








  SQLAllocHandle(SQL_HANDLE_DBC, pODBC3conn->env, &pODBC3conn->dbc);
  /* TBD: check all of the above for failures */

















  /* Build the connection string.

  */
  sprintf(szSqlConnStr, "DSN=" SLT_DSN ";DATABASE=" SLT_DB ";%s", ( zConnectStr ) ? zConnectStr : "");










  /* Open a connection to the new database.
  */
  /* TBD: should we use SQLConnect() here? */
  ret = SQLDriverConnect(pODBC3conn->dbc, 
                         NULL, 
                         (SQLCHAR *)szSqlConnStr, 
                         SQL_NTS,


                         NULL, 
                         0, 
                         NULL,
                         SQL_DRIVER_COMPLETE);
  if( !SQL_SUCCEEDED(ret) && (ret != SQL_SUCCESS_WITH_INFO) ){
    ODBC3_perror("SQLDriverConnect", pODBC3conn->dbc, SQL_HANDLE_DBC);
    rc = 1;
  }
  







  /* Loop over all tables, etc. available in the database and drop them,
  ** thus resetting it to an empty database.  */
  if( !rc ){
    rc = ODBC3_dropAllTables(pODBC3conn);
  }
  
  /* TBD: is there a way to specify synchronous=OFF or equivalent */



  /* store connection info */
  *ppConn = (void*)pODBC3conn;

  return rc;  
}

/*
................................................................................
  ODBC3_Handles *pODBC3conn = pConn;
  
  if ( !pODBC3conn ){
    fprintf(stderr, "invalid ODBC3 connection object\n");
    return 1;
  }


  ret = SQLDisconnect(pODBC3conn->dbc);   /* disconnect from driver */
  if( !SQL_SUCCEEDED(ret) && (ret != SQL_SUCCESS_WITH_INFO) ){
    ODBC3_perror("SQLDisconnect", pODBC3conn->dbc, SQL_HANDLE_DBC);
    rc = 1;
  }
  

  if( pODBC3conn->dbc != SQL_NULL_HDBC ){
    SQLFreeHandle(SQL_HANDLE_DBC, pODBC3conn->dbc);
  }
  if( pODBC3conn->env != SQL_NULL_HENV ){
    SQLFreeHandle(SQL_HANDLE_ENV, pODBC3conn->env);
  }





  pODBC3conn->env = SQL_NULL_HENV;
  pODBC3conn->dbc = SQL_NULL_HDBC;


  free(pODBC3conn);
  return rc;
}

/*
** This routine registers the ODBC3 database engine with the main
** driver.  New database engine interfaces should have a single







>







 







|







 







>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>


|

>







 







|


|







 







|









>


|
>
>
>
>
>

>
|
<
>
>
>
>
>
>
>
>
|
<
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
|
>
|
<
>
>
|
>
>
>
>
>
>
>
|
|
|
|
|
|
|
>
>
|
<
<
|
|
|
|
|
|
>
>
>
>
>
>
>








>
>







 







>
|
|
|
|
|
|
>






>
>
>
>



>
|







57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
..
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
...
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
...
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
...
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
...
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296

297
298
299
300
301
302
303
304
305

306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325

326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345


346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
...
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
/*
** Structure used to hold handles needed for ODBC3 connection.
*/
typedef struct ODBC3_Handles ODBC3_Handles;
struct ODBC3_Handles {
  SQLHENV env;
  SQLHDBC dbc;
  SQLCHAR *zConnStr;
};

/*
** Utility function to display the details of an ODBC3 error.
*/
static void ODBC3_perror(char *fn,
                         SQLHANDLE handle,
................................................................................
                        &len );
    if (SQL_SUCCEEDED(ret))
    {
      fprintf(stderr,
              "%s:%s:%ld:%ld:%s\n", fn, state, (long)i, (long)native, text);
    }
  }
  while( SQL_SUCCEEDED(ret) );
}

/*
** Structure used to accumulate a result set.
*/
typedef struct ODBC3_resAccum ODBC3_resAccum;
struct ODBC3_resAccum {
................................................................................
  ret = SQLAllocHandle(SQL_HANDLE_STMT, pODBC3conn->dbc, &stmt);
  if( !SQL_SUCCEEDED(ret) && (ret != SQL_SUCCESS_WITH_INFO) ){
    ODBC3_perror("SQLAllocHandle", pODBC3conn->dbc, SQL_HANDLE_DBC);
    return 1;
  }

  /* Retrieve a list of tables */
  /* TBD:  do we need to drop views, triggers, etc. here? */
  ret = SQLTables(stmt, NULL, 0, NULL, 0, NULL, 0, "TABLE", SQL_NTS);
  if( !SQL_SUCCEEDED(ret) && (ret != SQL_SUCCESS_WITH_INFO) ){
    ODBC3_perror("SQLTables", stmt, SQL_HANDLE_STMT);
    rc = 1;
  }
  
  if( !rc ){
................................................................................
  }
  
  if( stmt != SQL_NULL_HSTMT ){
    SQLFreeHandle(SQL_HANDLE_STMT, stmt);
  }

  if( !rc ){
  
    /* Find the name of the database (defaults to SLT_DB).
    ** When looping through the tables to delete, only delete
    ** tables from that database.
    */
    char zDbName[512] = SLT_DB;
    char *pc1 = zDbName;
    char *pc2 = strstr(pODBC3conn->zConnStr, "DATABASE=");
    if( pc2 ){
      pc2 += 9;
      while( *pc2 && (*pc2!=';') ) *pc1++ = *pc2++;
      *pc1 = '\0';
    }
  
    /* for each valid table found, drop it */
    for( i=0; !rc && (i+4<res.nUsed); i+=5 ){
      if(    (0 == strcmp(res.azValue[i], zDbName))
          && (0 == strcmp(res.azValue[i+1], "dbo"))
          && (strlen(res.azValue[i+2])>0)
          && (0 == strcmp(res.azValue[i+3], "TABLE"))
          && (0 == strcmp(res.azValue[i+4], "NULL")) ){
        sprintf(zSql, "DROP TABLE %s", res.azValue[i+2]);
        rc = ODBC3Statement(pODBC3conn, zSql);
      }
    }
  }
................................................................................
  
  return rc;
}


/*
** This routine is called to open a connection to a new, empty database.
** The zConnectStr argument is the value of the -odbc command-line
** option.  This is intended to contain information on how to connect to
** the database engine.  The zConnectStr argument will be NULL if there
** is no -odbc on the command-line.  
**
** An object that describes the newly opened and initialized database
** connection is returned by writing into *ppConn.
**
** This routine returns 0 on success and non-zero if there are any errors.
*/
static int ODBC3Connect(
................................................................................
  void *NotUsed,              /* Argument from DbEngine object.  Not used */
  const char *zConnectStr,    /* Connection string */
  void **ppConn               /* Write completed connection here */
){
  int rc = 0;
  SQLRETURN ret; /* ODBC API return status */
  ODBC3_Handles *pODBC3conn = NULL;
  char szConnStrIn[512] = "";

  /* Allocate a structure to hold all of our ODBC3 handles */
  pODBC3conn = (ODBC3_Handles *)malloc(sizeof(ODBC3_Handles));
  if( !pODBC3conn ){
    fprintf(stderr, "out of memory at %s:%d\n", __FILE__, __LINE__);
    return 1;
  }
  pODBC3conn->env = SQL_NULL_HENV;
  pODBC3conn->dbc = SQL_NULL_HDBC;
  pODBC3conn->zConnStr = NULL;

  /* Allocate an environment handle */
  ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &pODBC3conn->env);
  if( !SQL_SUCCEEDED(ret) ){
    ODBC3_perror("SQLAllocHandle", pODBC3conn->env, SQL_HANDLE_ENV);
    rc = 1;
  }
  
  /* We want ODBC 3 support */
  if( !rc ){
    ret = SQLSetEnvAttr(pODBC3conn->env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);

    if( !SQL_SUCCEEDED(ret) ){
      ODBC3_perror("SQLSetEnvAttr", pODBC3conn->env, SQL_HANDLE_ENV);
      rc = 1;
    }
  }
  
  /* Allocate a database connection (dbc) handle */
  if( !rc ){
    ret = SQLAllocHandle(SQL_HANDLE_DBC, pODBC3conn->env, &pODBC3conn->dbc);

    if( !SQL_SUCCEEDED(ret) ){
      ODBC3_perror("SQLAllocHandle", pODBC3conn->env, SQL_HANDLE_ENV);
      rc = 1;
    }
  }
  
  /* Allocate storage space for the returned connection information.
  */
  if( !rc ){
    pODBC3conn->zConnStr = (SQLCHAR *)malloc(1024 * sizeof(SQLCHAR));
    if( !pODBC3conn->zConnStr ){
      fprintf(stderr, "out of memory at %s:%d\n", __FILE__, __LINE__);
      rc = 1;
    }
  }
  
  if( !rc ){
    /* Build the connection string.   If a DSN or DATABASE
    ** is not specified, use the defaults.
    */

    if( !zConnectStr || !strstr(zConnectStr, "DSN=") ){
      strcat(szConnStrIn, "DSN=" SLT_DSN ";");
    }
    if( !zConnectStr || !strstr(zConnectStr, "DATABASE=") ){
      strcat(szConnStrIn, "DATABASE=" SLT_DB ";");
    }
    if( zConnectStr ){
      strcat(szConnStrIn, zConnectStr);
    }

    /* Open a connection to the new database.
    */
    /* TBD: should we use SQLConnect() here? */
    ret = SQLDriverConnect(pODBC3conn->dbc, 
                           NULL, 
                           (SQLCHAR *)szConnStrIn, 
                           SQL_NTS,
                           pODBC3conn->zConnStr, 
                           1024 * sizeof(SQLCHAR), 
                           NULL,


                           SQL_DRIVER_COMPLETE);
    if( !SQL_SUCCEEDED(ret) && (ret != SQL_SUCCESS_WITH_INFO) ){
      ODBC3_perror("SQLDriverConnect", pODBC3conn->dbc, SQL_HANDLE_DBC);
      rc = 1;
    }
  }
  
  /* TBD:  should we call CREATE DATABASE 'slt'?
  ** This would require removing any DATABASE name from 
  ** the connection string, connecting to the DSN only,
  ** creating the db, then reconnecting the DSN with the
  ** the database name specified. */

  /* Loop over all tables, etc. available in the database and drop them,
  ** thus resetting it to an empty database.  */
  if( !rc ){
    rc = ODBC3_dropAllTables(pODBC3conn);
  }
  
  /* TBD: is there a way to specify synchronous=OFF or equivalent */

  /* TBD: should we free up anything allocated on error? */
  
  /* store connection info */
  *ppConn = (void*)pODBC3conn;

  return rc;  
}

/*
................................................................................
  ODBC3_Handles *pODBC3conn = pConn;
  
  if ( !pODBC3conn ){
    fprintf(stderr, "invalid ODBC3 connection object\n");
    return 1;
  }

  if( pODBC3conn->dbc != SQL_NULL_HDBC ){
    ret = SQLDisconnect(pODBC3conn->dbc);   /* disconnect from driver */
    if( !SQL_SUCCEEDED(ret) && (ret != SQL_SUCCESS_WITH_INFO) ){
      ODBC3_perror("SQLDisconnect", pODBC3conn->dbc, SQL_HANDLE_DBC);
      rc = 1;
    }
  }

  if( pODBC3conn->dbc != SQL_NULL_HDBC ){
    SQLFreeHandle(SQL_HANDLE_DBC, pODBC3conn->dbc);
  }
  if( pODBC3conn->env != SQL_NULL_HENV ){
    SQLFreeHandle(SQL_HANDLE_ENV, pODBC3conn->env);
  }

  if( pODBC3conn->zConnStr ){
    free(pODBC3conn->zConnStr);
  }

  pODBC3conn->env = SQL_NULL_HENV;
  pODBC3conn->dbc = SQL_NULL_HDBC;
  pODBC3conn->zConnStr = NULL;
  
  free(pODBC3conn);
  return rc;
}

/*
** This routine registers the ODBC3 database engine with the main
** driver.  New database engine interfaces should have a single