Re: [firebird-support] Re: UPDATE OR INSERT in Firebird 3

2020-11-20 Thread Herman Viaene herman.via...@edpnet.be [firebird-support] 
Op 20/11/2020 om 01:04 schreef 'Walter R. Ojeda Valiente' 
sistemas2000profesio...@gmail.com [firebird-support]:


I use firebird3 from libreoffice base, not directly, but in that way I 
can make (and have done) such insert stattements.

CREATE TABLE MYTABLE (
  COLUMN1 INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT 
NULL,

  COLUMN2 VARCHAR(20));


ALTER TABLE MYTABLE ADD CONSTRAINT PK_MYTABLE PRIMARY KEY (COLUMN1);


I have similar tables



Then, is I write it:
UPDATE OR INSERT INTO MYTABLE (COLUMN1, COLUMN2) VALUES (NULL, 'TEST')

The message is:
Validation error for column "MYTABLE"."COLUMN1", value "*** null ***".


That is quite normal and correct, since you define column1 as not null, 
and now you try to insert a null value in it.




And if I write it:
UPDATE OR INSERT INTO MYTABLE (COLUMN2) VALUES ('TEST')


I have written similar insert statements and they work OK, BUT: I write 
either an insert statement OR an update statement. The two are 
fundamentally different as to the primary key handling:
in an insert statement you do not give a value to the PK, since it is 
generated by the system.
in an update statement you have to define on which rows (all or by 
giving a value for the PK or by a select statement) the update has to apply.


I must confess I've never tried to use "UPDATE OR INSERT", and I wonder 
about it.  I googled and checked the syntax, and I think your statement 
will try to match your value 'TEST' against the PK, and that does not 
work of course. You would have to use the "matching (column2)" in your 
statement.


Just my 2c

Herman Viaene





The message is:
UPDATE OR INSERT field list does not match primary key of table MYTABLE.


There is some solution? Or it is impossible to use an IDENTITY column 
with an UPDATE OR INSERT?


Greetings.

Walter.




On Thu, Nov 19, 2020 at 8:55 PM Walter R. Ojeda Valiente 
> wrote:


I forget to say that my IDENTITY column is the Primary Key of
MyTable, therefore if I don't write it an error happens
because...the table needs a Primary Key.

On Thu, Nov 19, 2020 at 8:52 PM Walter R. Ojeda Valiente
mailto:sistemas2000profesio...@gmail.com>> wrote:

Hello everybody

A long, long time without writing neither reading this group,
mostly because all that I need about Firebird I knew.

But now, I have a doubt.

With Firebird 2.5.x I can have an auto-incremental column and
use it in an UPDATE OR INSERT, but such thing is not possible
with Firebird 3.

With Firebird 2.5.x a generator and a trigger are created and
the value of the column is put automatically. So, if I write:
UPDATE OR INSERT INTO MyTable (MyColumn1, MyColumn2) VALUES
(NULL, 12345);

and MyColumn1 is auto-incremental its value is set for the
Firebird engine.

but...if I use the new IDENTITY type in Firebird 3 I always
have an error, writing MyColumn1 in the UPDATE OR INSERT or
not writing.

So, my question is:

Is it possible to use UPDATE OR INSERT with an IDENTITY column?

Thanks in advance.

Greetings.

Walter.





--
Plus je dors, mieux je me porte (Gaston Lagaffe)

Hoe meer ik slaap, hoe beter ik me voel (Guus Flater)

The more I sleep, the better I feel (Gomer Goof)

Re: [firebird-support] Re: UPDATE OR INSERT in Firebird 3

2020-11-20 Thread 'Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support] 
Hello Herman

I had used UPDATE OR INSERT for several years in several hundred of tables.
No problem...with Firebird 2.5.x

But with Firebird 3.0.x I can not make it work.

If I write the identity column, doesn't work. If I don't write the identity
column, doesn't work.

Thank you very much for your answer.

Greetings.

Walter.



On Fri, Nov 20, 2020 at 6:16 AM Herman Viaene herman.via...@edpnet.be
[firebird-support]  wrote:

