Subject Re: [firebird-support] Re: Firebird, Access and views, ODBC tracing
Author Eric E
Hi Alan,
I think we're getting to the bottom of this.

>when you say insert into this view in marathon - do you mean in the grid of
>data you use the insert key? in which case marathon is issuing an insert
>into the underlying table. try creating a view of multiple tables (join) and
>see if marathon can do that too?
>using isql you can't INSERT INTO VIEWNAME(FIELD1, etc) VALUES (val1, etc)
>can you?
In ISQL I just tested :
INSERT INTO VIEWNAME(Field1,Field2,Field3,Field4) VALUES
and it worked fine.

I would not expect to be able to insert into a view with multiple joins
(or even necessarily any joins), but this view only has one table. I
don't know if the data grid in Marathon inserts into the table or the view.

>you also (must) need to issue an INSERT INTO TABLENAME(FIELD1, etc etc.. No?
>and ODBC is the same.
Not in my experience. I have successfully executed the above insert
statement via ODBC using an Access pass-thru query. The pass-thru query
passes that exact text to the Firebird server, while insert the row in
table view in Access executes:
INSERT INTO VIEWNAME(Field1,Field2,Field3,Field4) VALUES (?,?,?,?)

and bindsValue1, Value2,Value3, Value4 as parameters. This latter
operation fails.

>>So using ODBC I cannot insert into a view as though it were any other
>>but as long as I arrange for the SQL to go directly to Firebird
>? as long as it's INSERT INTO UNDERLYINGTABLENAME(FIELD1, etc etc...
Not that I know of. At some stage,Firebird is obviously inserting the
rows into the underlying table, but this appears to be transparent to
ODBC. This is as it should be IMHO - a view whose joins do not make it
impossible to insert should behave just like a table as far as the
client knows. That is, I should be able to expose a view without
clients knowing where its fields came from, so that I can change the
underlying table structure as I please.

>>then I can insert into my view as I please (provided
>>the view accepts insert into). Is this correct?
>When you use IBO components for connecting to and using a FB database, the
>components allow you to query a view and you can choose to construct an
>INSERT and a separate UPDATE SQL statements to go along with the query. When
>you want to add (insert) a new record into the view, the designed SQL
>statement is issued for you by IBO but it is not in the form INSERT INTO
>FIELD1=value1 etc..
OK, now I'm starting to understand the confusion. While it's remotely
possible that ODBC does this kind of translatation, I don't believe it
does, and if so it is invisble to the traces that I have done. This
where I got the idea that it's an ODBC bug.