Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 5 of 5 total |
Insert (script) |
Sat, Nov 10 2007 9:40 AM | Permanent Link |
Dieter Nagy | Hello, I use ElevateDB 1.06.
When I use the script Script Begin EXECUTE IMMEDIATE 'CREATE TABLE "Lottozahlen" ( "REC" SMALLINT, "Runde" INTEGER, "Datum" DATE, "Z1" SMALLINT, "Z2" SMALLINT, "Z3" SMALLINT, "Z4" SMALLINT, "Z5" SMALLINT, "Z6" SMALLINT, "ZZ" SMALLINT, CONSTRAINT "Recodcount" PRIMARY KEY ("REC") ) VERSION 1 UNENCRYPTED INDEX PAGE SIZE 4096 BLOB BLOCK SIZE 512 MAX ROW BUFFER SIZE 32768 MAX INDEX BUFFER SIZE 65536 MAX BLOB BUFFER SIZE 32768'; INSERT INTO "LOTTOZAHLEN" VALUES (1, 1, '1986-09-07', 1, 20, 22, 24, 27, 40, 12); END Then I got the error: ElevateDB Error #700 An error was found in the script at line 25 and column 13(Invalid expression LOTTOZAHLEN found, the referenced cursor does not exist) what do I wrong TIA Dieter |
Sat, Nov 10 2007 1:01 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Dieter,
Use this instead: Script Begin EXECUTE IMMEDIATE 'CREATE TABLE "Lottozahlen" ( "REC" SMALLINT, "Runde" INTEGER, "Datum" DATE, "Z1" SMALLINT, "Z2" SMALLINT, "Z3" SMALLINT, "Z4" SMALLINT, "Z5" SMALLINT, "Z6" SMALLINT, "ZZ" SMALLINT, CONSTRAINT "Recodcount" PRIMARY KEY ("REC") ) VERSION 1 UNENCRYPTED INDEX PAGE SIZE 4096 BLOB BLOCK SIZE 512 MAX ROW BUFFER SIZE 32768 MAX INDEX BUFFER SIZE 65536 MAX BLOB BUFFER SIZE 32768'; EXECUTE IMMEDIATE 'INSERT INTO "LOTTOZAHLEN" VALUES (1, 1, ''1986-09-07'', 1, 20, 22, 24, 27, 40, 12)'; END All dynamic statements (INSERT, UPDATE, DELETE, and SELECT) that don't operate on a declared cursor must be executed with EXECUTE IMMEDIATE or PREPARE/EXECUTE statements: http://www.elevatesoft.com/edb1sql_sql_psm_statements.htm You'll notice that the cursor-based INSERT, UPDATE, and DELETE statements work on a cursor and not a direct table reference: http://www.elevatesoft.com/edb1sql_cursor_insert.htm http://www.elevatesoft.com/edb1sql_cursor_update.htm http://www.elevatesoft.com/edb1sql_cursor_delete.htm Think of the SQL/PSM INSERT, UPDATE, and DELETE statements as being the same as the Insert, Edit, and Delete (plus Post) methods of the TEDBQuery component. They operate on an open query cursor at the current row pointer in that cursor. -- Tim Young Elevate Software www.elevatesoft.com |
Sat, Nov 10 2007 1:16 PM | Permanent Link |
Dieter Nagy | "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:
Dieter, Use this instead: Script Begin EXECUTE IMMEDIATE 'CREATE TABLE "Lottozahlen" ( "REC" SMALLINT, "Runde" INTEGER, "Datum" DATE, "Z1" SMALLINT, "Z2" SMALLINT, "Z3" SMALLINT, "Z4" SMALLINT, "Z5" SMALLINT, "Z6" SMALLINT, "ZZ" SMALLINT, CONSTRAINT "Recodcount" PRIMARY KEY ("REC") ) VERSION 1 UNENCRYPTED INDEX PAGE SIZE 4096 BLOB BLOCK SIZE 512 MAX ROW BUFFER SIZE 32768 MAX INDEX BUFFER SIZE 65536 MAX BLOB BUFFER SIZE 32768'; EXECUTE IMMEDIATE 'INSERT INTO "LOTTOZAHLEN" VALUES (1, 1, ''1986-09-07'', 1, 20, 22, 24, 27, 40, 12)'; END All dynamic statements (INSERT, UPDATE, DELETE, and SELECT) that don't operate on a declared cursor must be executed with EXECUTE IMMEDIATE or PREPARE/EXECUTE statements: http://www.elevatesoft.com/edb1sql_sql_psm_statements.htm You'll notice that the cursor-based INSERT, UPDATE, and DELETE statements work on a cursor and not a direct table reference: http://www.elevatesoft.com/edb1sql_cursor_insert.htm http://www.elevatesoft.com/edb1sql_cursor_update.htm http://www.elevatesoft.com/edb1sql_cursor_delete.htm Think of the SQL/PSM INSERT, UPDATE, and DELETE statements as being the same as the Insert, Edit, and Delete (plus Post) methods of the TEDBQuery component. They operate on an open query cursor at the current row pointer in that cursor. -- Tim Young Elevate Software www.elevatesoft.com Thanks Tim, now I get the error: ElevateDB Error #700 An error was found in the script at line 1 and column 41(Expected NULL or Date expression but instead found '1986-09-07' Please help TIA Dieter |
Sat, Nov 10 2007 2:20 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Dieter,
<< now I get the error: ElevateDB Error #700 An error was found in the script at line 1 and column 41(Expected NULL or Date expression but instead found '1986-09-07' >> Sorry, I missed that - you need to use DATE 'yyyy-mm-dd' in ElevateDB: http://www.elevatesoft.com/edb1sql_date_time_types.htm so use this instead: Script Begin EXECUTE IMMEDIATE 'CREATE TABLE "Lottozahlen" ( "REC" SMALLINT, "Runde" INTEGER, "Datum" DATE, "Z1" SMALLINT, "Z2" SMALLINT, "Z3" SMALLINT, "Z4" SMALLINT, "Z5" SMALLINT, "Z6" SMALLINT, "ZZ" SMALLINT, CONSTRAINT "Recodcount" PRIMARY KEY ("REC") ) VERSION 1 UNENCRYPTED INDEX PAGE SIZE 4096 BLOB BLOCK SIZE 512 MAX ROW BUFFER SIZE 32768 MAX INDEX BUFFER SIZE 65536 MAX BLOB BUFFER SIZE 32768'; EXECUTE IMMEDIATE 'INSERT INTO "LOTTOZAHLEN" VALUES (1, 1, DATE ''1986-09-07'', 1, 20, 22, 24, 27, 40, 12)'; END Notice the prefixed keyword DATE before the actual quoted date constant. -- Tim Young Elevate Software www.elevatesoft.com |
Sat, Nov 10 2007 4:10 PM | Permanent Link |
Dieter Nagy | "Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:
Dieter, << now I get the error: ElevateDB Error #700 An error was found in the script at line 1 and column 41(Expected NULL or Date expression but instead found '1986-09-07' >> Sorry, I missed that - you need to use DATE 'yyyy-mm-dd' in ElevateDB: http://www.elevatesoft.com/edb1sql_date_time_types.htm so use this instead: Script Begin EXECUTE IMMEDIATE 'CREATE TABLE "Lottozahlen" ( "REC" SMALLINT, "Runde" INTEGER, "Datum" DATE, "Z1" SMALLINT, "Z2" SMALLINT, "Z3" SMALLINT, "Z4" SMALLINT, "Z5" SMALLINT, "Z6" SMALLINT, "ZZ" SMALLINT, CONSTRAINT "Recodcount" PRIMARY KEY ("REC") ) VERSION 1 UNENCRYPTED INDEX PAGE SIZE 4096 BLOB BLOCK SIZE 512 MAX ROW BUFFER SIZE 32768 MAX INDEX BUFFER SIZE 65536 MAX BLOB BUFFER SIZE 32768'; EXECUTE IMMEDIATE 'INSERT INTO "LOTTOZAHLEN" VALUES (1, 1, DATE ''1986-09-07'', 1, 20, 22, 24, 27, 40, 12)'; END Notice the prefixed keyword DATE before the actual quoted date constant. -- Tim Young Elevate Software www.elevatesoft.com Thanks Tim |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |