Mailing-List: contact user-help@empire-db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@empire-db.apache.org
Received-SPF: pass (athena.apache.org: domain of francisdb@gmail.com
 designates 209.85.212.44 as permitted sender)
MIME-Version: 1.0
In-Reply-To: <FCEDEE3E-2ED0-4E0B-8A4E-294E9CDEE19B@j-b-s.de>
References: <B297D4737108A0459B66C3A85BB7F9013E2939@kn.esteam.de>
 <CAMpzucqXPSxCOzVCJNdX8Kv4vbDwAyBJvm4Jxh+=9MX1zFL30A@mail.gmail.com>
 <EAC69789-863D-47FF-835F-E4D2E531694B@j-b-s.de>
 <FCEDEE3E-2ED0-4E0B-8A4E-294E9CDEE19B@j-b-s.de>
From: Francis De Brabandere <francisdb@gmail.com>
Date: Wed, 10 Oct 2012 14:02:09 +0200
Message-ID: 
 <CAGD0tqFE6r8Vf-UxLnj-Cc6OYT_rD9A4Z2E5gEwWQ-1sbdXHdw@mail.gmail.com>
Subject: Re: Batch inserts
To: user@empire-db.apache.org
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable

Why not use prepared statements for everything? Is there a drawback/limitat=
ion?

http://stackoverflow.com/questions/3385177/best-practices-with-preparedstat=
ements-when-to-and-when-not-to

Cheers,
F

On 10 October 2012 13:51, mailinglist@j-b-s.de <mailinglist@j-b-s.de> 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 toggl=
ing
> the flag depending on usage or creating two database instances having sam=
e
> set of tables. I suggest to allow passing a bool param to a second dbcomm=
and
> ctor to control creation of "normal value based statements" or "questionm=
ark
> based prepared statements" independently from DBDatabase which won't brea=
k
> 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 =3D db.isPreparedStatement;
>    ...
> }
>
> Adding a second CTOR like
> public DBCommand(final DBDatabase db, final boolean usePreparedStatement)=
 {
>   _usePreparedStatement =3D usePreparedStatement;
>   ...
> }
>
> And a last small change is required in method "useCmdParam" to make use o=
f
> the newly introduced boolean member instead of "db".
>
>
> What you think?
>
> Jens
>
>
> Von meinem iPad gesendet
>
> Am 31.08.2012 um 18:00 schrieb "mailinglist@j-b-s.de"
> <mailinglist@j-b-s.de>:
>
> Hi chris!
>
> I solved it this way
>
>
>
> final DBSQLScript script =3D3D new DBSQLScript();
>
> loop>>
>        final DBCommand dbCommand =3D3D _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
> <crichmond@referentia.com>:
>
> 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 erro=
r
> prone, then allow them to pass the objects in to which I will simply extr=
act
> 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=F6bele <doebele@esteam.de> wro=
te:
>>
>> 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(DataTyp=
e
>> type, Object value).
>>
>> See SampleAdvApp.commandParamsSample(=85) 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 thi=
s
>> feature with an future release of Emprie-db.
>>
>>
>>
>> Regards
>>
>> Rainer
>>
>>
>>
>>
>>
>> Von: Christopher Richmond [mailto:crichmond@referentia.com]
>> Gesendet: Donnerstag, 30. August 2012 04:51
>> An: user
>> Betreff: Batch inserts
>>
>>
>>
>> With pure JDBC I can do a PreparedStamement(ps) with batches for inserti=
ng
>> large numbers of rows(millions) with my embedded H2 database.  This work=
s
>> fine(along with setting autocommit OFF on my connection)
>>
>>         int count =3D 0;
>>
>>
>>
>>         for(int x =3D 1; x <=3D totalRows; x++){
>>
>>
>>
>>
>>
>>           for(<each item of data I have, up to millions>){
>>
>>             pst.setInt(colIndex, rowIndex);
>>
>>           }
>>
>>
>>
>>           pst.addBatch();
>>
>>           if(++count % batchSize =3D=3D 0) {
>>
>>             pst.executeBatch();   //execute batches at specified inverva=
ls
>> (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 doin=
g
>> with pure JDBC above.
>>
>>
>>
>> Thanks,
>>
>> Chris
>
>