sqllogictest

Check-in [81505d3c5f]
Login

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

Overview
Comment:Better support for the Oracle ODBC interface; Added option to xStatement to suppress printing of errors.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 81505d3c5ff71000eb2b338cd159146270989afc
User & Date: shaneh 2010-08-02 19:07:44.000
Context
2010-08-10
03:33
Evidence testing of SQLite SQL language. check-in: 99714e1f5b user: shaneh tags: trunk
2010-08-02
19:07
Better support for the Oracle ODBC interface; Added option to xStatement to suppress printing of errors. check-in: 81505d3c5f user: shaneh tags: trunk
2010-07-21
14:44
Some more tests of the in-op that work on MS SQL Server, Oracle, and MySQL. check-in: cf9bd258b0 user: shaneh tags: trunk
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/slt_odbc3.c.
︙︙
38
39
40
41
42
43
44
45
46

47
48
49
50
51
52
53
54






55
56
57
58
59
60
61

#include <windows.h>
#endif
#define SQL_NOUNICODEMAP
#include <sql.h>
#include <sqlext.h>


#define SLT_DSN "sqllogictest"
#define SLT_DB  "slt"



/* 
** Forward prototypes.
*/
static int ODBC3Statement(
  void *pConn,                /* Connection created by xConnect */
  const char *zSql            /* SQL statement to evaluate */






);

/*
** Structure used to hold handles needed for ODBC3 connection.
*/
typedef struct ODBC3_Handles ODBC3_Handles;
struct ODBC3_Handles {








|
|
>







|
>
>
>
>
>
>








38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68

#include <windows.h>
#endif
#define SQL_NOUNICODEMAP
#include <sql.h>
#include <sqlext.h>


#define SLT_DSN   "sqllogictest"
#define SLT_DB    "slt"
#define SLT_USER  "slt"


/* 
** Forward prototypes.
*/
static int ODBC3Statement(
  void *pConn,                /* Connection created by xConnect */
  const char *zSql,           /* SQL statement to evaluate */
  int bQuiet                  /* True to suppress printing errors. */
);

static int ODBC3GetEngineName(
  void *pConn,                /* Connection created by xConnect */
  const char **zName          /* SQL statement to evaluate */
);

/*
** Structure used to hold handles needed for ODBC3 connection.
*/
typedef struct ODBC3_Handles ODBC3_Handles;
struct ODBC3_Handles {
︙︙
152
153
154
155
156
157
158

159
160
161





162
163
164
165
166
167
168

static int ODBC3_dropAllTables(ODBC3_Handles *pODBC3conn)
{
  int rc = 0;
  SQLRETURN ret;       /* ODBC API return status */
  SQLSMALLINT columns; /* number of columns in result-set */
  ODBC3_resAccum res;          /* query result accumulator */
  SQLUSMALLINT i;

  char zSql[512];
  SQLHSTMT stmt = SQL_NULL_HSTMT;






  /* zero out accumulator structure */
  memset(&res, 0, sizeof(res));

  /* Allocate a statement handle */
  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);








>



>
>
>
>
>








159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181

static int ODBC3_dropAllTables(ODBC3_Handles *pODBC3conn)
{
  int rc = 0;
  SQLRETURN ret;       /* ODBC API return status */
  SQLSMALLINT columns; /* number of columns in result-set */
  ODBC3_resAccum res;          /* query result accumulator */
  SQLUSMALLINT i;
  char *zDmbsName = "unknown";
  char zSql[512];
  SQLHSTMT stmt = SQL_NULL_HSTMT;

  /* find type of db engine.  we may need to do special
   * processing based on the engine.
   */
  ODBC3GetEngineName(pODBC3conn, &zDmbsName);
  
  /* zero out accumulator structure */
  memset(&res, 0, sizeof(res));

  /* Allocate a statement handle */
  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);
︙︙
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

  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], "NULL"))


          && (strlen(res.azValue[i+2])>0)
          && (0 == strcmp(res.azValue[i+3], "TABLE"))

      ){
        sprintf(zSql, "DROP TABLE %s", res.azValue[i+2]);
        rc = ODBC3Statement(pODBC3conn, zSql);

      }
    }
  }
  
  return rc;
}









