0

I have an application that uses an Oracle database. It reads data out of the database using statements like:

SELECT * from schema.table WHERE ...

I would like to create a SQLite database with data I can use for regression-testing the application. It's simple enough to change the jdbc settings to get the application to connect to a different database, but the SQL statements need to be exactly the same.

I know when I run SQLite from the command line, I can attach a database using

attach database as

If I do that, then the same select statements that work in oracle to access a table in a different Oracle schema will work to access the data in a different SQLite database. But, since I'm connecting via JDBC, I don't have the option to run the "attach database" command.

Is there a way to configure SQLite such that it will accept requests like the above?

I tried

create table "schema.table" ...

That worked, but then I need to include the quotes in the select statement, which Oracle won't allow.

user3294068
  • 340
  • 2
  • 6
  • 1
    Does it have to be SQLite? Have you looked into other Java databases like HSQLDB that explicitly offer some degree of [Oracle compatibility](http://hsqldb.org/doc/guide/compatibility-chapt.html#coc_compatibility_oracle)? – Gord Thompson Oct 21 '16 at 19:05
  • Sounds like you are in a bit of a pickle. SQLite won't support a period in its table name without the quotes (reference: http://stackoverflow.com/questions/3694276/what-are-valid-table-names-in-sqlite) and you can't user the quotes in the query. Looks like you'll have to modify the queries some how or use a different db implementation over sqlite – James Oravec Oct 21 '16 at 20:11
  • Why wouldn't JDBC allow you to execute an ATTACH statement? – CL. Oct 21 '16 at 20:16
  • I'm going with creating a View in Oracle that will allow me to access the tables without specifying a schema. Using SQLite rather than some other database allows me to run tests locally without needing to install anything. – user3294068 Oct 24 '16 at 18:35

0 Answers0