[eclipselink-users] problem customizing insert query

["Rodrigue Lagoue" <rlagoue@xxxxxxxxxxxxxx>]

Hi all!

i'm about to customize the insert operation by setting many sql string. Here are the setting

// insert action
        StoredFunctionCall functionCall = new StoredFunctionCall();
        functionCall.setProcedureName("topcare.top_photos.photos_insert");
        functionCall.setResult("MEDICAL_PHOTO_ID", Integer.class);
        functionCall.addNamedArgument("p_file_name_photo", "FILE_NAME");
        functionCall.addNamedArgument("p_region_of_interest",
                "REGION_OF_INTEREST");
        functionCall.addNamedArgument("p_photo_set_id", "PHOTO_SET_ID");
        functionCall.addNamedArgument("p_number_in_set", "NUMBER_IN_SET");
        functionCall.addNamedArgument("p_file_name_preview", "PREVIEW_NAME");
        functionCall.addNamedArgument("p_patient_id", "PATIENT_ID");
        descriptorQueryManager.setInsertCall(functionCall);
        SQLCall call = new SQLCall();
        call
                .setSQLString("UPDATE TOPCARE.MEDICAL_PHOTOS SET PHOTO = #PHOTO"
                        + " WHERE MEDICAL_PHOTO_ID= #MEDICAL_PHOTO_ID");
        descriptorQueryManager.getInsertQuery().addCall(call);
        call = new SQLCall();
        call
                .setSQLString("UPDATE TOPCARE.PREVIEW_PHOTOS SET PHOTO = #THUMBNAIL"
                        + " WHERE MEDICAL_PHOTO_ID= #MEDICAL_PHOTO_ID");
        descriptorQueryManager.getInsertQuery().addCall(call);

And at runtime the logger printed the following:

[EL Fine]: 2008.12.22 15:54:12.828--ClientSession(25996619)--Connection(18809828)--Thread(Thread[http-8080-Processor25,5,main])--BEGIN ? := topcare.top_photos.photos_insert(p_file_name_photo=>?, p_region_of_interest=>?, p_photo_set_id=>?, p_number_in_set=>?, p_file_name_preview=>?, p_patient_id=>?); END;
    bind => [=> MEDICAL_PHOTO_ID, Wasserlilien.jpg, , 30013, 0, null, 364]
[EL Fine]: 2008.12.22 15:54:12.859--ClientSession(25996619)--Connection(18809828)--Thread(Thread[http-8080-Processor25,5,main])--UPDATE TOPCARE.MEDICAL_PHOTOS SET PHOTO = ? WHERE MEDICAL_PHOTO_ID= ?
    bind => [[B@f85722, 37008]
[EL Fine]: 2008.12.22 15:54:12.875--ClientSession(25996619)--Connection(18809828)--Thread(Thread[http-8080-Processor25,5,main])--UPDATE TOPCARE.PREVIEW_PHOTOS SET PHOTO = ? WHERE MEDICAL_PHOTO_ID= ?
    bind => [[B@4b27ad, 37008]
[EL Fine]: 2008.12.22 15:54:12.875--ClientSession(25996619)--Connection(18809828)--Thread(Thread[http-8080-Processor25,5,main])--SELECT PHOTO FROM TOPCARE.PHOTO_VIEW WHERE (MEDICAL_PHOTO_ID = ?) FOR UPDATE
    bind => [37008]
[EL Fine]: 2008.12.22 15:54:12.937--ClientSession(25996619)--Thread(Thread[http-8080-Processor25,5,main])--SELECT 1 FROM DUAL
[EL Warning]: 2008.12.22 15:54:12.937--UnitOfWork(18089627)--Thread(Thread[http-8080-Processor25,5,main])--Exception [EclipseLink-4002] (Eclipse Persistence Services - 1.0.2 (Build 20081024)): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: ORA-01031:

As you can see, eclipselink added a query " SELECT PHOTO FROM TOPCARE.PHOTO_VIEW WHERE (MEDICAL_PHOTO_ID = ?) FOR UPDATE". I don't know why, since i don't need it.

eclipselink try to set make update on the view (Photo_View) since i didn't set it in the query manager, does anybody know why?

thanks for any help

Rodrigue