[sqlite] confusing error msgs
On 12 June 2015 at 06:42, Hick Gunter wrote: > You are creating each table in a separate file; a foreign key may only > reference a table in the same file. > > Your type declarations are faulty in that you are omitting an opening > parenthesis in a DECIMAL 4,3) declaration. > > SQLite does not constrain sizes, a TEXT(10) or a CHAR(1) variable my contain > arbitrary length strings; types only define affinities, the same fields could > be used to store integers, reals or BLOBS for that matter. > > SQLIte also does not implement fixed point numeric fields. DECIMAL (6,2) > defines a field of numeric affinity, i.e. if you provide a value that > contains a decimal point, it will be stored as a REAL. > And table constraints (foreign key in timeslip table) must follow the column declarations Regards, Simon
[sqlite] confusing error msgs
You are creating each table in a separate file; a foreign key may only reference a table in the same file. Your type declarations are faulty in that you are omitting an opening parenthesis in a DECIMAL 4,3) declaration. SQLite does not constrain sizes, a TEXT(10) or a CHAR(1) variable my contain arbitrary length strings; types only define affinities, the same fields could be used to store integers, reals or BLOBS for that matter. SQLIte also does not implement fixed point numeric fields. DECIMAL (6,2) defines a field of numeric affinity, i.e. if you provide a value that contains a decimal point, it will be stored as a REAL. -Urspr?ngliche Nachricht- Von: Keller Racing [mailto:keller_racing at netscape.com] Gesendet: Donnerstag, 11. Juni 2015 20:11 An: sqlite-users at mailinglists.sqlite.org Betreff: [sqlite] confusing error msgs Hi all. Yes, I'm a newby. I have some past experience using dBASE III+ and I currently use freeBASIC for my programming needs. I have a need to put a small database in one of my freeBASIC applications and SQLite seemed to fit the bill. However I am having some problems. I have 4 sql "CREATE TABLE" files: event.sql, timeslip.sql, baseline.sql and current.sql. Here is the code in the various files: CREATE TABLE event( ev_rec_no INTEGER PRIMARY KEY, ev_date TEXT(10), LOCATION TEXT(40), SANCTION TEXT(10), elevation DECIMAL(6, 2), evNote_1 TEXT (76), evNote_2 TEXT (76), evNote_3 TEXT (76), evNote_4 TEXT (76), evNote_5 TEXT (76) ); CREATE TABLE timeslip ( ts_rec_no INTEGER PRIMARY KEY, evnt_rec_no INTEGER, FOREIGN KEY (evnt_rec_no) REFERENCES event(ev_rec_no), stEvent_date TEXT (10), stLocation TEXT (50), stSanction TEXT (10), sfElev DECIMAL (6, 2), sfDist DECIMAL (5, 4), sfWgt DECIMAL (5, 2), usLaunch_rpm INTEGER, usShift_rpm INTEGER, stRun_time TEXT (5), stAMPM CHAR (1), stRun_type TEXT (2), stLane CHAR (1), stCarNum TEXT (5), stClass TEXT (8), sfCl_index DECIMAL (6, 4), sfOvUn DECIMAL (6, 4), sfDial DECIMAL (7,4),-- dial in sfRt DECIMAL (6, 4), -- reaction time sfTime_60_ft DECIMAL (7, 4), sfTime_330_ft DECIMAL (7, 4), sfTime_660_ft DECIMAL (7, 4), sfMPH_660_ft DECIMAL (8, 4), sfTIme_1000_ft DECIMAL (7, 4), sfMPH_1000_ft DECIMAL (8, 4), sfTime_1320_ft DECIMAL (7, 4), sfMPH_1320_ft DECIMAL (8,4), sfFirst DECIMAL (7,4), sfMOV DECIMAL (6, 4), stResult CHAR (1), -- won or loss sfTempFah DECIMAL(6,2), sfRelHumid DECIMAL (4,3), sfVapPress DECIMAL (4,3), sfBaroPress DECIMAL 4,3), stType CHAR(1), stNote_1 TEXT (76), stNote_2 TEXT (76), stNote_3 TEXT (76), stNote_4 TEXT (76), stNote_5 TEXT (76) ); CREATE TABLE baseline( bl_rec_no INTEGER PRIMARY KEY NOT NULL, stTestDate TEXT (10), sfElev DECIMAL(6, 2), sfTempFah DECIMAL(6,2), sfRelHumid DECIMAL (4,3), sfVapPress DECIMAL (4,3), sfBaroPress DECIMAL 4,3), stType CHAR(1), sfMaxTrq DECIMAL (7,3), sfMaxHP DECIMAL (7, 3), sfCorrecFact DECIMAL (4,3), stCorrecType TEXT (3), sfWgt DECIMAL (5, 2), stNote_1 TEXT (76), stNote_2 TEXT (76), stNote_3 TEXT (76), stNote_4 TEXT (76), stNote_5 TEXT (76) ); CREATE TABLE current( cr_rec_no INTEGER PRIMARY KEY, stCurDate TEXT (12), sfElev DECIMAL(6, 2), sfTempFah DECIMAL(6,2), sfRelHumid DECIMAL (4,3), sfVapPress DECIMAL (4,3), sfBaroPress DECIMAL 4,3), stType CHAR(1), stWgt DECIMAL (5,2), sfMPHcf DECIMAL (5, 4), sfETcf DECMIAL (5, 4), stNote_1 TEXT (76), stNote_2 TEXT (76), stNote_3 TEXT (76), stNote_4 TEXT (76), stNote_5 TEXT (76) ); Now I have a batch file that I use to construct the actual databases. Here is that batch file: REM ** REM db.bat - this batch file creats database for use with Performance Tuner REM ** del *.bak REM sqlite3 -init event.sql pt__x3xx.db REM sqlite3 -init event.sql event.db REM C:\Documents and Settings\All Users\Documents\src\perftune\sqlite_test>sqlite3 - REM init event.sql event.db REM -- Loading resources from event.sql REM SQLite version 3.7.5 REM Enter ".help" for instructions REM Enter SQL statements terminated with a ";" REM sqlite> .exit REM sqlite3 -init timeslip.sql timeslip.db REM C:\Documents and Settings\All Users\Documents\src\perftune\sqlite_test>sqlite3 - REM init timeslip.sql timeslip.db REM -- Loading resources from timeslip.sql REM Error: near line 3: near "stEvent_date": syntax error REM sqlite3 -init baseline.sql baseline.db REM C:\Documents and Settings\All Users\Documents\src\perftune\sqlite_test>sqlite3 - REM init baseline.sql baseline.db REM -- Loading resources f
[sqlite] confusing error msgs
Hi all. Yes, I'm a newby. I have some past experience using dBASE III+ and I currently use freeBASIC for my programming needs. I have a need to put a small database in one of my freeBASIC applications and SQLite seemed to fit the bill. However I am having some problems. I have 4 sql "CREATE TABLE" files: event.sql, timeslip.sql, baseline.sql and current.sql. Here is the code in the various files: CREATE TABLE event( ev_rec_no INTEGER PRIMARY KEY, ev_date TEXT(10), LOCATION TEXT(40), SANCTION TEXT(10), elevation DECIMAL(6, 2), evNote_1 TEXT (76), evNote_2 TEXT (76), evNote_3 TEXT (76), evNote_4 TEXT (76), evNote_5 TEXT (76) ); CREATE TABLE timeslip ( ts_rec_no INTEGER PRIMARY KEY, evnt_rec_no INTEGER, FOREIGN KEY (evnt_rec_no) REFERENCES event(ev_rec_no), stEvent_date TEXT (10), stLocation TEXT (50), stSanction TEXT (10), sfElev DECIMAL (6, 2), sfDist DECIMAL (5, 4), sfWgt DECIMAL (5, 2), usLaunch_rpm INTEGER, usShift_rpm INTEGER, stRun_time TEXT (5), stAMPM CHAR (1), stRun_type TEXT (2), stLane CHAR (1), stCarNum TEXT (5), stClass TEXT (8), sfCl_index DECIMAL (6, 4), sfOvUn DECIMAL (6, 4), sfDial DECIMAL (7,4),-- dial in sfRt DECIMAL (6, 4), -- reaction time sfTime_60_ft DECIMAL (7, 4), sfTime_330_ft DECIMAL (7, 4), sfTime_660_ft DECIMAL (7, 4), sfMPH_660_ft DECIMAL (8, 4), sfTIme_1000_ft DECIMAL (7, 4), sfMPH_1000_ft DECIMAL (8, 4), sfTime_1320_ft DECIMAL (7, 4), sfMPH_1320_ft DECIMAL (8,4), sfFirst DECIMAL (7,4), sfMOV DECIMAL (6, 4), stResult CHAR (1), -- won or loss sfTempFah DECIMAL(6,2), sfRelHumid DECIMAL (4,3), sfVapPress DECIMAL (4,3), sfBaroPress DECIMAL 4,3), stType CHAR(1), stNote_1 TEXT (76), stNote_2 TEXT (76), stNote_3 TEXT (76), stNote_4 TEXT (76), stNote_5 TEXT (76) ); CREATE TABLE baseline( bl_rec_no INTEGER PRIMARY KEY NOT NULL, stTestDate TEXT (10), sfElev DECIMAL(6, 2), sfTempFah DECIMAL(6,2), sfRelHumid DECIMAL (4,3), sfVapPress DECIMAL (4,3), sfBaroPress DECIMAL 4,3), stType CHAR(1), sfMaxTrq DECIMAL (7,3), sfMaxHP DECIMAL (7, 3), sfCorrecFact DECIMAL (4,3), stCorrecType TEXT (3), sfWgt DECIMAL (5, 2), stNote_1 TEXT (76), stNote_2 TEXT (76), stNote_3 TEXT (76), stNote_4 TEXT (76), stNote_5 TEXT (76) ); CREATE TABLE current( cr_rec_no INTEGER PRIMARY KEY, stCurDate TEXT (12), sfElev DECIMAL(6, 2), sfTempFah DECIMAL(6,2), sfRelHumid DECIMAL (4,3), sfVapPress DECIMAL (4,3), sfBaroPress DECIMAL 4,3), stType CHAR(1), stWgt DECIMAL (5,2), sfMPHcf DECIMAL (5, 4), sfETcf DECMIAL (5, 4), stNote_1 TEXT (76), stNote_2 TEXT (76), stNote_3 TEXT (76), stNote_4 TEXT (76), stNote_5 TEXT (76) ); Now I have a batch file that I use to construct the actual databases. Here is that batch file: REM ** REM db.bat - this batch file creats database for use with Performance Tuner REM ** del *.bak REM sqlite3 -init event.sql pt__x3xx.db REM sqlite3 -init event.sql event.db REM C:\Documents and Settings\All Users\Documents\src\perftune\sqlite_test>sqlite3 - REM init event.sql event.db REM -- Loading resources from event.sql REM SQLite version 3.7.5 REM Enter ".help" for instructions REM Enter SQL statements terminated with a ";" REM sqlite> .exit REM sqlite3 -init timeslip.sql timeslip.db REM C:\Documents and Settings\All Users\Documents\src\perftune\sqlite_test>sqlite3 - REM init timeslip.sql timeslip.db REM -- Loading resources from timeslip.sql REM Error: near line 3: near "stEvent_date": syntax error REM sqlite3 -init baseline.sql baseline.db REM C:\Documents and Settings\All Users\Documents\src\perftune\sqlite_test>sqlite3 - REM init baseline.sql baseline.db REM -- Loading resources from baseline.sql REM Error: near line 2: near "4": syntax error REM sqlite3 -init current.sql current.db REM C:\Documents and Settings\All Users\Documents\src\perftune\sqlite_test>sqlite3 - REM init current.sql current.db REM -- Loading resources from current.sql REM Error: near line 3: near "4": syntax error I have added the various error msgs I get when running the batch file. You'll notice that the lines that construct event.db run fine. The other 3, not so much. Most confusing to me are the msgs for baseline.sql and current.sql. I can discern no difference between those files and event.sql. I have tried to search the on-line documentation but a search on "error" turns up MANY, MANY entries. So I thought I'd turn to the mailing list. I should also note that I only run the batch file for one table at a time, the others are commented out. Any help would be