Oops, I was a bit too quick with my answer: Adding an additional constructor to DBCommand won't help, as it is an abstract class and gets instantiated by the driver.
But generally I like the idea of being able to choose on a "per command" basis. I will have a closer look and think of a solution. Regards Rainer > from: Rainer Döbele > to: [email protected] > re: re: Batch inserts > > Hi all, > > Yes, I do think we need both prepared and unprepared statements. > In fact I am "pro choice" ;-) > > And I have no objection in adding a second ctor to DBCommand as suggested > by Jens. > In fact I think this is a very good idea. > The only thing I would change is removing the underscore in the name of > private member field as this is not our code style. > > I will create an issue and check this is and add the additional ctor, so it > will go > in our current release, that we want to publish soon. > Objections from anyone? > > 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-prep > > >> ar edstatements-when-to-and-when-not-to > > >> > > >> Cheers, > > >> F > > >> > > >> On 10 October 2012 13:51, [email protected] > > >> <[email protected]> > > 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 > > >>> > > >>>
