QueryDataSet / bind variable

[jurgen valkom]

Lets take following code snippet:

QueryDataSet q = new QueryDataSet(c);

String query = "SELECT * FROM " + table + " WHERE NAME = ' " + name+  " ' ";

q.addTable(table, query);

if (q.getTable(table).getRowCount() > 0)

            DatabaseOperation.DELETE.execute(c, q);

In the above query I'm using a non-bind variable for 'name'.

Is there a way to use bind variables in this query?

The query could look like this:

String query = "SELECT * FROM " + table + " WHERE NAME = :? ";

The result should be passed to QueryDataSet.

Thanks

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
dbunit-user mailing list
dbunit-user@...
https://lists.sourceforge.net/lists/listinfo/dbunit-user

[Matthias Gommeringer]

Hi there,

one possible way to achieve what you want is:

PreparedStatement ps = ...
for(...<loop over your names>...){
  ps.setString(...) // set current bind variable
  ResultSet rs = ps.executeQuery();
  ITableMetaData metaData = new ResultSetTableMetaData(tableName, rs, dbUnitConnection, caseSensitiveTableNames);
  ForwardOnlyResultSetTable iTable = new ForwardOnlyResultSetTable(metaData,rs);
  IDataSet ds = new DefaultDataSet(iTable);
  DatabaseOperation.DELETE.execute(c, ds);
}  

AFAIK there is no simple way to do what you want.

rgds,
matthias

> -----Ursprüngliche Nachricht-----
> Von: "jurgen valkom" <valkomjurgen@...>
> Gesendet: 03.08.09 21:55:41
> An: dbunit-user@...
> Betreff: [dbunit-user] QueryDataSet / bind variable

Lets take following code snippet:

>
> QueryDataSet q = new QueryDataSet(c);
> String query = "SELECT * FROM " + table + " WHERE NAME = ' " + name+ "
> ' ";
> q.addTable(table, query);
> if (q.getTable(table).getRowCount() > 0)
>
>  DatabaseOperation.DELETE.execute(c, q);
>
> In the above query I'm using a non-bind variable for 'name'.
>
> Is there a way to use bind variables in this query?
> The query could look like this:
> String query = "SELECT * FROM " + table + " WHERE NAME = :? ";
>
> The result should be passed to QueryDataSet.
>
> Thanks
>
> ----------------------------------------------------------------------
> -------- Let Crystal Reports handle the reporting - Free Crystal
> Reports 2008 30-Day trial. Simplify your report design, integration
> and deployment - and focus on what you do best, core application
> coding. Discover what's new with Crystal Reports now. http://p.sf.net/
> sfu/bobj-july_______________________________________________ dbunit-
> user mailing list dbunit-user@... https://lists.
>
> sourceforge.net/lists/listinfo/dbunit-user

______________________________________________________
GRATIS für alle WEB.DE-Nutzer: Die maxdome Movie-FLAT!
Jetzt freischalten unter http://movieflat.web.de


------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
dbunit-user mailing list
dbunit-user@...
https://lists.sourceforge.net/lists/listinfo/dbunit-user

[jurgen valkom]

Hi 

Thanks for the quick reply!

The DELETE operation works if the query returns 1 record.

If multiple records are found the DELETE  operation throws

For multiple records I get following exception:

java.lang.UnsupportedOperationException

at org.dbunit.database.ForwardOnlyResultSetTable.getRowCount(ForwardOnlyResultSetTable.java:73)

at org.dbunit.operation.AbstractBatchOperation.execute(AbstractBatchOperation.java:148)

I'm using dbunit 2.4.5

Any suggestions ?

On Mon, Aug 3, 2009 at 10:31 PM, Matthias Gommeringer <Matthias.Gommeringer@...> wrote:

Hi there,

one possible way to achieve what you want is:

