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:[email protected]] > to: [email protected] > 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, [email protected] <[email protected]> > 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 > <[email protected]>: > > > >> 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, [email protected] <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 "[email protected]" > >>> <[email protected]>: > >>> > >>> 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 > >>> <[email protected]>: > >>> > >>> 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 <[email protected]> > 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:[email protected]] > >>>> 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 > >>> > >>>
