I tried to use spamfilter with MSSQL and it was ok. Next, I tried to use Oracle. First I met the error during creating tables (table tblServers is not included in the script and table tblQuarantine is missing one column - serverid). I corrected these errors. I met another errors in activity log:

09.01.03 14:55:06:625 -- Exception occurred during DoDeleteExpiredQuarantineList: ORA-00911: chybný znak
09.01.03 14:55:12:875 -- Exception occurred during UpdateListViewQuarantine: ORA-00911: chybný znak

What should I do?

Thanks.

Jan Brychta

Jan,

If you could please try to translate the error "chybný znak" in english we can try to understand what is happening.

Roberto F.
LogSat Software

I found this in Oracle manual:

ORA-00911: invalid character
Cause: Special characters are valid only in certain places. If special characters
other than $, _, and # are used in a name and the name is not enclosed in double
quotation marks (”), this message will be issued. One exception to this rule
is for database names; in this case, double quotes are stripped out and ignored.
Action: Remove the invalid character from the statement or enclose the object
name in double quotation marks.

I can send the reworked oracle script.

Jan

Jan,

We tested the scripts with Oracle 8i, can you please let us know what version you are using? Yes, if you can send us the scripts that work for you we can see if the same correction will work for the other errors you are seing.

Roberto F.
LogSat Software

We are using Oracle 8.0.4 version. It is quite old version :-)

I made these corrections in your scripts:

CREATE TABLE TBLSERVERS (
  SERVERID NUMBER(10) NOT NULL,
  SERVERNAME VARCHAR2(50) NOT NULL
);

and

CREATE TABLE TBLQUARANTINE (
       QUARID               NUMBER(10) NOT NULL
                                   CHECK (("QUARID" IS NOT NULL)),
       EMAILFROM            VARCHAR2(100) NULL,
       EMAILTO              VARCHAR2(100) NULL,
       SUBJECT              VARCHAR2(100) NULL,
       MSGDATE              DATE NULL,
       MSGID                NUMBER(10) NULL,
       REJECTID             NUMBER(10) NULL,
       REJECTDETAILS        VARCHAR2(200) NULL,
       DELIVER              NUMBER(2) NULL,
       EXPIRE               NUMBER(2) NULL,
       SERVERID             NUMBER(10) NULL
);

nothing else (no indexes, constraints...)

We are a sw-developer company and we have some expiriences with oracle. I thing we are able to try and correct your queries used in DoDeleteExpiredQuarantineList and UpdateListViewQuarantine - in case you can send them to us.

Jan

Sorry, I forgot this trigger:

create trigger TBLSERVERS_AUTOINCREMENT BEFORE INSERT on TBLSERVERS for each row
DECLARE l_pkval number(22)\;   
BEGIN 
  select ID_SEQ.nextval into l_pkval from dual\;   
  :new.SERVERID :=  l_pkval\;   
END\;
;

Anything new with this problem ?

Jan

Jan,

We've only tested with 8i and 9i, older versions are untested and we may not able to support them.

We use a lot of ADO objects in our code to try to make generic calls and let ADO take care of converting them to specific SQL for the various platforms. In case of the "UpdateListViewQuarantine" error, we do issue a simple SQL statement:

SELECT Count(QuarID) FROM tblQuarantine WHERE tblQuarantine.Expire = 0;

followed by:

    'SELECT tblQuarantine.QuarID, tblQuarantine.Deliver, tblQuarantine.Expire, tblQuarantine.EmailFrom, tblQuarantine.EmailTo, tblQuarantine.Subject, tblQuarantine.MsgDate, tblRejectCodes.RejectDesc, ' + '       tblQuarantine.MsgID, tblQuarantine.RejectDetails, tblQuarantine.ServerID ' +  'FROM   tblQuarantine INNER JOIN ' +'  tblRejectCodes ON tblQuarantine.RejectID = tblRejectCodes.RejectID ' +
    'WHERE tblQuarantine.Expire = 0 ' +
    'ORDER BY tblQuarantine.MsgDate DESC;';

In the case of the "DeleteExpiredQuarantine" things are more complex since we use a parameterized query (:MsgDate is parameter):

'UPDATE tblQuarantine SET Expire = 1 WHERE MsgDate <= :MsgDate AND ' +  '( (ServerID = ' + IntToStr(tblServersServerID) + ') OR (ServerID = 0) OR (ServerID IS NULL) );'

followed by a simple SQL:

DELETE tblMsgs FROM tblMsgs INNER JOIN tblQuarantine ' + 'ON tblMsgs.MsgID = tblQuarantine.MsgID WHERE tblQuarantine.Expire <> 0;'

If you turn on tracing in SQLnet you should be able to see how ADO converts these calls into SQL. Also, which provider are you using to connect? Microsoft OLE DB for Oracle, Oracle's OLE DB? Others? Have you tried switching providers?

Finally, which verison of SpamFilter are you using?

Roberto F.
LogSat Software

I tried to turn on sqlnet tracing and I think the problem is in one extra semicolon ";" at the end of some commands.

I compared commands sent during Settings - Quarantine DB - Step 2 (these are without ;) with first command sent during Quarantine DB - Refresh (SELECT Count(QuarID) FROM tblQuarantine WHERE tblQuarantine.Expire = 0;). I am able to simulate the error in SQLPlus by adding one extra ; after the command.

I am using spamfilter v. 178 (non-registered) and MS Provider for Oracle (I dont know its version).

I watched the commands sent by you and I thing that Oracle doesnt use syntax "INNER JOIN" in any version. But it is possible that the ADO is able to translate the command properly :-o

I am going to try another OLE DB provider.

Bye.

Jan

Jan,

We may have fixed this problem for the older Oracle releases. I emailed you privately for further info.

Roberto F.
LogSat Software