You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@empire-db.apache.org by McKinley <mc...@gmail.com> on 2010/01/15 15:37:41 UTC
Copy and Insert DBRecord (Urgent for me)
I'd like to get a DBRecord and use it as a template for an insert. Is
that possible?
if(reader.getRecordData(cmd, conn)){
tableA.initRecord(recA, reader);
// I would like to create a new record where only KeyColumn1 and Name
// are different than the old one.
// can't do the following, key is read only;
//recA.setKeyColumn1(null);
recA.setName("Some New Name for a New Record");
recA.create(tableA, conn); // <-- still points to old primary key.
}
OK, thanks if you can help!
Cheers,
McKinley
re: Copy and Insert DBRecord (Urgent for me)
Posted by Rainer Döbele <do...@esteam.de>.
McKinley wrote:
> Re: Copy and Insert DBRecord (Urgent for me)
>
> On Fri, Jan 15, 2010 at 8:55 PM, Francis De Brabandere
> <fr...@gmail.com> wrote:
> > Feel free to contribute! The wiki should be open for everybody.
> > http://cwiki.apache.org/confluence/display/empiredb/Query+Building
>
> I registered on the wiki but it does not appear that I can edit. I'll
> send some wiki markup through the issue tracker for now.
>
> > I agree that sometimes getting things done is not that intuitive.
> > Better documentation or api might help there. On the other hand I
> have
> > to agree with Rainer. These days the power of the database is
> > sometimes forgotten.
>
> I agree with the philosophy of using the database as much as possible.
> What I found confusing about getInsertInto() was making sure that
> DBColumnExpr like t.colA.as("colA") are included in the select and
> straight t.colA are not. In the end it is essentially just like
> writing the statement.
There has to be some kind of mapping between the selection and the target columns. One should never rely on a particular order of column in the target table.
However renaming the column is only necessary if the name of the selected column is different or if it is an expression (i.e. transformation).
And you may as well use the other overload version of getInsertInto() where you can provide an array of target columns yourself.
So I think you have all options here that you may require.
>
> At first glance it looks like Empire-db does not support joins in
> insert and update statements. Is that true?
What makes you think it does not?
We're using it a lot with SQLServer.
However not all databases support the standard SQL syntax with joins in updates (i.e. Oracle).
Generally Empire-db should be able to create any kind of SQL statement (with a few database specific exceptions maybe).
>
> Thanks,
>
> McKinley
Rainer
Re: Copy and Insert DBRecord (Urgent for me)
Posted by McKinley <mc...@gmail.com>.
On Fri, Jan 15, 2010 at 8:55 PM, Francis De Brabandere
<fr...@gmail.com> wrote:
> Feel free to contribute! The wiki should be open for everybody.
> http://cwiki.apache.org/confluence/display/empiredb/Query+Building
I registered on the wiki but it does not appear that I can edit. I'll
send some wiki markup through the issue tracker for now.
> I agree that sometimes getting things done is not that intuitive.
> Better documentation or api might help there. On the other hand I have
> to agree with Rainer. These days the power of the database is
> sometimes forgotten.
I agree with the philosophy of using the database as much as possible.
What I found confusing about getInsertInto() was making sure that
DBColumnExpr like t.colA.as("colA") are included in the select and
straight t.colA are not. In the end it is essentially just like
writing the statement.
At first glance it looks like Empire-db does not support joins in
insert and update statements. Is that true?
Thanks,
McKinley
Re: Copy and Insert DBRecord (Urgent for me)
Posted by Francis De Brabandere <fr...@gmail.com>.
On Fri, Jan 15, 2010 at 9:02 PM, McKinley <mc...@gmail.com> wrote:
> On Fri, Jan 15, 2010 at 7:42 PM, Rainer Döbele <do...@esteam.de> wrote:
>>
>> I know it is tempting to use initRecord() but it has not been designed for doing what you want to do.
>> Rather it should be used for modifying existing records.
>
> Thanks for your example! I ended up using getInsertInto() which took a
> long time to learn how to use correctly. I think that Empire-db is the
> best thing out there right now, but there is a whole other API that
> could be build on top of it. I would like to come up with methods that
> return better references and generally provide more convenience in
> their arguments. After I have time to figure out the verbose areas of
> my code, I'll present some ideas.
>
>> IMO the right approach is to create a new record and copy all but pk and timestamp columns.
>
> This should definitely go into a convenience method with optional
> (keepPK, keepTs) arguments in the future. The best way to avoid using
> the wrong method is by finding a more tempting one :)
>
> Perhaps the best thing for now is to get a wiki for common query
> examples. Is that possible at Apache?
Feel free to contribute! The wiki should be open for everybody.
http://cwiki.apache.org/confluence/display/empiredb/Query+Building
I agree that sometimes getting things done is not that intuitive.
Better documentation or api might help there. On the other hand I have
to agree with Rainer. These days the power of the database is
sometimes forgotten.
Cheers,
Francis
>
> Thanks again,
>
> McKinley
>
--
http://www.somatik.be
Microsoft gives you windows, Linux gives you the whole house.
re: Copy and Insert DBRecord (Urgent for me)
Posted by Rainer Döbele <do...@esteam.de>.
Correct, using getInsertInto() is the preffered way to do this (shame on me for not telling you).
It is in the true spirit of Empire-db that says: If the database can do it, let the datbase do it.
So many people implement things in code that a clever sql statement could do much more efficiently.
However I don't think that extending initRecord() here is a good idea, simply because this is such a special case that it needs special handling and as you have found out, one should rather use getInsertInto(). If this is not possible then the copying logic is too complex anyway and it must be adressed invidiually.
We have a wiki for Emprire-db and there are even some query examples there:
http://cwiki.apache.org/confluence/display/empiredb/Query+Building
You are welcome to add more, but I am not sure whether you need write access priviledges.
Regards
Rainer
McKinley wrote:
> Re: Copy and Insert DBRecord (Urgent for me)
>
> On Fri, Jan 15, 2010 at 7:42 PM, Rainer Döbele <do...@esteam.de>
> wrote:
> >
> > I know it is tempting to use initRecord() but it has not been
> designed for doing what you want to do.
> > Rather it should be used for modifying existing records.
>
> Thanks for your example! I ended up using getInsertInto() which took a
> long time to learn how to use correctly. I think that Empire-db is the
> best thing out there right now, but there is a whole other API that
> could be build on top of it. I would like to come up with methods that
> return better references and generally provide more convenience in
> their arguments. After I have time to figure out the verbose areas of
> my code, I'll present some ideas.
>
> > IMO the right approach is to create a new record and copy all but pk
> and timestamp columns.
>
> This should definitely go into a convenience method with optional
> (keepPK, keepTs) arguments in the future. The best way to avoid using
> the wrong method is by finding a more tempting one :)
>
> Perhaps the best thing for now is to get a wiki for common query
> examples. Is that possible at Apache?
>
> Thanks again,
>
> McKinley
Re: Copy and Insert DBRecord (Urgent for me)
Posted by McKinley <mc...@gmail.com>.
On Fri, Jan 15, 2010 at 7:42 PM, Rainer Döbele <do...@esteam.de> wrote:
>
> I know it is tempting to use initRecord() but it has not been designed for doing what you want to do.
> Rather it should be used for modifying existing records.
Thanks for your example! I ended up using getInsertInto() which took a
long time to learn how to use correctly. I think that Empire-db is the
best thing out there right now, but there is a whole other API that
could be build on top of it. I would like to come up with methods that
return better references and generally provide more convenience in
their arguments. After I have time to figure out the verbose areas of
my code, I'll present some ideas.
> IMO the right approach is to create a new record and copy all but pk and timestamp columns.
This should definitely go into a convenience method with optional
(keepPK, keepTs) arguments in the future. The best way to avoid using
the wrong method is by finding a more tempting one :)
Perhaps the best thing for now is to get a wiki for common query
examples. Is that possible at Apache?
Thanks again,
McKinley
re: Copy and Insert DBRecord (Urgent for me)
Posted by Rainer Döbele <do...@esteam.de>.
I know it is tempting to use initRecord() but it has not been designed for doing what you want to do.
Rather it should be used for modifying existing records.
IMO the right approach is to create a new record and copy all but pk and timestamp columns.
In fact this is similar to what initRecord() does internally.
So basically the layout could be as follows:
reader.open(cmd, conn);
DBIndex pk = table.getPrimaryKey();
DBColumn ts = table.getTimestampColumn();
DBRecord rec = new DBRecord();
while (reader.moveNext())
{
rec.create(table);
for (DBColumn c : table.getColumns())
{ // ignore PK and timestamp columns
if (pk.contains(c) || c==ts)
continue;
// copy other fields
rec.setValue(c, reader.getValue(c));
}
rec.update(conn);
}
Regards
Rainer
McKinley wrote:
> re: Copy and Insert DBRecord (Urgent for me)
>
> I'd like to get a DBRecord and use it as a template for an insert. Is
> that possible?
>
> if(reader.getRecordData(cmd, conn)){
> tableA.initRecord(recA, reader);
> // I would like to create a new record where only KeyColumn1 and
> Name
> // are different than the old one.
> // can't do the following, key is read only;
> //recA.setKeyColumn1(null);
> recA.setName("Some New Name for a New Record");
> recA.create(tableA, conn); // <-- still points to old primary key.
> }
>
> OK, thanks if you can help!
>
> Cheers,
>
> McKinley