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 "mailinglist@j-b-s.de" <ma...@j-b-s.de> on 2012/10/10 13:51:13 UTC
Re: Batch inserts
Hi Guys!
Finally I made progress creating batch commands via empire DBCommands in a generic way to feed batchable prepared statements. Unfortunately there is a drawback I want to discuss with you guys.
Most annoying (to me) is that creation of PreparedStatement sql's depends on a boolean flag set on the database object itself. This either means toggling the flag depending on usage or creating two database instances having same set of tables. I suggest to allow passing a bool param to a second dbcommand ctor to control creation of "normal value based statements" or "questionmark based prepared statements" independently from DBDatabase which won't break any existing code from my understanding.
Thus I suggest to add a field in DBCommand class like
private final boolean _usePreparedStatement;
The existing ctor will look like
public DBCommand(final DBDatabase db) {
_usePreparedStatement = db.isPreparedStatement;
...
}
Adding a second CTOR like
public DBCommand(final DBDatabase db, final boolean usePreparedStatement) {
_usePreparedStatement = usePreparedStatement;
...
}
And a last small change is required in method "useCmdParam" to make use of the newly introduced boolean member instead of "db".
What you think?
Jens
Von meinem iPad gesendet
Am 31.08.2012 um 18:00 schrieb "mailinglist@j-b-s.de" <ma...@j-b-s.de>:
> Hi chris!
>
> I solved it this way
>
>
>
> final DBSQLScript script =3D new DBSQLScript();
>
> loop>>
> final DBCommand dbCommand =3D _db.createCommand();
> dbCommand.set(new DBSetExpr(column1, value1));
> ...
> dbCommand.set(new DBSetExpr(columnN, valueN));
> script.addStmt(dbCommand.getInsert());
> <<loop
>
> script.run(_db.getDriver(), cDestinationWrite, false);
> script.clear();
>
> What you think?
>
> Jens
>
>
>
> Von meinem iPad gesendet
>
> Am 30.08.2012 um 18:38 schrieb Christopher Richmond <cr...@referentia.com>:
>
>> I was thinking of doing something very similar to this, since I think one of the most useful things about the library is the SQL generation, sparing other developers from having to deal with string butchering which is error prone, then allow them to pass the objects in to which I will simply extract the query string to execute via pure JDBC.
>>
>> I also am taking a look at the source code and if i feel I can modify a patch to allow batch inserts, I will submit it back.
>>
>> Thanks!
>> Chris
>>
>> On Wed, Aug 29, 2012 at 11:43 PM, Rainer Döbele <do...@esteam.de> wrote:
>> Hi Christopher,
>>
>>
>>
>> currently there is no support for Batch-Inserts in Empire-DB.
>>
>>
>>
>> However you can combine direct JDBC and Empire-DB and e.g. let Empire-DB create the SQL Command string for the Prepared statement.
>>
>> In order to use Prepared Statement params use DBCommand.addParam(DataType type, Object value).
>>
>> See SampleAdvApp.commandParamsSample(…) method for an example.
>>
>>
>>
>> If you find a good generic solution for this that is reusable for other projects, we would be happy to receive your code in order to provide this feature with an future release of Emprie-db.
>>
>>
>>
>> Regards
>>
>> Rainer
>>
>>
>>
>>
>>
>> Von: Christopher Richmond [mailto:crichmond@referentia.com]
>> Gesendet: Donnerstag, 30. August 2012 04:51
>> An: user
>> Betreff: Batch inserts
>>
>>
>>
>> With pure JDBC I can do a PreparedStamement(ps) with batches for inserting large numbers of rows(millions) with my embedded H2 database. This works fine(along with setting autocommit OFF on my connection)
>>
>> int count = 0;
>>
>>
>>
>> for(int x = 1; x <= totalRows; x++){
>>
>>
>>
>>
>>
>> for(<each item of data I have, up to millions>){
>>
>> pst.setInt(colIndex, rowIndex);
>>
>> }
>>
>>
>>
>> pst.addBatch();
>>
>> if(++count % batchSize == 0) {
>>
>> pst.executeBatch(); //execute batches at specified invervals (batchSize)
>>
>>
>>
>> }
>>
>>
>>
>> }
>>
>>
>>
>>
>>
>> pst.executeBatch(); // insert remaining records
>>
>>
>>
>> pst.close();
>>
>>
>>
>> but I am now trying to use EmpireDB and it is unclear if I can do batch inserts against the database usinge the EmpireDB api. Is this possible and is there sample code for how to configure or execute against the API do this?
>>
>>
>>
>> In summary, I want batch insertion for large sets of data(millions of rows), executing batches of inserts at regular intervals like I was doing with pure JDBC above.
>>
>>
>>
>> Thanks,
>>
>> Chris
>>
>>
Re: Cutting a release now
Posted by Francis De Brabandere <fr...@gmail.com>.
Ok, let's do this. I'll try to free up some time for the release.
Cheers,
Francis
On 13 October 2012 13:23, Rainer Döbele <do...@esteam.de> wrote:
> Hi all (and especially to Francis),
>
> as I think it does not make sense to rush into new features that may require API changes, I think it would be a good idea to cut a release from the current state of the trunk.
>
> Francis would you be able to do that, and is there anything you want me to do?
> I have already updated the Changelog and set the issues to resolved (I can also close the issues).
>
> Thanks and best regards
> Rainer
>
>
>> from: Francis De Brabandere [mailto:francisdb@gmail.com]
>> to: user@empire-db.apache.org
>> re: Re: Batch inserts
>>
>> Hi Jens,
>>
>> I agree ont the need for flexibility, was just thinking out loud why
>> you would not want a prepared statement...
>>
>> For my part this change is ok, but I'd like to hear Rainer's opinion.
>> Can you create a ticket with patch at
>> https://issues.apache.org/jira/browse/EMPIREDB ?
>>
>> We plan to make a release in the near future so it would be nice if
>> your modification was in there...
>>
>> Cheers,
>> F
>>
>> On 10 October 2012 14:37, mailinglist@j-b-s.de <ma...@j-b-s.de>
>> wrote:
>> > Hi F!
>> >
>> > We have different where clauses and frequently changing numbers of
>> parameters thus prepared statements usually will not gain any
>> advantages. I just suggest a more fine grained selection concerning
>> prepared statement usage in contrast to "all or nothing".
>> >
>> > One more thing: from a logical point of view I can argue why is the
>> datebase object responsibe to decide how sql is created (as statement
>> or prepared statement)? To me this is part of the code using a
>> DBCommand's sql. It's perfectly fine to allow setting a default, but
>> why not allowing to override the default when needed without risking
>> side-effects while changing the global flag on DBDatabase?
>> >
>> > But you are perfectly right, it's more about beautifying it's
>> definitely not a bug or severe issue at all.
>> >
>> >
>> > Jens
>> >
>> >
>> > Von meinem iPad gesendet
>> >
>> > Am 10.10.2012 um 14:02 schrieb Francis De Brabandere
>> <fr...@gmail.com>:
>> >
>> >> Why not use prepared statements for everything? Is there a
>> drawback/limitation?
>> >>
>> >> http://stackoverflow.com/questions/3385177/best-practices-with-
>> prepar
>> >> edstatements-when-to-and-when-not-to
>> >>
>> >> Cheers,
>> >> F
>> >>
>> >> On 10 October 2012 13:51, mailinglist@j-b-s.de <mailinglist@j-b-
>> s.de> wrote:
>> >>> Hi Guys!
>> >>>
>> >>> Finally I made progress creating batch commands via empire
>> >>> DBCommands in a generic way to feed batchable prepared statements.
>> >>> Unfortunately there is a drawback I want to discuss with you guys.
>> >>>
>> >>> Most annoying (to me) is that creation of PreparedStatement sql's
>> >>> depends on a boolean flag set on the database object itself. This
>> >>> either means toggling the flag depending on usage or creating two
>> >>> database instances having same set of tables. I suggest to allow
>> >>> passing a bool param to a second dbcommand ctor to control creation
>> >>> of "normal value based statements" or "questionmark based prepared
>> >>> statements" independently from DBDatabase which won't break any
>> existing code from my understanding.
>> >>>
>> >>>
>> >>> Thus I suggest to add a field in DBCommand class like private final
>> >>> boolean _usePreparedStatement;
>> >>>
>> >>> The existing ctor will look like
>> >>> public DBCommand(final DBDatabase db) {
>> >>> _usePreparedStatement = db.isPreparedStatement;
>> >>> ...
>> >>> }
>> >>>
>> >>> Adding a second CTOR like
>> >>> public DBCommand(final DBDatabase db, final boolean
>> >>> usePreparedStatement) { _usePreparedStatement =
>> >>> usePreparedStatement; ...
>> >>> }
>> >>>
>> >>> And a last small change is required in method "useCmdParam" to make
>> >>> use of the newly introduced boolean member instead of "db".
>> >>>
>> >>>
>> >>> What you think?
>> >>>
>> >>> Jens
>> >>>
>> >>>
>> >>> Von meinem iPad gesendet
>> >>>
>> >>> Am 31.08.2012 um 18:00 schrieb "mailinglist@j-b-s.de"
>> >>> <ma...@j-b-s.de>:
>> >>>
>> >>> Hi chris!
>> >>>
>> >>> I solved it this way
>> >>>
>> >>>
>> >>>
>> >>> final DBSQLScript script =3D new DBSQLScript();
>> >>>
>> >>> loop>>
>> >>> final DBCommand dbCommand =3D _db.createCommand();
>> >>> dbCommand.set(new DBSetExpr(column1, value1));
>> >>> ...
>> >>> dbCommand.set(new DBSetExpr(columnN, valueN));
>> >>> script.addStmt(dbCommand.getInsert());
>> >>> <<loop
>> >>>
>> >>> script.run(_db.getDriver(), cDestinationWrite, false);
>> >>> script.clear();
>> >>>
>> >>> What you think?
>> >>>
>> >>> Jens
>> >>>
>> >>>
>> >>>
>> >>> Von meinem iPad gesendet
>> >>>
>> >>> Am 30.08.2012 um 18:38 schrieb Christopher Richmond
>> >>> <cr...@referentia.com>:
>> >>>
>> >>> I was thinking of doing something very similar to this, since I
>> >>> think one of the most useful things about the library is the SQL
>> >>> generation, sparing other developers from having to deal with
>> string
>> >>> butchering which is error prone, then allow them to pass the
>> objects
>> >>> in to which I will simply extract the query string to execute via
>> pure JDBC.
>> >>>
>> >>> I also am taking a look at the source code and if i feel I can
>> >>> modify a patch to allow batch inserts, I will submit it back.
>> >>>
>> >>> Thanks!
>> >>> Chris
>> >>>
>> >>> On Wed, Aug 29, 2012 at 11:43 PM, Rainer Döbele <do...@esteam.de>
>> wrote:
>> >>>>
>> >>>> Hi Christopher,
>> >>>>
>> >>>>
>> >>>>
>> >>>> currently there is no support for Batch-Inserts in Empire-DB.
>> >>>>
>> >>>>
>> >>>>
>> >>>> However you can combine direct JDBC and Empire-DB and e.g. let
>> >>>> Empire-DB create the SQL Command string for the Prepared
>> statement.
>> >>>>
>> >>>> In order to use Prepared Statement params use
>> >>>> DBCommand.addParam(DataType type, Object value).
>> >>>>
>> >>>> See SampleAdvApp.commandParamsSample(...) method for an example.
>> >>>>
>> >>>>
>> >>>>
>> >>>> If you find a good generic solution for this that is reusable for
>> >>>> other projects, we would be happy to receive your code in order to
>> >>>> provide this feature with an future release of Emprie-db.
>> >>>>
>> >>>>
>> >>>>
>> >>>> Regards
>> >>>>
>> >>>> Rainer
>> >>>>
>> >>>>
>> >>>>
>> >>>>
>> >>>>
>> >>>> Von: Christopher Richmond [mailto:crichmond@referentia.com]
>> >>>> Gesendet: Donnerstag, 30. August 2012 04:51
>> >>>> An: user
>> >>>> Betreff: Batch inserts
>> >>>>
>> >>>>
>> >>>>
>> >>>> With pure JDBC I can do a PreparedStamement(ps) with batches for
>> >>>> inserting large numbers of rows(millions) with my embedded H2
>> >>>> database. This works fine(along with setting autocommit OFF on my
>> >>>> connection)
>> >>>>
>> >>>> int count = 0;
>> >>>>
>> >>>>
>> >>>>
>> >>>> for(int x = 1; x <= totalRows; x++){
>> >>>>
>> >>>>
>> >>>>
>> >>>>
>> >>>>
>> >>>> for(<each item of data I have, up to millions>){
>> >>>>
>> >>>> pst.setInt(colIndex, rowIndex);
>> >>>>
>> >>>> }
>> >>>>
>> >>>>
>> >>>>
>> >>>> pst.addBatch();
>> >>>>
>> >>>> if(++count % batchSize == 0) {
>> >>>>
>> >>>> pst.executeBatch(); //execute batches at specified
>> invervals
>> >>>> (batchSize)
>> >>>>
>> >>>>
>> >>>>
>> >>>> }
>> >>>>
>> >>>>
>> >>>>
>> >>>> }
>> >>>>
>> >>>>
>> >>>>
>> >>>>
>> >>>>
>> >>>> pst.executeBatch(); // insert remaining records
>> >>>>
>> >>>>
>> >>>>
>> >>>> pst.close();
>> >>>>
>> >>>>
>> >>>>
>> >>>> but I am now trying to use EmpireDB and it is unclear if I can do
>> >>>> batch inserts against the database usinge the EmpireDB api. Is
>> >>>> this possible and is there sample code for how to configure or
>> >>>> execute against the API do this?
>> >>>>
>> >>>>
>> >>>>
>> >>>> In summary, I want batch insertion for large sets of data(millions
>> >>>> of rows), executing batches of inserts at regular intervals like I
>> >>>> was doing with pure JDBC above.
>> >>>>
>> >>>>
>> >>>>
>> >>>> Thanks,
>> >>>>
>> >>>> Chris
>> >>>
>> >>>
Cutting a release now
Posted by Rainer Döbele <do...@esteam.de>.
Hi all (and especially to Francis),
as I think it does not make sense to rush into new features that may require API changes, I think it would be a good idea to cut a release from the current state of the trunk.
Francis would you be able to do that, and is there anything you want me to do?
I have already updated the Changelog and set the issues to resolved (I can also close the issues).
Thanks and best regards
Rainer
> from: Francis De Brabandere [mailto:francisdb@gmail.com]
> to: user@empire-db.apache.org
> re: Re: Batch inserts
>
> Hi Jens,
>
> I agree ont the need for flexibility, was just thinking out loud why
> you would not want a prepared statement...
>
> For my part this change is ok, but I'd like to hear Rainer's opinion.
> Can you create a ticket with patch at
> https://issues.apache.org/jira/browse/EMPIREDB ?
>
> We plan to make a release in the near future so it would be nice if
> your modification was in there...
>
> Cheers,
> F
>
> On 10 October 2012 14:37, mailinglist@j-b-s.de <ma...@j-b-s.de>
> wrote:
> > Hi F!
> >
> > We have different where clauses and frequently changing numbers of
> parameters thus prepared statements usually will not gain any
> advantages. I just suggest a more fine grained selection concerning
> prepared statement usage in contrast to "all or nothing".
> >
> > One more thing: from a logical point of view I can argue why is the
> datebase object responsibe to decide how sql is created (as statement
> or prepared statement)? To me this is part of the code using a
> DBCommand's sql. It's perfectly fine to allow setting a default, but
> why not allowing to override the default when needed without risking
> side-effects while changing the global flag on DBDatabase?
> >
> > But you are perfectly right, it's more about beautifying it's
> definitely not a bug or severe issue at all.
> >
> >
> > Jens
> >
> >
> > Von meinem iPad gesendet
> >
> > Am 10.10.2012 um 14:02 schrieb Francis De Brabandere
> <fr...@gmail.com>:
> >
> >> Why not use prepared statements for everything? Is there a
> drawback/limitation?
> >>
> >> http://stackoverflow.com/questions/3385177/best-practices-with-
> prepar
> >> edstatements-when-to-and-when-not-to
> >>
> >> Cheers,
> >> F
> >>
> >> On 10 October 2012 13:51, mailinglist@j-b-s.de <mailinglist@j-b-
> s.de> wrote:
> >>> Hi Guys!
> >>>
> >>> Finally I made progress creating batch commands via empire
> >>> DBCommands in a generic way to feed batchable prepared statements.
> >>> Unfortunately there is a drawback I want to discuss with you guys.
> >>>
> >>> Most annoying (to me) is that creation of PreparedStatement sql's
> >>> depends on a boolean flag set on the database object itself. This
> >>> either means toggling the flag depending on usage or creating two
> >>> database instances having same set of tables. I suggest to allow
> >>> passing a bool param to a second dbcommand ctor to control creation
> >>> of "normal value based statements" or "questionmark based prepared
> >>> statements" independently from DBDatabase which won't break any
> existing code from my understanding.
> >>>
> >>>
> >>> Thus I suggest to add a field in DBCommand class like private final
> >>> boolean _usePreparedStatement;
> >>>
> >>> The existing ctor will look like
> >>> public DBCommand(final DBDatabase db) {
> >>> _usePreparedStatement = db.isPreparedStatement;
> >>> ...
> >>> }
> >>>
> >>> Adding a second CTOR like
> >>> public DBCommand(final DBDatabase db, final boolean
> >>> usePreparedStatement) { _usePreparedStatement =
> >>> usePreparedStatement; ...
> >>> }
> >>>
> >>> And a last small change is required in method "useCmdParam" to make
> >>> use of the newly introduced boolean member instead of "db".
> >>>
> >>>
> >>> What you think?
> >>>
> >>> Jens
> >>>
> >>>
> >>> Von meinem iPad gesendet
> >>>
> >>> Am 31.08.2012 um 18:00 schrieb "mailinglist@j-b-s.de"
> >>> <ma...@j-b-s.de>:
> >>>
> >>> Hi chris!
> >>>
> >>> I solved it this way
> >>>
> >>>
> >>>
> >>> final DBSQLScript script =3D new DBSQLScript();
> >>>
> >>> loop>>
> >>> final DBCommand dbCommand =3D _db.createCommand();
> >>> dbCommand.set(new DBSetExpr(column1, value1));
> >>> ...
> >>> dbCommand.set(new DBSetExpr(columnN, valueN));
> >>> script.addStmt(dbCommand.getInsert());
> >>> <<loop
> >>>
> >>> script.run(_db.getDriver(), cDestinationWrite, false);
> >>> script.clear();
> >>>
> >>> What you think?
> >>>
> >>> Jens
> >>>
> >>>
> >>>
> >>> Von meinem iPad gesendet
> >>>
> >>> Am 30.08.2012 um 18:38 schrieb Christopher Richmond
> >>> <cr...@referentia.com>:
> >>>
> >>> I was thinking of doing something very similar to this, since I
> >>> think one of the most useful things about the library is the SQL
> >>> generation, sparing other developers from having to deal with
> string
> >>> butchering which is error prone, then allow them to pass the
> objects
> >>> in to which I will simply extract the query string to execute via
> pure JDBC.
> >>>
> >>> I also am taking a look at the source code and if i feel I can
> >>> modify a patch to allow batch inserts, I will submit it back.
> >>>
> >>> Thanks!
> >>> Chris
> >>>
> >>> On Wed, Aug 29, 2012 at 11:43 PM, Rainer Döbele <do...@esteam.de>
> wrote:
> >>>>
> >>>> Hi Christopher,
> >>>>
> >>>>
> >>>>
> >>>> currently there is no support for Batch-Inserts in Empire-DB.
> >>>>
> >>>>
> >>>>
> >>>> However you can combine direct JDBC and Empire-DB and e.g. let
> >>>> Empire-DB create the SQL Command string for the Prepared
> statement.
> >>>>
> >>>> In order to use Prepared Statement params use
> >>>> DBCommand.addParam(DataType type, Object value).
> >>>>
> >>>> See SampleAdvApp.commandParamsSample(...) method for an example.
> >>>>
> >>>>
> >>>>
> >>>> If you find a good generic solution for this that is reusable for
> >>>> other projects, we would be happy to receive your code in order to
> >>>> provide this feature with an future release of Emprie-db.
> >>>>
> >>>>
> >>>>
> >>>> Regards
> >>>>
> >>>> Rainer
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>
> >>>> Von: Christopher Richmond [mailto:crichmond@referentia.com]
> >>>> Gesendet: Donnerstag, 30. August 2012 04:51
> >>>> An: user
> >>>> Betreff: Batch inserts
> >>>>
> >>>>
> >>>>
> >>>> With pure JDBC I can do a PreparedStamement(ps) with batches for
> >>>> inserting large numbers of rows(millions) with my embedded H2
> >>>> database. This works fine(along with setting autocommit OFF on my
> >>>> connection)
> >>>>
> >>>> int count = 0;
> >>>>
> >>>>
> >>>>
> >>>> for(int x = 1; x <= totalRows; x++){
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>
> >>>> for(<each item of data I have, up to millions>){
> >>>>
> >>>> pst.setInt(colIndex, rowIndex);
> >>>>
> >>>> }
> >>>>
> >>>>
> >>>>
> >>>> pst.addBatch();
> >>>>
> >>>> if(++count % batchSize == 0) {
> >>>>
> >>>> pst.executeBatch(); //execute batches at specified
> invervals
> >>>> (batchSize)
> >>>>
> >>>>
> >>>>
> >>>> }
> >>>>
> >>>>
> >>>>
> >>>> }
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>
> >>>> pst.executeBatch(); // insert remaining records
> >>>>
> >>>>
> >>>>
> >>>> pst.close();
> >>>>
> >>>>
> >>>>
> >>>> but I am now trying to use EmpireDB and it is unclear if I can do
> >>>> batch inserts against the database usinge the EmpireDB api. Is
> >>>> this possible and is there sample code for how to configure or
> >>>> execute against the API do this?
> >>>>
> >>>>
> >>>>
> >>>> In summary, I want batch insertion for large sets of data(millions
> >>>> of rows), executing batches of inserts at regular intervals like I
> >>>> was doing with pure JDBC above.
> >>>>
> >>>>
> >>>>
> >>>> Thanks,
> >>>>
> >>>> Chris
> >>>
> >>>
Re: Batch inserts
Posted by Francis De Brabandere <fr...@gmail.com>.
Hi Jens,
I agree ont the need for flexibility, was just thinking out loud why
you would not want a prepared statement...
For my part this change is ok, but I'd like to hear Rainer's opinion.
Can you create a ticket with patch at
https://issues.apache.org/jira/browse/EMPIREDB ?
We plan to make a release in the near future so it would be nice if
your modification was in there...
Cheers,
F
On 10 October 2012 14:37, mailinglist@j-b-s.de <ma...@j-b-s.de> wrote:
> Hi F!
>
> We have different where clauses and frequently changing numbers of parameters thus prepared statements usually will not gain any advantages. I just suggest a more fine grained selection concerning prepared statement usage in contrast to "all or nothing".
>
> One more thing: from a logical point of view I can argue why is the datebase object responsibe to decide how sql is created (as statement or prepared statement)? To me this is part of the code using a DBCommand's sql. It's perfectly fine to allow setting a default, but why not allowing to override the default when needed without risking side-effects while changing the global flag on DBDatabase?
>
> But you are perfectly right, it's more about beautifying it's definitely not a bug or severe issue at all.
>
>
> Jens
>
>
> Von meinem iPad gesendet
>
> Am 10.10.2012 um 14:02 schrieb Francis De Brabandere <fr...@gmail.com>:
>
>> Why not use prepared statements for everything? Is there a drawback/limitation?
>>
>> http://stackoverflow.com/questions/3385177/best-practices-with-preparedstatements-when-to-and-when-not-to
>>
>> Cheers,
>> F
>>
>> On 10 October 2012 13:51, mailinglist@j-b-s.de <ma...@j-b-s.de> wrote:
>>> Hi Guys!
>>>
>>> Finally I made progress creating batch commands via empire DBCommands in a
>>> generic way to feed batchable prepared statements. Unfortunately there is a
>>> drawback I want to discuss with you guys.
>>>
>>> Most annoying (to me) is that creation of PreparedStatement sql's depends on
>>> a boolean flag set on the database object itself. This either means toggling
>>> the flag depending on usage or creating two database instances having same
>>> set of tables. I suggest to allow passing a bool param to a second dbcommand
>>> ctor to control creation of "normal value based statements" or "questionmark
>>> based prepared statements" independently from DBDatabase which won't break
>>> any existing code from my understanding.
>>>
>>>
>>> Thus I suggest to add a field in DBCommand class like
>>> private final boolean _usePreparedStatement;
>>>
>>> The existing ctor will look like
>>> public DBCommand(final DBDatabase db) {
>>> _usePreparedStatement = db.isPreparedStatement;
>>> ...
>>> }
>>>
>>> Adding a second CTOR like
>>> public DBCommand(final DBDatabase db, final boolean usePreparedStatement) {
>>> _usePreparedStatement = usePreparedStatement;
>>> ...
>>> }
>>>
>>> And a last small change is required in method "useCmdParam" to make use of
>>> the newly introduced boolean member instead of "db".
>>>
>>>
>>> What you think?
>>>
>>> Jens
>>>
>>>
>>> Von meinem iPad gesendet
>>>
>>> Am 31.08.2012 um 18:00 schrieb "mailinglist@j-b-s.de"
>>> <ma...@j-b-s.de>:
>>>
>>> Hi chris!
>>>
>>> I solved it this way
>>>
>>>
>>>
>>> final DBSQLScript script =3D new DBSQLScript();
>>>
>>> loop>>
>>> final DBCommand dbCommand =3D _db.createCommand();
>>> dbCommand.set(new DBSetExpr(column1, value1));
>>> ...
>>> dbCommand.set(new DBSetExpr(columnN, valueN));
>>> script.addStmt(dbCommand.getInsert());
>>> <<loop
>>>
>>> script.run(_db.getDriver(), cDestinationWrite, false);
>>> script.clear();
>>>
>>> What you think?
>>>
>>> Jens
>>>
>>>
>>>
>>> Von meinem iPad gesendet
>>>
>>> Am 30.08.2012 um 18:38 schrieb Christopher Richmond
>>> <cr...@referentia.com>:
>>>
>>> I was thinking of doing something very similar to this, since I think one of
>>> the most useful things about the library is the SQL generation, sparing
>>> other developers from having to deal with string butchering which is error
>>> prone, then allow them to pass the objects in to which I will simply extract
>>> the query string to execute via pure JDBC.
>>>
>>> I also am taking a look at the source code and if i feel I can modify a
>>> patch to allow batch inserts, I will submit it back.
>>>
>>> Thanks!
>>> Chris
>>>
>>> On Wed, Aug 29, 2012 at 11:43 PM, Rainer Döbele <do...@esteam.de> wrote:
>>>>
>>>> Hi Christopher,
>>>>
>>>>
>>>>
>>>> currently there is no support for Batch-Inserts in Empire-DB.
>>>>
>>>>
>>>>
>>>> However you can combine direct JDBC and Empire-DB and e.g. let Empire-DB
>>>> create the SQL Command string for the Prepared statement.
>>>>
>>>> In order to use Prepared Statement params use DBCommand.addParam(DataType
>>>> type, Object value).
>>>>
>>>> See SampleAdvApp.commandParamsSample(…) method for an example.
>>>>
>>>>
>>>>
>>>> If you find a good generic solution for this that is reusable for other
>>>> projects, we would be happy to receive your code in order to provide this
>>>> feature with an future release of Emprie-db.
>>>>
>>>>
>>>>
>>>> Regards
>>>>
>>>> Rainer
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> Von: Christopher Richmond [mailto:crichmond@referentia.com]
>>>> Gesendet: Donnerstag, 30. August 2012 04:51
>>>> An: user
>>>> Betreff: Batch inserts
>>>>
>>>>
>>>>
>>>> With pure JDBC I can do a PreparedStamement(ps) with batches for inserting
>>>> large numbers of rows(millions) with my embedded H2 database. This works
>>>> fine(along with setting autocommit OFF on my connection)
>>>>
>>>> int count = 0;
>>>>
>>>>
>>>>
>>>> for(int x = 1; x <= totalRows; x++){
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> for(<each item of data I have, up to millions>){
>>>>
>>>> pst.setInt(colIndex, rowIndex);
>>>>
>>>> }
>>>>
>>>>
>>>>
>>>> pst.addBatch();
>>>>
>>>> if(++count % batchSize == 0) {
>>>>
>>>> pst.executeBatch(); //execute batches at specified invervals
>>>> (batchSize)
>>>>
>>>>
>>>>
>>>> }
>>>>
>>>>
>>>>
>>>> }
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> pst.executeBatch(); // insert remaining records
>>>>
>>>>
>>>>
>>>> pst.close();
>>>>
>>>>
>>>>
>>>> but I am now trying to use EmpireDB and it is unclear if I can do batch
>>>> inserts against the database usinge the EmpireDB api. Is this possible and
>>>> is there sample code for how to configure or execute against the API do
>>>> this?
>>>>
>>>>
>>>>
>>>> In summary, I want batch insertion for large sets of data(millions of
>>>> rows), executing batches of inserts at regular intervals like I was doing
>>>> with pure JDBC above.
>>>>
>>>>
>>>>
>>>> Thanks,
>>>>
>>>> Chris
>>>
>>>
Re: Batch inserts
Posted by "mailinglist@j-b-s.de" <ma...@j-b-s.de>.
Hi F!
We have different where clauses and frequently changing numbers of parameters thus prepared statements usually will not gain any advantages. I just suggest a more fine grained selection concerning prepared statement usage in contrast to "all or nothing".
One more thing: from a logical point of view I can argue why is the datebase object responsibe to decide how sql is created (as statement or prepared statement)? To me this is part of the code using a DBCommand's sql. It's perfectly fine to allow setting a default, but why not allowing to override the default when needed without risking side-effects while changing the global flag on DBDatabase?
But you are perfectly right, it's more about beautifying it's definitely not a bug or severe issue at all.
Jens
Von meinem iPad gesendet
Am 10.10.2012 um 14:02 schrieb Francis De Brabandere <fr...@gmail.com>:
> Why not use prepared statements for everything? Is there a drawback/limitation?
>
> http://stackoverflow.com/questions/3385177/best-practices-with-preparedstatements-when-to-and-when-not-to
>
> Cheers,
> F
>
> On 10 October 2012 13:51, mailinglist@j-b-s.de <ma...@j-b-s.de> wrote:
>> Hi Guys!
>>
>> Finally I made progress creating batch commands via empire DBCommands in a
>> generic way to feed batchable prepared statements. Unfortunately there is a
>> drawback I want to discuss with you guys.
>>
>> Most annoying (to me) is that creation of PreparedStatement sql's depends on
>> a boolean flag set on the database object itself. This either means toggling
>> the flag depending on usage or creating two database instances having same
>> set of tables. I suggest to allow passing a bool param to a second dbcommand
>> ctor to control creation of "normal value based statements" or "questionmark
>> based prepared statements" independently from DBDatabase which won't break
>> any existing code from my understanding.
>>
>>
>> Thus I suggest to add a field in DBCommand class like
>> private final boolean _usePreparedStatement;
>>
>> The existing ctor will look like
>> public DBCommand(final DBDatabase db) {
>> _usePreparedStatement = db.isPreparedStatement;
>> ...
>> }
>>
>> Adding a second CTOR like
>> public DBCommand(final DBDatabase db, final boolean usePreparedStatement) {
>> _usePreparedStatement = usePreparedStatement;
>> ...
>> }
>>
>> And a last small change is required in method "useCmdParam" to make use of
>> the newly introduced boolean member instead of "db".
>>
>>
>> What you think?
>>
>> Jens
>>
>>
>> Von meinem iPad gesendet
>>
>> Am 31.08.2012 um 18:00 schrieb "mailinglist@j-b-s.de"
>> <ma...@j-b-s.de>:
>>
>> Hi chris!
>>
>> I solved it this way
>>
>>
>>
>> final DBSQLScript script =3D new DBSQLScript();
>>
>> loop>>
>> final DBCommand dbCommand =3D _db.createCommand();
>> dbCommand.set(new DBSetExpr(column1, value1));
>> ...
>> dbCommand.set(new DBSetExpr(columnN, valueN));
>> script.addStmt(dbCommand.getInsert());
>> <<loop
>>
>> script.run(_db.getDriver(), cDestinationWrite, false);
>> script.clear();
>>
>> What you think?
>>
>> Jens
>>
>>
>>
>> Von meinem iPad gesendet
>>
>> Am 30.08.2012 um 18:38 schrieb Christopher Richmond
>> <cr...@referentia.com>:
>>
>> I was thinking of doing something very similar to this, since I think one of
>> the most useful things about the library is the SQL generation, sparing
>> other developers from having to deal with string butchering which is error
>> prone, then allow them to pass the objects in to which I will simply extract
>> the query string to execute via pure JDBC.
>>
>> I also am taking a look at the source code and if i feel I can modify a
>> patch to allow batch inserts, I will submit it back.
>>
>> Thanks!
>> Chris
>>
>> On Wed, Aug 29, 2012 at 11:43 PM, Rainer Döbele <do...@esteam.de> wrote:
>>>
>>> Hi Christopher,
>>>
>>>
>>>
>>> currently there is no support for Batch-Inserts in Empire-DB.
>>>
>>>
>>>
>>> However you can combine direct JDBC and Empire-DB and e.g. let Empire-DB
>>> create the SQL Command string for the Prepared statement.
>>>
>>> In order to use Prepared Statement params use DBCommand.addParam(DataType
>>> type, Object value).
>>>
>>> See SampleAdvApp.commandParamsSample(…) method for an example.
>>>
>>>
>>>
>>> If you find a good generic solution for this that is reusable for other
>>> projects, we would be happy to receive your code in order to provide this
>>> feature with an future release of Emprie-db.
>>>
>>>
>>>
>>> Regards
>>>
>>> Rainer
>>>
>>>
>>>
>>>
>>>
>>> Von: Christopher Richmond [mailto:crichmond@referentia.com]
>>> Gesendet: Donnerstag, 30. August 2012 04:51
>>> An: user
>>> Betreff: Batch inserts
>>>
>>>
>>>
>>> With pure JDBC I can do a PreparedStamement(ps) with batches for inserting
>>> large numbers of rows(millions) with my embedded H2 database. This works
>>> fine(along with setting autocommit OFF on my connection)
>>>
>>> int count = 0;
>>>
>>>
>>>
>>> for(int x = 1; x <= totalRows; x++){
>>>
>>>
>>>
>>>
>>>
>>> for(<each item of data I have, up to millions>){
>>>
>>> pst.setInt(colIndex, rowIndex);
>>>
>>> }
>>>
>>>
>>>
>>> pst.addBatch();
>>>
>>> if(++count % batchSize == 0) {
>>>
>>> pst.executeBatch(); //execute batches at specified invervals
>>> (batchSize)
>>>
>>>
>>>
>>> }
>>>
>>>
>>>
>>> }
>>>
>>>
>>>
>>>
>>>
>>> pst.executeBatch(); // insert remaining records
>>>
>>>
>>>
>>> pst.close();
>>>
>>>
>>>
>>> but I am now trying to use EmpireDB and it is unclear if I can do batch
>>> inserts against the database usinge the EmpireDB api. Is this possible and
>>> is there sample code for how to configure or execute against the API do
>>> this?
>>>
>>>
>>>
>>> In summary, I want batch insertion for large sets of data(millions of
>>> rows), executing batches of inserts at regular intervals like I was doing
>>> with pure JDBC above.
>>>
>>>
>>>
>>> Thanks,
>>>
>>> Chris
>>
>>
Re: Batch inserts
Posted by Francis De Brabandere <fr...@gmail.com>.
Why not use prepared statements for everything? Is there a drawback/limitation?
http://stackoverflow.com/questions/3385177/best-practices-with-preparedstatements-when-to-and-when-not-to
Cheers,
F
On 10 October 2012 13:51, mailinglist@j-b-s.de <ma...@j-b-s.de> wrote:
> Hi Guys!
>
> Finally I made progress creating batch commands via empire DBCommands in a
> generic way to feed batchable prepared statements. Unfortunately there is a
> drawback I want to discuss with you guys.
>
> Most annoying (to me) is that creation of PreparedStatement sql's depends on
> a boolean flag set on the database object itself. This either means toggling
> the flag depending on usage or creating two database instances having same
> set of tables. I suggest to allow passing a bool param to a second dbcommand
> ctor to control creation of "normal value based statements" or "questionmark
> based prepared statements" independently from DBDatabase which won't break
> any existing code from my understanding.
>
>
> Thus I suggest to add a field in DBCommand class like
> private final boolean _usePreparedStatement;
>
> The existing ctor will look like
> public DBCommand(final DBDatabase db) {
> _usePreparedStatement = db.isPreparedStatement;
> ...
> }
>
> Adding a second CTOR like
> public DBCommand(final DBDatabase db, final boolean usePreparedStatement) {
> _usePreparedStatement = usePreparedStatement;
> ...
> }
>
> And a last small change is required in method "useCmdParam" to make use of
> the newly introduced boolean member instead of "db".
>
>
> What you think?
>
> Jens
>
>
> Von meinem iPad gesendet
>
> Am 31.08.2012 um 18:00 schrieb "mailinglist@j-b-s.de"
> <ma...@j-b-s.de>:
>
> Hi chris!
>
> I solved it this way
>
>
>
> final DBSQLScript script =3D new DBSQLScript();
>
> loop>>
> final DBCommand dbCommand =3D _db.createCommand();
> dbCommand.set(new DBSetExpr(column1, value1));
> ...
> dbCommand.set(new DBSetExpr(columnN, valueN));
> script.addStmt(dbCommand.getInsert());
> <<loop
>
> script.run(_db.getDriver(), cDestinationWrite, false);
> script.clear();
>
> What you think?
>
> Jens
>
>
>
> Von meinem iPad gesendet
>
> Am 30.08.2012 um 18:38 schrieb Christopher Richmond
> <cr...@referentia.com>:
>
> I was thinking of doing something very similar to this, since I think one of
> the most useful things about the library is the SQL generation, sparing
> other developers from having to deal with string butchering which is error
> prone, then allow them to pass the objects in to which I will simply extract
> the query string to execute via pure JDBC.
>
> I also am taking a look at the source code and if i feel I can modify a
> patch to allow batch inserts, I will submit it back.
>
> Thanks!
> Chris
>
> On Wed, Aug 29, 2012 at 11:43 PM, Rainer Döbele <do...@esteam.de> wrote:
>>
>> Hi Christopher,
>>
>>
>>
>> currently there is no support for Batch-Inserts in Empire-DB.
>>
>>
>>
>> However you can combine direct JDBC and Empire-DB and e.g. let Empire-DB
>> create the SQL Command string for the Prepared statement.
>>
>> In order to use Prepared Statement params use DBCommand.addParam(DataType
>> type, Object value).
>>
>> See SampleAdvApp.commandParamsSample(…) method for an example.
>>
>>
>>
>> If you find a good generic solution for this that is reusable for other
>> projects, we would be happy to receive your code in order to provide this
>> feature with an future release of Emprie-db.
>>
>>
>>
>> Regards
>>
>> Rainer
>>
>>
>>
>>
>>
>> Von: Christopher Richmond [mailto:crichmond@referentia.com]
>> Gesendet: Donnerstag, 30. August 2012 04:51
>> An: user
>> Betreff: Batch inserts
>>
>>
>>
>> With pure JDBC I can do a PreparedStamement(ps) with batches for inserting
>> large numbers of rows(millions) with my embedded H2 database. This works
>> fine(along with setting autocommit OFF on my connection)
>>
>> int count = 0;
>>
>>
>>
>> for(int x = 1; x <= totalRows; x++){
>>
>>
>>
>>
>>
>> for(<each item of data I have, up to millions>){
>>
>> pst.setInt(colIndex, rowIndex);
>>
>> }
>>
>>
>>
>> pst.addBatch();
>>
>> if(++count % batchSize == 0) {
>>
>> pst.executeBatch(); //execute batches at specified invervals
>> (batchSize)
>>
>>
>>
>> }
>>
>>
>>
>> }
>>
>>
>>
>>
>>
>> pst.executeBatch(); // insert remaining records
>>
>>
>>
>> pst.close();
>>
>>
>>
>> but I am now trying to use EmpireDB and it is unclear if I can do batch
>> inserts against the database usinge the EmpireDB api. Is this possible and
>> is there sample code for how to configure or execute against the API do
>> this?
>>
>>
>>
>> In summary, I want batch insertion for large sets of data(millions of
>> rows), executing batches of inserts at regular intervals like I was doing
>> with pure JDBC above.
>>
>>
>>
>> Thanks,
>>
>> Chris
>
>