Hello!

I want to use ElevateDb instead of Absolute Database very mach, but from the start I have a problems with right SQL syntax. So, I   ask somebody more experienced to help me with several easy problems:

1) That is wrong in this query:
SELECT * from tablename
where CAST(’21.12.2010’ AS DATE ) - CAST(data AS DATE )>=20

2) How to execute several sql statetments in one sql text? As I understand it should be done with the EDBscript component?
I tried, but there are errors:
SCRIPT
begin
insert into tablename ('fieldname') values (test');
insert into tablename ('fieldname') values (test2');
end

I read the manuals 3 days, searched forum, but no result to solve these problems. Please, help me. My opinion, that manuals has a lack of practical easy samples, like a samples in chapters 1 and 2 (tutorial databases).

Regards,
Andrews

Andrews,

<< 1) That is wrong in this query:
SELECT * from tablename
where CAST(’21.12.2010’ AS DATE ) - CAST(data AS DATE )>=20 >>

ElevateDB uses ANSI/ISO standard date/time formats, and also has complete
date/time interval support, so the correct query is:

SELECT * from tablename
where (DATE '2010-12-21' - CAST(data AS DATE )) >= INTERVAL '20' DAY

This assumes that the "data" column is in the valid ANSI/ISO date format
(mm-dd-yyyy) for the CAST.  CAST can accommodate generating different
date/time formats when casting to a VARCHAR/CHAR, but not the other way
around.

Also, be sure to read up on the INTERVAL support, if you plan on doing a lot
of date/time calculations:

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Interval_Types

<< 2) How to execute several sql statetments in one sql text? As I
understand it should be done with the EDBscript component?
I tried, but there are errors: >>

ElevateDB uses dynamic SQL for the body of all scripts, procedures,
functions, triggers, and jobs.  That means that any DML (INSERT, UPDATE,
DELETE, SELECT) statements must be prepared and executed with the
PREPARE/EXECUTE statements, or executed once using the EXECUTE IMMEDIATE
statement.  This is what allows EDB to easily mix DDL (CREATE TABLE, etc.)
with DML in any routine.

In your example, you need to use the EXECUTE IMMEDIATE statement for the SQL
execution, like this:

SCRIPT
begin
EXECUTE IMMEDIATE 'insert into tablename (fieldname) values (''test'')';
EXECUTE IMMEDIATE 'insert into tablename (fieldname) values (''test2'')';
end

Note the use of the doubled-up single quotes in the INSERT statements in
order to escape the constants.  You can also use a parameterized statement
to avoid having to double-up the single quotes:

SCRIPT
begin
EXECUTE IMMEDIATE 'insert into tablename (fieldname) values (?)' USING
'test';
EXECUTE IMMEDIATE 'insert into tablename (fieldname) values (?)' USING
'test2';
end

This is especially useful if you want to pass parameters to a
script/procedure directly into an SQL statement.

<< I read the manuals 3 days, searched forum, but no result to solve these
problems. Please, help me. My opinion, that manuals has a lack of practical
easy samples, like a samples in chapters 1 and 2 (tutorial databases). >>

If you run into issues like this, feel free to post a question here or send
an email to support@elevatesoft.com.  That's the purpose of these support
forums and our email support - to augment information that is not
particularly clear from the manuals.

--
Tim Young
Elevate Software
www.elevatesoft.com

Dear Team!

Thank You very much for Your comprehensive and quick answer. Your answer is another strong argument for ElevateDB. I'm very appreciated to You. So my learning of ElevateDB is going on.

Regards,
Andrews

Dear Tim!

I'm very sorry for mistake in Your name. Please excuse me and my poor english.

Regards,
Andrews

Andrew,

<< I'm very sorry for mistake in Your name. Please excuse me and my poor
english. >>

No problem at all.

--
Tim Young
Elevate Software
www.elevatesoft.com