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