Hi Jebb, thanks for your suggestions. We will consider them, but it'll take us some time. This is not a decision we should rush.
Regards Rainer > from: [email protected] [mailto:[email protected]] > to: [email protected] > re: Re: Batch inserts > > Hi Rainer! > > What about > > a) we add a new method on the DBDatabase "createCommandPrepared" and > follow the Ctor approach from our prevmail trail? > > Or > > B) adding methods on DBCommand like > getInsert(boolean prepared) or getInsertPrepared() in parallel to the > "old" getInsert()? > This makes it more obvious and we can leave the ctor as is. > Unfortunately the old getInsert may return a prepared statement, too > due to global database settings. And this might be confusing now, > because having two methods may lead to the wrong assumption one method > returns the value based and the other the prepared variont of a sql. So > a) ? > > > Jens > > Sent from my iPhone > > On 11.10.2012, at 10:41, Rainer Döbele <[email protected]> wrote: > > > 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- > pre > >>>>> p 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 > >>>>>> > >>>>>>
