Using CPA in SQLite mode to open a SQL setup script


#1

Hi-

In our struggles to get one of the SQL modes to work, we’ve been encountering a consistent problem with a piece of the SQL_SETUP.SQL script generated by CP.

8/2/16 11:00:36.144 AM CellProfiler Analyst[96933]: Query was: "CREATE TABLE IF NOT EXISTS Experiment (
8/2/16 11:00:36.144 AM CellProfiler Analyst[96933]: experiment_id integer primary key AUTO_INCREMENT,
8/2/16 11:00:36.144 AM CellProfiler Analyst[96933]: name text);
8/2/16 11:00:36.144 AM CellProfiler Analyst[96933]: "
8/2/16 11:00:36.144 AM CellProfiler Analyst[96933]: First exception was: near “AUTO_INCREMENT”: syntax error
8/2/16 11:00:36.144 AM CellProfiler Analyst[96933]: Second exception was: ERROR: Database query failed for connection "MainThread"
8/2/16 11:00:36.144 AM CellProfiler Analyst[96933]: Query was: "CREATE TABLE IF NOT EXISTS Experiment (
8/2/16 11:00:36.144 AM CellProfiler Analyst[96933]: experiment_id integer primary key AUTO_INCREMENT,
8/2/16 11:00:36.145 AM CellProfiler Analyst[96933]: name text);
8/2/16 11:00:36.145 AM CellProfiler Analyst[96933]: "
8/2/16 11:00:36.145 AM CellProfiler Analyst[96933]: Exception was: near “AUTO_INCREMENT”: syntax error
8/2/16 11:00:36.184 AM CellProfiler Analyst[96933]: CellProfiler Analyst Error

We’ve encountered problems with this piece of the script when using CPA to open the .sql file, as well as when we’ve tried to have CP push data directly to our SQL database, as well as when we’ve tried to use the SQL_SETUP.sql script offline to create the tables in our MySQL database.

Unfortunately we’re not pro SQL people here, so if anyone can spot why we’re getting syntax errors here, that’d be super helpful. I’ve included the properties file and the SQL_SETUP.sql script in question.

Here’s the offending bit of the SQL script:

PRIMARY KEY (ImageNumber, Whole_Cell_Mask_Number_Object_Number) );

CREATE TABLE IF NOT EXISTS Experiment (
experiment_id integer primary key AUTO_INCREMENT,
name text);

CREATE TABLE IF NOT EXISTS Experiment_Properties (
experiment_id integer not null,
object_name text not null,
field text not null,
value longtext,
constraint Experiment_Properties_pk primary key
(experiment_id, object_name(200), field(200)));

INSERT INTO Experiment (name) values (‘SQLite’);

SQLite_exp1.properties (6.5 KB)

SQL_SETUP.SQL.zip (18.2 KB)