Hi

I created a table in edb manager but the script failed in the end.
Here's the script (reduced to the minimum that fails):

EXECUTE IMMEDIATE 'CREATE TABLE "generictypes"
(
"intgenexp" INTEGER GENERATED ALWAYS AS 2 NOT NULL
)
VERSION 1
UNENCRYPTED
INDEX PAGE SIZE 8192
BLOB BLOCK SIZE 1024
PUBLISH BLOCK SIZE 1024
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';

--
regards,
Lucian

Lucian


This works

CREATE TABLE "generictypes"
(
"intgenexp" INTEGER GENERATED ALWAYS AS 2
)



The poor things probably getting confused by saying generate as a constant

Roy Lambert [Team Elevate]

> The poor things probably getting confused by saying generate as a
> constant

Not sure about that. The initial expression was "2*otherfield" (so it's
not a constant), but I said I reduced the script to the minimum
exposing the problem.

regards,
Lucian

Lucian,

skip the "NOT NULL".

Its seems that it's not allowed if a column is generated as expression.

Uli

Lucian,

<< I created a table in edb manager but the script failed in the end. Here's
the script (reduced to the minimum that fails): >>

It's the NOT NULL.  The parser gets confused by the NOT NULL, thinking that
the column expression is 2 NOT <BETWEEN, LIKE, IN>, so currently there isn't
any way to do what you want.

--
Tim Young
Elevate Software
www.elevatesoft.com

> skip the "NOT NULL".


Hi Uli,

I don't have a problem with that specific table. That is just a test I
did in edb manager because something wasn't working in my app.

I (finally) got lots of time to work on automatically restructuring
tables based on my own database definition script. So my app relies
100% on syntax I read in the help files. The syntax MUST work. I look
at the help file and implement my crap. If the syntax is BS, I need
another job


<ColumnName> <ColumnDefinition>|
<ConstraintName> <ConstraintDefinition>


<ColumnDefinition> =
GENERATED <GenerationOptions>

<ColumnConstraintDefinition> =
NOT NULL


So this should be correct? or not?

<ColumnName> GENERATED <GenerationOptions> NOT NULL



regards,
Lucian

> so currently there isn't any way to do what you want

So Tim, you're saying that after you took AlterTable from us, now that
I am trying to replace it with all this SQL stuff, it's going to be
very hard, if not impossible because one can't even rely on syntax
being corect?

regards,
Lucian

Lucian,

<< So Tim, you're saying that after you took AlterTable from us, now that I
am trying to replace it with all this SQL stuff, it's going to be very hard,
if not impossible because one can't even rely on syntax being corect? >>

No, I'm saying that in this one, and only one, instance, the NOT NULL
ordering according to the SQL standard causes the parser to choke because it
cannot determine whether the NOT is part of the GENERATED/COMPUTED
expression, or part of the normal NOT NULL.

And, actually, I forgot that you can just do this:

CREATE TABLE "generictypes"
(
"intgenexp" INTEGER GENERATED ALWAYS AS 2
CONSTRAINT "intgenexp_req" CHECK "intgenexp" IS NOT NULL
)

--
Tim Young
Elevate Software
www.elevatesoft.com

Lucian,

I found myself in the same situation after cancelling AlterTable function in
ElevateDB. Please find in elevatesoft.public.elevatedb.extensions group the
'Reverse Engineer to Pascal Code' item where my solution of the problem is
described. The intention was to make similar approach to manage all tables'
structures alterations by Pascal code as it was in DBISAM with AlterTable
function. It may help you to find your own way to do so.

Regards
Iztok Lajovic

"Lucian Radulescu" <lucianATez-delphiDOTcom> je napisal v sporoèilo
news:xn0govcs6uumdv004@news.elevatesoft.com ...
>> so currently there isn't any way to do what you want
>
> So Tim, you're saying that after you took AlterTable from us, now that
> I am trying to replace it with all this SQL stuff, it's going to be
> very hard, if not impossible because one can't even rely on syntax
> being corect?
>
> regards,
> Lucian
>

Tim


>And, actually, I forgot that you can just do this:
>
>CREATE TABLE "generictypes"
>(
>"intgenexp" INTEGER GENERATED ALWAYS AS 2
>CONSTRAINT "intgenexp_req" CHECK "intgenexp" IS NOT NULL
>)

Which I presume is what EDBManager will soon do on our behalf?

Leaving aside the philosophical debate on wether or not it should be the precursor elements of the calculation that should be checked rather than the result I think Lucian's main moan was that the documentation didn't tell him, and EDBManager generated unusable code. The nice thing is that enhancement which stuffs the duff SQL into another window. I don't know about Uli but it took me c30 secs to spot the workround

Roy Lambert