PreparedStatement ps = ...
for(...<loop over your names>...){
 ps.setString(...) // set current bind variable
 ResultSet rs = ps.executeQuery();
 ITableMetaData metaData = new ResultSetTableMetaData(tableName, rs, dbUnitConnection, caseSensitiveTableNames);
 ForwardOnlyResultSetTable iTable = new ForwardOnlyResultSetTable(metaData,rs);
 IDataSet ds = new DefaultDataSet(iTable);
 DatabaseOperation.DELETE.execute(c, ds);
}

AFAIK there is no simple way to do what you want.

rgds,
matthias

> -----Ursprüngliche Nachricht-----
> Von: "jurgen valkom" <valkomjurgen@...>
> Gesendet: 03.08.09 21:55:41
> An: dbunit-user@...
> Betreff: [dbunit-user] QueryDataSet / bind variable

Lets take following code snippet:
>
> QueryDataSet q = new QueryDataSet(c);
> String query = "SELECT * FROM " + table + " WHERE NAME = ' " + name+ "
> ' ";
> q.addTable(table, query);
> if (q.getTable(table).getRowCount() > 0)
>
>  DatabaseOperation.DELETE.execute(c, q);
>
> In the above query I'm using a non-bind variable for 'name'.
>
> Is there a way to use bind variables in this query?
> The query could look like this:
> String query = "SELECT * FROM " + table + " WHERE NAME = :? ";
>
> The result should be passed to QueryDataSet.
>
> Thanks
>

> ----------------------------------------------------------------------
> -------- Let Crystal Reports handle the reporting - Free Crystal
> Reports 2008 30-Day trial. Simplify your report design, integration
> and deployment - and focus on what you do best, core application
> coding. Discover what's new with Crystal Reports now. http://p.sf.net/
> sfu/bobj-july_______________________________________________ dbunit-
> user mailing list dbunit-user@... https://lists.
>
> sourceforge.net/lists/listinfo/dbunit-user


______________________________________________________
GRATIS für alle WEB.DE-Nutzer: Die maxdome Movie-FLAT!
Jetzt freischalten unter http://movieflat.web.de


------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
dbunit-user mailing list
dbunit-user@...
https://lists.sourceforge.net/lists/listinfo/dbunit-user

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
dbunit-user mailing list
dbunit-user@...
https://lists.sourceforge.net/lists/listinfo/dbunit-user

[Matthias Gommeringer]

Hi,

just wrap your ForwardOnlyResultSetTable by a CachedResultSetTable:

new CachedResultSetTable(new ForwardOnlyResultSetTable(...))

this should do the trick.

rgds,
matthias


> -----Ursprüngliche Nachricht-----
> Von: "jurgen valkom" <valkomjurgen@...>
> Gesendet: 04.08.09 18:51:02
> An: dbunit-user@...
> Betreff: Re: [dbunit-user] QueryDataSet / bind variable

Hi

