Re: [gnome-db] Last insert feature request

["Daniel Espinosa" <esodan gmail com>]

2007/12/6, Piotr Pokora <piotrek pokora gmail com>:
> "Daniel Espinosa" <esodan gmail com> wrote:
>
> Hi!
>
> > > Wouldn't it be easier and more flexible to retrieve primary column name using
> > > provider's reflection? I know that last_insert_id is sometimes helpfull , but in practice
> > > this functionality is useless with SQLite ( IIRC ) and may foolish you with MySQL.
> > >
> > > Personally I would store primary column name as "application's parameter" instead of
> > > fetching it with every insert command.
> >
> > But how do you get the value in the primary key column for the last
> > inserted row?
>
> I do not say this API function is bad.
> I think returned gchar is not good and GValue as returned data type seems to be the best.
>
> > What do you mean about "provider's reflection"?
>
> I didn't check this in practice, but you could get this infor using provider's schema.
> get_schema function with GDA_CONNECTION_SCHEMA_FIELDS identifier.
>
> My point is that GDA *should not* abuse returned data in this particular case.
> Returned GdaParameter could be ( IMO ) a bit overloaded. It's up to the application and developer
> to get as many information as needed. So if two data are needed ( a name and value ) two functions
> should be called instead of one.
>

Let me expose my use case:

- I'm creating a GObject class to easy manage one data row and only
one data row in the database. This object will allow you to modify the
field's values (update), get field's values (select), and to get all
objects from the same type.

- I need to insert this data using any method and then get the ID
value, the table and the column, in order to allow this object to
use/modify the data using UPDATEs commands in the form:

UPDATE table SET col = val1 ... WHERE id_column_name = id_value

or

DELETE FROM table WHERE id_column_name = id_value

where the table, id_column_name and id_value, must be setted when the
object is created.

or SELECT commands using:

SELECT * FROM table WHERE id_column_name = id_name

Then when a data is stored in the database for the first time and I
want to return a GObject to use, modify or delete it.

The problem rise becouse PostgreSQL and SQLite providers (MySQL i
don't know), returns an string for the last insert id, but this string
'means' different things depending on the provider, then I think is
important to add a function in order to get a GValue and the column
this values comes from, this allows the caller to run a simple query
using this information to get the data from the DB to know the value
in the Primary Key Column setted automaticaly by the DBMS.  Of course
you allways know the the table and Primary Key Column for the data you
are using, but providers not.

I thing this task could be too common, then could be better that the
provider returns a GdaParameter with the column name and value that
the caller can use in a query.

I plan to propose this object to be included in GDA. This object could
be called GdaDataObjectProxy, becouse you can change the values in the
fields but they will be commited ufter a gda_data_object_proxy_update
() is called. This object doesn't use a GdaDataModelProxy, but
GdaParameter and GdaParameterList, in order to call an UPDATE or
DELETE queries directly, this object dinamicaly construct and register
SELECT, UPDATE and DELETE queries in a GdaDictObject for the current
GdaConnection, using the GdaDictFields in the GdaDictTable setted when
the object is created.

-- 
Trabajar, la mejor arma para tu superación
"de grano en grano, se hace la arena" (R) (entrámite, pero para los
cuates: LIBRE)