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