Sqlite database schema

[abbynewbury]

When running CohortDiagnostics package on a sqlite database that is structured with a cdmDatabaseSchema of "dbo" and a cohortDatabaseSchema of "results" I run into errors since sqlite does not support multiple schemas within a database. Instead tables like the concept table can be referred to as "dboCONCEPT" where "dbo" doesn't act as a schema but instead just a naming convention for tables. Then, for any CohortDiagnostics sql queries I get the error "no such table: dbo.CONCEPT". Even after renaming my tables to "dbo." instead of "dbo_" I find I am still getting this error. Is there a fix to this problem? Thanks!

[azimov]

Hi @abbynewbury I'm a little confused with this question - the convention (and standard requirement) for sqlite is to always use "main" in place of any schema variable (e.g. main.concept). To reiterate - if your CDM is in sqlite your cdmDatabaseSchema, cohortDatabaseSchema etc should all be set to main as sqlite doesn't actually support the use of schemas. However, we need to use this variable to execute all cross platform supported queries.

dbo Is normally used in microsoft database standards like access and sql server - we don't intentionally use it in any of our systems.

Can you give me some example code as to where you're calling CohortDiagnostics?

[abbynewbury]

Hi, thanks so much for the response. I didn't realize that the "schema" main would work for my sqlite database but I see that it does!