>
> Thanks for the quick reply!
> The DELETE operation works if the query returns 1 record.
>
> If multiple records are found the DELETE operation throws
> For multiple records I get following exception:
>
> java.lang.UnsupportedOperationException
> at org.dbunit.database.ForwardOnlyResultSetTable.getRowCount(
> ForwardOnlyResultSetTable.java:73)
> at org.dbunit.operation.AbstractBatchOperation.execute(AbstractBatchOpe
> ration.java:148)
>
> I'm using dbunit 2.4.5
>
> Any suggestions ?
>
> On Mon, Aug 3, 2009 at 10:31 PM, Matthias Gommeringer <Matthias.
> Gommeringer@...> wrote:
> Hi there,
>
> one possible way to achieve what you want is:
>
> PreparedStatement ps = ...
> for(...<loop over your names>...){
>  ps.setString(...) // set current bind variable
>  ResultSet rs = ps.executeQuery();
>  ITableMetaData metaData = new ResultSetTableMetaData(tableName, rs,
> dbUnitConnection, caseSensitiveTableNames);
>  ForwardOnlyResultSetTable iTable = new ForwardOnlyResultSetTable(
> metaData,rs);
>  IDataSet ds = new DefaultDataSet(iTable);
>  DatabaseOperation.DELETE.execute(c, ds);
> }
>
> AFAIK there is no simple way to do what you want.
>
> rgds,
> matthias
>
> > -----Ursprüngliche Nachricht-----
> > Von: "jurgen valkom" <valkomjurgen@...>
> > Gesendet: 03.08.09 21:55:41
> > An: dbunit-user@...
> > Betreff: [dbunit-user] QueryDataSet / bind variable
>
> Lets take following code snippet:
> >
> > QueryDataSet q = new QueryDataSet(c);
> > String query = "SELECT * FROM " + table + " WHERE NAME = ' " + name+
> "
> > ' ";
> > q.addTable(table, query);
> > if (q.getTable(table).getRowCount() > 0)
> >
> > DatabaseOperation.DELETE.execute(c, q);
> >
> > In the above query I'm using a non-bind variable for 'name'.
> >
> > Is there a way to use bind variables in this query?
> > The query could look like this:
> > String query = "SELECT * FROM " + table + " WHERE NAME = :? ";
> >
> > The result should be passed to QueryDataSet.
> >
> > Thanks
> >
> > --------------------------------------------------------------------
> --
> > -------- Let Crystal Reports handle the reporting - Free Crystal
> > Reports 2008 30-Day trial. Simplify your report design, integration
> > and deployment - and focus on what you do best, core application
> > coding. Discover what's new with Crystal Reports now. http://p.sf.
> net/
> > sfu/bobj-july_______________________________________________ dbunit-
> > user mailing list dbunit-user@...://lists.
> >
> > sourceforge.net/lists/listinfo/dbunit-user
>
> ______________________________________________________
> GRATIS für alle WEB.DE-Nutzer: Die maxdome Movie-FLAT!
> Jetzt freischalten unter http://movieflat.web.de
>
> ----------------------------------------------------------------------
> --------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008
> 30-Day
> trial. Simplify your report design, integration and deployment - and
> focus on
> what you do best, core application coding. Discover what's new with
> Crystal Reports now. http://p.sf.net/sfu/bobj-july
> _______________________________________________
> dbunit-user mailing list
> dbunit-user@...
> https://lists.sourceforge.net/lists/listinfo/dbunit-user
>
> ----------------------------------------------------------------------
> -------- Let Crystal Reports handle the reporting - Free Crystal
> Reports 2008 30-Day trial. Simplify your report design, integration
> and deployment - and focus on what you do best, core application
> coding. Discover what's new with Crystal Reports now. http://p.sf.net/
> sfu/bobj-july_______________________________________________ dbunit-
> user mailing list dbunit-user@... https://lists.
>
> sourceforge.net/lists/listinfo/dbunit-user

______________________________________________________
GRATIS für alle WEB.DE-Nutzer: Die maxdome Movie-FLAT!
Jetzt freischalten unter http://movieflat.web.de


------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
dbunit-user mailing list
dbunit-user@...
https://lists.sourceforge.net/lists/listinfo/dbunit-user

[jurgen valkom]

Thanks again for the quick reply!

And it works now.

On Tue, Aug 4, 2009 at 11:20 PM, Matthias Gommeringer <Matthias.Gommeringer@...> wrote:

Hi,

just wrap your ForwardOnlyResultSetTable by a CachedResultSetTable:

new CachedResultSetTable(new ForwardOnlyResultSetTable(...))

this should do the trick.

rgds,
matthias


> -----Ursprüngliche Nachricht-----
> Von: "jurgen valkom" <valkomjurgen@...>

> Gesendet: 04.08.09 18:51:02

> An: dbunit-user@...

> Betreff: Re: [dbunit-user] QueryDataSet / bind variable

