Блог

У меня есть этот фрагмент кода, который хорошо работает в DB2 и Oracle, но не работает в MSSQL (как 2005, так и 2008).

 c = SessionMgr.getInstance().getTOPLinkManager().getDataSource().getConnection();
String sql = "";
sql = "{CALL "   TOPLinkManager.getSchemaPrefix()   "CONTACT_REFERENCES (?, ?, ?)}";
cs = c.prepareCall(sql);
cs.setInt(1, dateType);
cs.setDate(2, date);
cs.setInt(3, operator);
cs.execute();
cs.close();
sql= "SELECT FIRST_NAME,LAST_NAME,CREATED,LASTMODIFIED FROM CONTACT WHERE EXISTS(SELECT * FROM CONTACT_REF WHERE CONTACT.SEQID = CONTACT_REF.SEQID AND CONTACT.SITEID = CONTACT_REF.SITEID)";
ps = c.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
  

Вызванная хранимая процедура заполняет таблицу CONTACT_REF , которая используется в запросе для получения данных с сервера.

Генерируемое исключение является:

 com.microsoft.sqlserver.jdbc.SQLServerException: The server failed to resume the transaction. Desc:3c00000002.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:388)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:338)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:185)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:160)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:281)
at weblogic.jdbc.wrapper.PreparedStatement.executeQuery(PreparedStatement.java:100)
at marketsoft.tools.purge.actions.PurgeContactAction.checkReferences(PurgeContactAction.java:126)
  

Это вызываемая хранимая процедура:

 IF OBJECT_ID ('CONTACT_REFERENCES', 'P') IS NOT NULL
   DROP PROCEDURE CONTACT_REFERENCES
go

CREATE PROCEDURE CONTACT_REFERENCES
@datetype int,
@created DATETIME,
@operator int
AS
BEGIN
BEGIN TRANSACTION

CREATE TABLE #DUP_REF
(
SEQID NUMERIC(19,0),
SITEID NUMERIC(10,0)
)

DELETE FROM PURGE_CONTACT;
DELETE FROM CONTACT_REF;
IF @datetype = 0 BEGIN
IF @operator = 0 BEGIN
insert into PURGE_CONTACT SELECT SEQID,SITEID,DETAILID,DETAILSITE from CONTACT WHERE CREATED < @created;
END

ELSE IF @operator = 1 BEGIN
insert into PURGE_CONTACT SELECT SEQID,SITEID,DETAILID,DETAILSITE from CONTACT WHERE DATEPART(yy,CREATED) = DATEPART(yy,@created)
                                                            AND DATEPART(mm,CREATED) = DATEPART(mm,@created)
                                                            AND DATEPART(dd,CREATED) = DATEPART(dd,@created);
END

ELSE IF @operator = 2 BEGIN
insert into PURGE_CONTACT SELECT SEQID,SITEID,DETAILID,DETAILSITE from CONTACT WHERE CREATED > @created;
END
END
ELSE IF @datetype = 1 BEGIN
IF @operator = 0 BEGIN
insert into PURGE_CONTACT SELECT SEQID,SITEID,DETAILID,DETAILSITE from CONTACT WHERE LASTMODIFIED < @created;
END

ELSE IF @operator = 1 BEGIN
insert into PURGE_CONTACT SELECT SEQID,SITEID,DETAILID,DETAILSITE from CONTACT WHERE DATEPART(yy,LASTMODIFIED) = DATEPART(yy,@created)
                                                            AND DATEPART(mm,LASTMODIFIED) = DATEPART(mm,@created)
                                                            AND DATEPART(dd,LASTMODIFIED) = DATEPART(dd,@created);
END

ELSE IF @operator = 2 BEGIN
insert into PURGE_CONTACT SELECT SEQID,SITEID,DETAILID,DETAILSITE from CONTACT WHERE LASTMODIFIED > @created;
END
END


INSERT INTO #DUP_REF SELECT SEQID,SITEID
FROM PURGE_CONTACT
WHERE EXISTS (SELECT *
              FROM LEAD
              WHERE LEAD.CONTACTID = PURGE_CONTACT.SEQID
                    AND LEAD.CONTACTSITE = PURGE_CONTACT.SITEID);

INSERT INTO #DUP_REF SELECT SEQID,SITEID
FROM PURGE_CONTACT
WHERE EXISTS (SELECT *
              FROM MAPRESULT
              WHERE MAPRESULT.CONTACTSEQ = PURGE_CONTACT.SEQID
                    AND MAPRESULT.CONTACTSITE = PURGE_CONTACT.SITEID);

INSERT INTO #DUP_REF SELECT SEQID,SITEID
FROM PURGE_CONTACT
WHERE EXISTS (SELECT *
              FROM USERS
              WHERE USERS.CONTACTID = PURGE_CONTACT.SEQID
                    AND USERS.CONTACTSITE = PURGE_CONTACT.SITEID);

INSERT INTO #DUP_REF SELECT SEQID,SITEID
FROM PURGE_CONTACT
WHERE EXISTS (SELECT *
              FROM PLAYER
              WHERE PLAYER.CONTACTID = PURGE_CONTACT.SEQID
                    AND PLAYER.CONTACTSITE = PURGE_CONTACT.SITEID);

INSERT INTO #DUP_REF SELECT SEQID,SITEID
FROM PURGE_CONTACT
WHERE EXISTS (SELECT *
              FROM SHOPPINGCART
              WHERE SHOPPINGCART.CONTACT_SEQID = PURGE_CONTACT.SEQID
                    AND SHOPPINGCART.CONTACT_SITEID = PURGE_CONTACT.SITEID);

INSERT INTO CONTACT_REF SELECT DISTINCT SEQID,SITEID FROM #DUP_REF;

DROP TABLE #DUP_REF;

DELETE FROM PURGE_CONTACT
WHERE EXISTS (SELECT *
              FROM CONTACT_REF
              WHERE CONTACT_REF.SEQID = PURGE_CONTACT.SEQID
                    AND CONTACT_REF.SITEID = PURGE_CONTACT.SITEID);

COMMIT

END
go