PreparedStatment Batch with more than 1 query

[abkad]

Hi there,

I have the following preparedStatement:

PreparedStatement proPreStProdProperties= connection.prepareStatement(" " +
	 	" DELETE FROM properties" +
		" WHERE id_itm = ?" +
		" " +
		" INSERT INTO properties " +
	        " (id_itm,property_key,property_value) "+
		" VALUES (?,?,?) ");

The issue I am facing that sometimes I want to run the delete and some sometimes don't want so. AND I want to run the delete and insert in the batch in sequence.
the following includes my code where productProperties includes the list of each product and its properties
on the first iterate I want to delete so I fill in variables and else I don't want to so I fill in NULL

for (int i = 0 ; i< productProperties.length;i++){
			if(productProperties[i].getTrnsTypeCode().equals(ATransaction.A)){
if(i == 0){
proPreStProdProperties.setString(1, item);
				
}else{
proPreStProdProperties.setNull(1, Types.VARCHAR);
}
proPreStProdProperties.setString(2,item);
proPreStProdProperties.setString(3,productProperties[i].getPropertyKey());
proPreStProdProperties.setString(4,productProperties[i].getPropertyValue());
				
proPreStProdProperties.addBatch();
}

HOW can I get rid of executing the delete query when it NULL since it does not do nothing?

 

[bborissov]

Make it SP and call it with parameters.
then use something like:

IF @par1 IS NOT NULL
   BEGIN
       DELETE .....
   END
....

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[abkad]

HI bborissov,

could you be more clearer how to do that?

Thanks..

 

[bborissov]

CREATE PROCEDURE WhatEverName(
       @DeleteId varchar(20),
       @InsertId varchar(20),
       @property_key [appropriate type and length here],
       @property_value [appropriate type and length here]
       )
AS
 BEGIN
     IF @DeleteId IS NOT NULL
        DELETE FROM properties WHERE id_itm = @DeleteId

      INSERT INTO properties (id_itm,property_key,property_value)
              VALUES (@InsertId,@property_key,@property_value)
  END

Then you could call this SP with appropriate parameters:

EXEC  WhatEverName  @InsertId = 'blabla',
                    @property_key = 724,
                    @property_value = 4376283
-- or
EXEC  WhatEverName  @DeleteId = ''blabla1',
                    @InsertId = 'blabla',
                    @property_key = 724,
                    @property_value = 4376283

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.