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

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

"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

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

"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