>
>
> Op 20/11/2020 om 01:04 schreef 'Walter R. Ojeda Valiente'
> sistemas2000profesio...@gmail.com [firebird-support]:
>
> I use firebird3 from libreoffice base, not directly, but in that way I can
> make (and have done) such insert stattements.
>
>
> CREATE TABLE MYTABLE (
>   COLUMN1 INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
>   COLUMN2 VARCHAR(20));
>
>
> ALTER TABLE MYTABLE ADD CONSTRAINT PK_MYTABLE PRIMARY KEY (COLUMN1);
>
>
> I have similar tables
>
>
> Then, is I write it:
> UPDATE OR INSERT INTO MYTABLE (COLUMN1, COLUMN2) VALUES (NULL, 'TEST')
>
> The message is:
> Validation error for column "MYTABLE"."COLUMN1", value "*** null ***".
>
>
> That is quite normal and correct, since you define column1 as not null,
> and now you try to insert a null value in it.
>
>
> And if I write it:
> UPDATE OR INSERT INTO MYTABLE (COLUMN2) VALUES ('TEST')
>
>
> I have written similar insert statements and they work OK, BUT: I write
> either an insert statement OR an update statement. The two are
> fundamentally different as to the primary key handling:
> in an insert statement you do not give a value to the PK, since it is
> generated by the system.
> in an update statement you have to define on which rows (all or by giving
> a value for the PK or by a select statement) the update has to apply.
>
> I must confess I've never tried to use "UPDATE OR INSERT", and I wonder
> about it.  I googled and checked the syntax, and I think your statement
> will try to match your value 'TEST' against the PK, and that does not work
> of course. You would have to use the "matching (column2)" in your statement.
>
> Just my 2c
>
> Herman Viaene
>
>
>
>
> The message is:
> UPDATE OR INSERT field list does not match primary key of table MYTABLE.
>
>
> There is some solution? Or it is impossible to use an IDENTITY column with
> an UPDATE OR INSERT?
>
> Greetings.
>
> Walter.
>
>
>
>
> On Thu, Nov 19, 2020 at 8:55 PM Walter R. Ojeda Valiente <
> sistemas2000profesio...@gmail.com> wrote:
>
>> I forget to say that my IDENTITY column is the Primary Key of MyTable,
>> therefore if I don't write it an error happens because...the table needs a
>> Primary Key.
>>
>> On Thu, Nov 19, 2020 at 8:52 PM Walter R. Ojeda Valiente <
>> sistemas2000profesio...@gmail.com> wrote:
>>
>>> Hello everybody
>>>
>>> A long, long time without writing neither reading this group, mostly
>>> because all that I need about Firebird I knew.
>>>
>>> But now, I have a doubt.
>>>
>>> With Firebird 2.5.x I can have an auto-incremental column and use it in
>>> an UPDATE OR INSERT, but such thing is not possible with Firebird 3.
>>>
>>> With Firebird 2.5.x a generator and a trigger are created and the value
>>> of the column is put automatically. So, if I write:
>>> UPDATE OR INSERT INTO MyTable (MyColumn1, MyColumn2) VALUES (NULL,
>>> 12345);
>>>
>>> and MyColumn1 is auto-incremental its value is set for the Firebird
>>> engine.
>>>
>>> but...if I use the new IDENTITY type in Firebird 3 I always have an
>>> error, writing MyColumn1 in the UPDATE OR INSERT or not writing.
>>>
>>> So, my question is:
>>>
>>> Is it possible to use UPDATE OR INSERT with an IDENTITY column?
>>>
>>> Thanks in advance.
>>>
>>> Greetings.
>>>
>>> Walter.
>>>
>> 
>

Re: [firebird-support] Re: UPDATE OR INSERT in Firebird 3

2020-11-24 Thread Daniel Miller dmil...@amfes.com [firebird-support] 
I experimented with IDENTITY fields myself - based on my experience and 
some older comments here I quit using them. I think they are presently 
suitable for "quick" tables - something that will be append only like a log.


If you're looking for more advanced features, like supporting UPDATE OR 
INSERT (which I use myself), then you're much better off explicitly 
writing the appropriate insert and update triggers. Possibly version 4 
will implement IDENTITY better but I don't use it all now.


Daniel

On 11/20/2020 5:59 AM, 'Walter R. Ojeda Valiente' 
sistemas2000profesio...@gmail.com [firebird-support] wrote:



Hello Herman

I had used UPDATE OR INSERT for several years in several hundred of 
tables. No problem...with Firebird 2.5.x


But with Firebird 3.0.x I can not make it work.

If I write the identity column, doesn't work. If I don't write the 
identity column, doesn't work..


Thank you very much for your answer.

Greetings.

Walter.



On Fri, Nov 20, 2020 at 6:16 AM Herman Viaene herman.via...@edpnet.be 
 [firebird-support] 