>
|
>
>
>
|





|
>
>
>
>
>
>
>
>

>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
|
>
>
|
|
>
|
|
|
>








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

  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 zUserName[512] = SLT_USER;
    char *pc1;
    char *pc2;

    pc1 = zDbName;
    pc2 = strstr(pODBC3conn->zConnStr, "DATABASE=");
    if( pc2 ){
      pc2 += 9;
      while( *pc2 && (*pc2!=';') ) *pc1++ = *pc2++;
      *pc1 = '\0';
    }

    pc1 = zUserName;
    pc2 = strstr(pODBC3conn->zConnStr, "UID=");
    if( pc2 ){
      pc2 += 4;
      while( *pc2 && (*pc2!=';') ) *pc1++ = *pc2++;
      *pc1 = '\0';
    }

    /* for each valid table found, drop it */
    if( 0 == stricmp(zDmbsName, "mysql") ){
      for( i=0; !rc && (i+4<res.nUsed); i+=5 ){
        if(    (0 == stricmp(res.azValue[i], zDbName))
            && (strlen(res.azValue[i+2])>0)
            && (0 == strcmp(res.azValue[i+3], "TABLE"))
        ){
          sprintf(zSql, "DROP TABLE %s", res.azValue[i+2]);
          rc = ODBC3Statement(pODBC3conn, zSql, 0);
        }
      }
    }else if( 0 == stricmp(zDmbsName, "oracle") ){
      for( i=0; !rc && (i+4<res.nUsed); i+=5 ){
        if(    (0 == stricmp(res.azValue[i], zDbName)
                 || 0 == strcmp(res.azValue[i], "NULL"))
            && (0 == strcmp(res.azValue[i+1], "(empty)")
                 || 0 == strcmp(res.azValue[i+1], "NULL")
                 || 0 == stricmp(res.azValue[i+1], zUserName))
            && (strlen(res.azValue[i+2])>0)
              && (0 == strchr(res.azValue[i+2], '$')) /* system/temp table */
            && (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, 0);
        }
      }
    }else if( 0 == stricmp(zDmbsName, "mssql") ){
      for( i=0; !rc && (i+4<res.nUsed); i+=5 ){
        if(    (0 == stricmp(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, 0);
        }
      }
    }else{
      for( i=0; !rc && (i+4<res.nUsed); i+=5 ){
        if(    (0 == strcmp(res.azValue[i], zDbName)
                 || 0 == strcmp(res.azValue[i], "NULL"))
            && (0 == strcmp(res.azValue[i+1], "(empty)")
                 || 0 == strcmp(res.azValue[i+1], "NULL"))
            && (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, 0);
        }
      }
    }
  }
  
  return rc;
}
︙︙
381
382
383
384
385
386
387
388

389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416


/*
** Evaluate the single SQL statement given in zSql.  Return 0 on success.
** return non-zero if any error occurs.
*/
static int ODBC3Statement(
  void *pConn,                /* Connection created by xConnect */
  const char *zSql            /* SQL statement to evaluate */

){
  int rc = 0;
  SQLRETURN ret; /* ODBC API return status */
  ODBC3_Handles *pODBC3conn = pConn;
  SQLHSTMT stmt = SQL_NULL_HSTMT;

  /* Allocate a statement handle */
  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;
  }

  ret = SQLExecDirect(stmt, (SQLCHAR *)zSql, SQL_NTS);
  if( !SQL_SUCCEEDED(ret) && (ret != SQL_SUCCESS_WITH_INFO) 
  /* a searched update or delete statement was executed but did not affect any rows (ODBC3) */
#if defined(SQL_NO_DATA)
      && (ret != SQL_NO_DATA) 
#endif
    ){
    ODBC3_perror("SQLExecDirect", stmt, SQL_HANDLE_STMT);
    rc = 1;
  }

  if( stmt != SQL_NULL_HSTMT ){
    SQLFreeHandle(SQL_HANDLE_STMT, stmt);
  }









|
>









|










|








449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485


