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-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 >>>>>> >>>>>>