> wrote:


Op 20/11/2020 om 01:04 schreef 'Walter R. Ojeda Valiente'
sistemas2000profesio...@gmail.com
 [firebird-support]:

I use firebird3 from libreoffice base, not directly, but in that
way I can make (and have done) such insert stattements.

CREATE TABLE MYTABLE (
  COLUMN1 INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1)
NOT NULL,
  COLUMN2 VARCHAR(20));


ALTER TABLE MYTABLE ADD CONSTRAINT PK_MYTABLE PRIMARY KEY (COLUMN1);


I have similar tables



Then, is I write it:
UPDATE OR INSERT INTO MYTABLE (COLUMN1, COLUMN2) VALUES (NULL,
'TEST')

The message is:
Validation error for column "MYTABLE"."COLUMN1", value "*** null
***".


That is quite normal and correct, since you define column1 as not
null, and now you try to insert a null value in it.



And if I write it:
UPDATE OR INSERT INTO MYTABLE (COLUMN2) VALUES ('TEST')


I have written similar insert statements and they work OK, BUT: I
write either an insert statement OR an update statement. The two
are fundamentally different as to the primary key handling:
in an insert statement you do not give a value to the PK, since it
is generated by the system.
in an update statement you have to define on which rows (all or by
giving a value for the PK or by a select statement) the update has
to apply.

I must confess I've never tried to use "UPDATE OR INSERT", and I
wonder about it.  I googled and checked the syntax, and I think
your statement will try to match your value 'TEST' against the PK,
and that does not work of course. You would have to use the
"matching (column2)" in your statement.

Just my 2c

Herman Viaene





The message is:
UPDATE OR INSERT field list does not match primary key of table
MYTABLE.


There is some solution? Or it is impossible to use an IDENTITY
column with an UPDATE OR INSERT?

Greetings.

Walter.




On Thu, Nov 19, 2020 at 8:55 PM Walter R. Ojeda Valiente
mailto:sistemas2000profesio...@gmail.com>> wrote:

I forget to say that my IDENTITY column is the Primary Key of
MyTable, therefore if I don't write it an error happens
because...the table needs a Primary Key.

On Thu, Nov 19, 2020 at 8:52 PM Walter R. Ojeda Valiente
mailto:sistemas2000profesio...@gmail.com>> wrote:

Hello everybody

A long, long time without writing neither reading this
group, mostly because all that I need about Firebird I knew.

But now, I have a doubt.

With Firebird 2.5.x I can have an auto-incremental column
and use it in an UPDATE OR INSERT, but such thing is not
possible with Firebird 3.

With Firebird 2.5.x a generator and a trigger are created
and the value of the column is put automatically. So, if
I write:
UPDATE OR INSERT INTO MyTable (MyColumn1, MyColumn2)
VALUES (NULL, 12345);

and MyColumn1 is auto-incremental its value is set for
the Firebird engine.

but...if I use the new IDENTITY type in Firebird 3 I
always have an error, writing MyColumn1 in the UPDATE OR
INSERT or not writing.

So, my question is:

Is it possible to use UPDATE OR INSERT with an IDENTITY
column?

Thanks in advance.

Greetings.

Walter.

Re: [firebird-support] Re: UPDATE OR INSERT in Firebird 3

2020-11-24 Thread 'Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support] 
Hello Daniel

Yes, I agree with you, IDENTITY columns are useless for me, so I quit using
them. Maybe for a log table or something so can serve, but for normal work
they are useless.

I had returned to the old and well suitable practice of generator/trigger,
so with a short stored procedure I can INSERT and UPDATE too. Fewer lines
of code are a good practice according to my point of view.

Greetings.

Walter.


On Tue, Nov 24, 2020 at 3:32 PM Daniel Miller dmil...@amfes.com
[firebird-support]  wrote:

>
>
> I experimented with IDENTITY fields myself - based on my experience and
> some older comments here I quit using them. I think they are presently
> suitable for "quick" tables - something that will be append only like a log.
>
> If you're looking for more advanced features, like supporting UPDATE OR
> INSERT (which I use myself), then you're much better off explicitly writing
> the appropriate insert and update triggers. Possibly version 4 will
> implement IDENTITY better but I don't use it all now.
>
> Daniel
>
> On 11/20/2020 5:59 AM, 'Walter R. Ojeda Valiente'
> sistemas2000profesio...@gmail.com [firebird-support] wrote:
>
> 
>