/*
** Evaluate the single SQL statement given in zSql.  Return 0 on success.
** return non-zero if any error occurs.
*/
static int ODBC3Statement(
  void *pConn,                /* Connection created by xConnect */
  const char *zSql,           /* SQL statement to evaluate */
  int bQuiet                  /* True to suppress printing errors. */
){
  int rc = 0;
  SQLRETURN ret; /* ODBC API return status */
  ODBC3_Handles *pODBC3conn = pConn;
  SQLHSTMT stmt = SQL_NULL_HSTMT;

  /* Allocate a statement handle */
  ret = SQLAllocHandle(SQL_HANDLE_STMT, pODBC3conn->dbc, &stmt);
  if( !SQL_SUCCEEDED(ret) && (ret != SQL_SUCCESS_WITH_INFO) ){
    if (!bQuiet) ODBC3_perror("SQLAllocHandle", pODBC3conn->dbc, SQL_HANDLE_DBC);
    return 1;
  }

  ret = SQLExecDirect(stmt, (SQLCHAR *)zSql, SQL_NTS);
  if( !SQL_SUCCEEDED(ret) && (ret != SQL_SUCCESS_WITH_INFO) 
  /* a searched update or delete statement was executed but did not affect any rows (ODBC3) */
#if defined(SQL_NO_DATA)
      && (ret != SQL_NO_DATA) 
#endif
    ){
    if (!bQuiet) ODBC3_perror("SQLExecDirect", stmt, SQL_HANDLE_STMT);
    rc = 1;
  }

  if( stmt != SQL_NULL_HSTMT ){
    SQLFreeHandle(SQL_HANDLE_STMT, stmt);
  }
︙︙
Changes to src/slt_sqlite.c.
︙︙
73
74
75
76
77
78
79
80

81
82
83
84
85
86
87


/*
** Evaluate the single SQL statement given in zSql.  Return 0 on success.
** return non-zero if any error occurs.
*/
static int sqliteStatement(
  void *pConn,                /* Connection created by xConnect */
  const char *zSql            /* SQL statement to evaluate */

){
  int rc;
  sqlite3 *db;

  db = (sqlite3*)pConn;
  rc = sqlite3_exec(db, zSql, 0, 0, 0);
  return rc!=SQLITE_OK;








|
>








73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88


/*
** Evaluate the single SQL statement given in zSql.  Return 0 on success.
** return non-zero if any error occurs.
*/
static int sqliteStatement(
  void *pConn,                /* Connection created by xConnect */
  const char *zSql,           /* SQL statement to evaluate */
  int bQuiet                  /* True to suppress printing errors. */
){
  int rc;
  sqlite3 *db;

  db = (sqlite3*)pConn;
  rc = sqlite3_exec(db, zSql, 0, 0, 0);
  return rc!=SQLITE_OK;
︙︙
Changes to src/sqllogictest.c.
︙︙
510
511
512
513
514
515
516


517
518
519
520
521
522
523
524
525
526
527



528



529
530
531
532
533
534
535
536
537
538
539
540
541
542

      }
      continue;
    }

    /* Figure out the record type and do appropriate processing */
    if( strcmp(sScript.azToken[0],"statement")==0 ){
      int k = 0;



      /* Extract the SQL from second and subsequent lines of the
      ** record.  Copy the SQL into contiguous memory at the beginning
      ** of zScript - we are guaranteed to have enough space there. */
      while( nextLine(&sScript) && sScript.zLine[0] ){
        if( k>0 ) zScript[k++] = '\n';
        memmove(&zScript[k], sScript.zLine, sScript.len);
        k += sScript.len;
      }
      zScript[k] = 0;




      /* Run the statement.  Remember the results */



      rc = pEngine->xStatement(pConn, zScript);
      nCmd++;

      /* Check to see if we are expecting success or failure */
      if( strcmp(sScript.azToken[1],"ok")==0 ){
        /* do nothing if we expect success */
      }else if( strcmp(sScript.azToken[1],"error")==0 ){
        /* Invert the result if we expect failure */
        rc = !rc;
      }else{
        fprintf(stderr, "%s:%d: statement argument should be 'ok' or 'error'\n",
                zScriptFile, sScript.startLine);
        nErr++;
        rc = 0;








>
>











>
>
>
|
>
>
>
|



|

|








510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550

      }
      continue;
    }

    /* Figure out the record type and do appropriate processing */
    if( strcmp(sScript.azToken[0],"statement")==0 ){
      int k = 0;
      int bExpectOk = 0;
      int bExpectError = 0;

      /* Extract the SQL from second and subsequent lines of the
      ** record.  Copy the SQL into contiguous memory at the beginning
      ** of zScript - we are guaranteed to have enough space there. */
      while( nextLine(&sScript) && sScript.zLine[0] ){
        if( k>0 ) zScript[k++] = '\n';
        memmove(&zScript[k], sScript.zLine, sScript.len);
        k += sScript.len;
      }
      zScript[k] = 0;

      bExpectOk = strcmp(sScript.azToken[1],"ok")==0;
      bExpectError = strcmp(sScript.azToken[1],"error")==0;

      /* Run the statement.  Remember the results 
      ** If we're expecting an error, pass true to suppress
      ** printing of any errors. 
      */
      rc = pEngine->xStatement(pConn, zScript, bExpectError);
      nCmd++;

      /* Check to see if we are expecting success or failure */
      if( bExpectOk ){
        /* do nothing if we expect success */
      }else if( bExpectError ){
        /* Invert the result if we expect failure */
        rc = !rc;
      }else{
        fprintf(stderr, "%s:%d: statement argument should be 'ok' or 'error'\n",
                zScriptFile, sScript.startLine);
        nErr++;
        rc = 0;
︙︙
Changes to src/sqllogictest.h.
︙︙
31
32
33
34
35
36
37
38


39
40
41
42
43
44
45

*/
typedef struct DbEngine DbEngine;
struct DbEngine {
  const char *zName;                      /* Name of this engine */
  void *pAuxData;                         /* Aux data passed to xConnect */
  int (*xConnect)(void *pAux, const char *zConnectStr, void **ppConn);
  int (*xGetEngineName)(void*, const char **zName);
  int (*xStatement)(void*, const char *zSql);


  int (*xQuery)(void*, const char *zSql, const char *zTypes,
                char ***pazResult, int *pnResult);
  int (*xFreeResults)(void*, char **azResult, int nResult);
  int (*xDisconnect)(void*);
};

/*








|
>
>








31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

*/
typedef struct DbEngine DbEngine;
struct DbEngine {
  const char *zName;                      /* Name of this engine */
  void *pAuxData;                         /* Aux data passed to xConnect */
  int (*xConnect)(void *pAux, const char *zConnectStr, void **ppConn);
  int (*xGetEngineName)(void*, const char **zName);
  int (*xStatement)(void*, 
                    const char *zSql, 
                    int bQuiet);          /* True to suppress printing errors. */
  int (*xQuery)(void*, const char *zSql, const char *zTypes,
                char ***pazResult, int *pnResult);
  int (*xFreeResults)(void*, char **azResult, int nResult);
  int (*xDisconnect)(void*);
};

/*
︙︙
Changes to test/evidence/in2.test.
︙︙
288
289
290
291
292
293
294






















295

query I nosort
SELECT 1 FROM t1 WHERE NULL NOT IN ( NULL, 1.0 )
----

query I nosort
SELECT 1 FROM t1 WHERE NULL NOT IN ( NULL, '1' )
----































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


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

query I nosort
SELECT 1 FROM t1 WHERE NULL NOT IN ( NULL, 1.0 )
----

query I nosort
SELECT 1 FROM t1 WHERE NULL NOT IN ( NULL, '1' )
----

# EVIDENCE-OF: R-00416-37972 When the right operand of an IN or NOT IN
# operator is a subquery, the subquery must have a single result column.

query I nosort
SELECT 1 FROM t1 WHERE 1 IN (SELECT 1)
----
1
1
1

statement error
SELECT 1 FROM t1 WHERE 1 IN (SELECT 1,2)

statement error
SELECT 1 FROM t1 WHERE 1 IN (SELECT x,y FROM t1)

statement error
SELECT 1 FROM t1 WHERE 1 IN (SELECT * FROM t1)

statement error
SELECT 1 FROM t1 WHERE 1 IN (SELECT min(x),max(x) FROM t1)