Hi
>
> Thanks for the quick reply!
> The DELETE operation works if the query returns 1 record.
>
> If multiple records are found the DELETE operation throws
> For multiple records I get following exception:
>
> java.lang.UnsupportedOperationException
> at org.dbunit.database.ForwardOnlyResultSetTable.getRowCount(
> ForwardOnlyResultSetTable.java:73)
> at org.dbunit.operation.AbstractBatchOperation.execute(AbstractBatchOpe
> ration.java:148)
>
> I'm using dbunit 2.4.5
>
> Any suggestions ?
>
> On Mon, Aug 3, 2009 at 10:31 PM, Matthias Gommeringer <Matthias.
> Gommeringer@...> wrote:
> Hi there,
>
> one possible way to achieve what you want is:
>
> PreparedStatement ps = ...
> for(...<loop over your names>...){
>  ps.setString(...) // set current bind variable
>  ResultSet rs = ps.executeQuery();
>  ITableMetaData metaData = new ResultSetTableMetaData(tableName, rs,
> dbUnitConnection, caseSensitiveTableNames);
>  ForwardOnlyResultSetTable iTable = new ForwardOnlyResultSetTable(
> metaData,rs);
>  IDataSet ds = new DefaultDataSet(iTable);
>  DatabaseOperation.DELETE.execute(c, ds);
> }
>
> AFAIK there is no simple way to do what you want.
>
> rgds,
> matthias
>
> > -----Ursprüngliche Nachricht-----
> > Von: "jurgen valkom" <valkomjurgen@...>
> > Gesendet: 03.08.09 21:55:41
> > An: dbunit-user@...
> > Betreff: [dbunit-user] QueryDataSet / bind variable
>
> Lets take following code snippet:
> >
> > QueryDataSet q = new QueryDataSet(c);
> > String query = "SELECT * FROM " + table + " WHERE NAME = ' " + name+
> "
> > ' ";
> > q.addTable(table, query);
> > if (q.getTable(table).getRowCount() > 0)
> >
> > DatabaseOperation.DELETE.execute(c, q);
> >
> > In the above query I'm using a non-bind variable for 'name'.
> >
> > Is there a way to use bind variables in this query?
> > The query could look like this:
> > String query = "SELECT * FROM " + table + " WHERE NAME = :? ";
> >
> > The result should be passed to QueryDataSet.
> >
> > Thanks
> >
> > --------------------------------------------------------------------
> --
> > -------- Let Crystal Reports handle the reporting - Free Crystal
> > Reports 2008 30-Day trial. Simplify your report design, integration
> > and deployment - and focus on what you do best, core application
> > coding. Discover what's new with Crystal Reports now. http://p.sf.
> net/
> > sfu/bobj-july_______________________________________________ dbunit-

> > user mailing list dbunit-user@...://lists.

> >
> > sourceforge.net/lists/listinfo/dbunit-user
>
> ______________________________________________________
> GRATIS für alle WEB.DE-Nutzer: Die maxdome Movie-FLAT!
> Jetzt freischalten unter http://movieflat.web.de
>
> ----------------------------------------------------------------------
> --------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008
> 30-Day
> trial. Simplify your report design, integration and deployment - and
> focus on
> what you do best, core application coding. Discover what's new with
> Crystal Reports now. http://p.sf.net/sfu/bobj-july
> _______________________________________________
> dbunit-user mailing list
> dbunit-user@...
> https://lists.sourceforge.net/lists/listinfo/dbunit-user
>
> ----------------------------------------------------------------------
> -------- Let Crystal Reports handle the reporting - Free Crystal
> Reports 2008 30-Day trial. Simplify your report design, integration
> and deployment - and focus on what you do best, core application
> coding. Discover what's new with Crystal Reports now. http://p.sf.net/
> sfu/bobj-july_______________________________________________ dbunit-
> user mailing list dbunit-user@... https://lists.
>
> sourceforge.net/lists/listinfo/dbunit-user


______________________________________________________
GRATIS für alle WEB.DE-Nutzer: Die maxdome Movie-FLAT!
Jetzt freischalten unter http://movieflat.web.de


------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
dbunit-user mailing list
dbunit-user@...
https://lists.sourceforge.net/lists/listinfo/dbunit-user

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
dbunit-user mailing list
dbunit-user@...
https://lists.sourceforge.net/lists/listinfo/dbunit-user