Parsing out multiple sqlite queries

[philip zeyliger]

Hi there,

Ideally, I'd like to use Xerials' jdbc-sqlite to run queries like "SELECT 1; SELECT 2"; i.e., running multiple queries in one go.  JDBC doesn't tend to allow this, so the trick is to parse out the SQL.  Does anyone have here have any recommendations on solutions that do that, using sqlite's parser?

Another approach, is, of course, to change how NativeDB.prepare works.  APSW is one python wrapper for sqlite that actually allows executing multiple queries.

Thanks for any input you might have!

Here's a relevant similar request: http://thread.gmane.org/gmane.comp.db.sqlite.general/27563

Here's how jdbc-sqlite does it.  Note how the last argument to sqlite3_prepare_v2 is 0.  If that were a pointer, it would allow one to grab the remaining sql text, per the docs in http://www.sqlite.org/c3ref/prepare.html.

JNIEXPORT jlong JNICALL Java_org_sqlite_NativeDB_prepare(

        JNIEnv *env, jobject this, jstring sql)

{

    sqlite3* db = gethandle(env, this);

    sqlite3_stmt* stmt;

    const char *strsql = (*env)->GetStringUTFChars(env, sql, 0);

    int status = sqlite3_prepare_v2(db, strsql, -1, &stmt, 0);

    (*env)->ReleaseStringUTFChars(env, sql, strsql);

    if (status != SQLITE_OK) {

        throw_errorcode(env, this, status);

        return fromref(0);

    }

    return fromref(stmt);

}

Here's how apsw does it.  Note "val->next" using "&tail" here.

  PYSQLITE_SC_CALL(res=(usepreparev2)?

                   sqlite3_prepare_v2(sc->db, buffer, buflen+1, &val->vdbestatement, &tail):  /* PYSQLITE_SC_CALL */

                   sqlite3_prepare(sc->db, buffer, buflen+1, &val->vdbestatement, &tail));    /* PYSQLITE_SC_CALL */

  /* Handle error.  We would have a Python error if vtable.FindFunction had an error */

  if(res!=SQLITE_OK || PyErr_Occurred())

    {

      SET_EXC(res, sc->db);

      AddTraceBackHere(__FILE__, __LINE__, "sqlite3_prepare", "{s: N}", "sql", convertutf8stringsize(buffer, buflen));

      goto error;

    }

  val->querylen=tail-buffer;

  /* is there a next statement (ignore semicolons and white space) */

  while( (tail-buffer<buflen) && (*tail==' ' || *tail=='\t' || *tail==';' || *tail=='\r' || *tail=='\n') )

    tail++;

  if(tail-buffer<buflen)

    {

      /* there are more statements */

      val->next=APSWBuffer_FromObject(utf8, tail-buffer, buflen-(tail-buffer));

      if(!val->next) goto error;

    }

  return val;

Many thanks,

-- Philip

[Grace B]

Hey Philip,

Those are good suggestions.

It would be great if you implemented the necessary changes and submitted a patch via a pull request on bitbucket https://bitbucket.org/xerial/sqlite-jdbc.

Cheers.