Hi, like a few people here, avatars for transports are not working, my error log is full of …
2008.08.15 14:53:54 [org.jivesoftware.openfire.gateway.util.Log4JToOpenfireAppender.append(Log4JToO penfireAppender.java:49)
] Avatar: SQL exception while inserting avatar:
java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:213)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.jav a:952)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:11 60)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatem ent.java:3285)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatemen t.java:3368)
at sun.reflect.GeneratedMethodAccessor12.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:100)
at org.logicalcobwebs.proxool.ProxyStatement.intercept(ProxyStatement.java:57)
at oracle.jdbc.OracleStatement$$EnhancerByProxool$$7772aabb.executeUpdate()
at org.jivesoftware.openfire.gateway.avatars.Avatar.insertIntoDb(Avatar.java:277)
at org.jivesoftware.openfire.gateway.avatars.Avatar.(Avatar.java:118)
at org.jivesoftware.openfire.gateway.protocols.oscar.BasicFlapConnection$2.handleR esponse(BasicFlapConnection.java:262)
at net.kano.joscar.snac.SnacRequest.gotResponse(SnacRequest.java:344)
at net.kano.joscar.snac.ClientSnacProcessor.continueHandling(ClientSnacProcessor.j ava:727)
at net.kano.joscar.snac.AbstractSnacProcessor.processPacket(AbstractSnacProcessor. java:447)
at net.kano.joscar.snac.AbstractSnacProcessor.access$100(AbstractSnacProcessor.jav a:134)
at net.kano.joscar.snac.AbstractSnacProcessor$1.handlePacket(AbstractSnacProcessor .java:208)
at net.kano.joscar.flap.AbstractFlapProcessor.processPacketSynchronously(AbstractF lapProcessor.java:231)
at net.kano.joscar.flap.AsynchronousFlapProcessor$BackgroundPacketProcessor.tryPro cessingPackets(AsynchronousFlapProcessor.java:75)
at net.kano.joscar.flap.AsynchronousFlapProcessor$BackgroundPacketProcessor.run(As ynchronousFlapProcessor.java:63)
at java.lang.Thread.run(Unknown Source)
We’re using here:
Oracle Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options
with Oracle JDBC driver 10.2.0.1.0
Can someone please look into it ?
Thanks in advance
MK
Hrm. Seeing as there’s no tables with LONG in them for the im gateway plugin for oracle, I’m guessing that I’m trying to insert a long value into an integer column. Must be for the dates. Try editing your database, editing gatewayAvatars, changing columns createDate and lastUpdate to LONG instead of INTEGER and see if that fixes it. (please let me know if that fixes it)
hi, so … we have tried to create table like this:
Error starting at line 1 in command:
CREATE TABLE gatewayAvatars (
jid VARCHAR2(255) NOT NULL,
imageData BLOB NOT NULL,
xmppHash VARCHAR2(255),
legacyIdentifier VARCHAR2(255),
createDate LONG NOT NULL,
lastUpdate LONG,
imageType VARCHAR2(25)
)
Error at Command Line:13 Column:3
Error report:
SQL Error: ORA-01754: a table may contain only one column of type LONG 01754. 00000 - “a table may contain only one column of type LONG”
*Cause: An attempt was made to add a LONG column to a table which already
had a LONG column. Note that even if the LONG column currently
in the table has already been marked unused, another LONG column
may not be added until the unused columns are dropped.
*Action: Remove the LONG column currently in the table by using the ALTER
TABLE command.
and oracle don’t like it for some reason, we have also tried to alter these two columns as varchar(200), it didn’t work as wel, we tried date, and the error look now like:
2008.08.20 09:02:14 [org.jivesoftware.openfire.gateway.util.Log4JToOpenfireAppender.append(Log4JToO penfireAppender.java:49)
] Avatar: SQL exception while inserting avatar:
java.sql.SQLException: ORA-00932: inconsistent datatypes: expected DATE got NUMBER
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:213)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.jav a:952)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:11 60)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatem ent.java:3285)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatemen t.java:3368)
at sun.reflect.GeneratedMethodAccessor13.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.logicalcobwebs.proxool.ProxyStatement.invoke(ProxyStatement.java:100)
at org.logicalcobwebs.proxool.ProxyStatement.intercept(ProxyStatement.java:57)
at oracle.jdbc.OraclePreparedStatement$$EnhancerByProxool$$5d6f327d.executeUpdate( )
at org.jivesoftware.openfire.gateway.avatars.Avatar.insertIntoDb(Avatar.java:277)
at org.jivesoftware.openfire.gateway.avatars.Avatar.(Avatar.java:118)
at org.jivesoftware.openfire.gateway.protocols.oscar.BasicFlapConnection$2.handleR esponse(BasicFlapConnection.java:262)
at net.kano.joscar.snac.SnacRequest.gotResponse(SnacRequest.java:344)
at net.kano.joscar.snac.ClientSnacProcessor.continueHandling(ClientSnacProcessor.j ava:727)
at net.kano.joscar.snac.AbstractSnacProcessor.processPacket(AbstractSnacProcessor. java:447)
at net.kano.joscar.snac.AbstractSnacProcessor.access$100(AbstractSnacProcessor.jav a:134)
at net.kano.joscar.snac.AbstractSnacProcessor$1.handlePacket(AbstractSnacProcessor .java:208)
at net.kano.joscar.flap.AbstractFlapProcessor.processPacketSynchronously(AbstractF lapProcessor.java:231)
at net.kano.joscar.flap.AsynchronousFlapProcessor$BackgroundPacketProcessor.tryPro cessingPackets(AsynchronousFlapProcessor.java:75)
at net.kano.joscar.flap.AsynchronousFlapProcessor$BackgroundPacketProcessor.run(As ynchronousFlapProcessor.java:63)
at java.lang.Thread.run(Unknown Source)
So we’re planning in starting javavm with tracing mode for oracle driver, so we’ll see the exact query.
Comparing to Openfire’s db scripts, it looks like LONG is something else in Oracle. Looks like INTEGER is generally used, which is what we had. CHAR(15) is used for dates for some reason. I switched to numbers so that ordering could work correctly. I don’t really know why DATETIME or whatever is considered a bad thing – ie why we don’t use it in Openfire. So going back to the original issue, I think we’re looking at the wrong item. Change the date ones back to INTEGER and change that CLOB (imageData) to LONG. I -think- that would cover it. You’d also need to update gatewayVCards to change it’s CLOB to LONG I think. Please let me know if that works.
Hmmm, what we tested so far, and co-worker of me told me. Long is obsolete for oracle, and it’s there just for some backward compatibility, and shouldn’t be used at all, you should stick to date,int,blob,varchar, which should be sufficient enough for everyone. I don’t know personally, cuz i’m not expert on databases so i trust him.We’ll try to switch the imageData from BLOB -> long, and the second one aswell. Will keep you informed
So …
drop table gatewayAvatars;
CREATE TABLE gatewayAvatars (
jid VARCHAR2(255) NOT NULL,
imageData LONG NOT NULL,
xmppHash VARCHAR2(255),
legacyIdentifier VARCHAR2(255),
createDate INTEGER NOT NULL,
lastUpdate INTEGER,
imageType VARCHAR2(25)
);
CREATE INDEX gatewayAvtr_jid_idx ON gatewayAvatars (jid);
drop table gatewayVCards;
CREATE TABLE gatewayVCards (
jid VARCHAR2(255) NOT NULL,
value LONG NOT NULL
);
CREATE INDEX gatewayVCrd_jid_idx ON gatewayVCards (jid);
This helped us, can se avatars now, and they are stored in db, still for future versinou i woul recommend you to switch to BLOB for oracles
Thnx Martin
Well, what I’m confused about is — CLOB is supposed to be the character version of BLOB (which is binary focused) We’re not storing the binary version of the avatar, we convert it to a hex based version first, so why wasn’t CLOB working would be my question. =D Any thoughts?
asked my chief about clob and blob difference. CLOB is actually doing some codepage conversions upon storing datas. So … it maybe could destroyed that avatar. Still, on other hands, as that error messages appeared in logs, both altered tables were completely empty. And as i can see you’re db scripts in plugin, you’re declaring the collumns as BLOB, not CLOB.
see
CREATE TABLE gatewayAvatars (
jid VARCHAR2(255) NOT NULL,
imageData BLOB NOT NULL,
xmppHash VARCHAR2(255),
legacyIdentifier VARCHAR2(255),
createDate INTEGER NOT NULL,
lastUpdate INTEGER,
imageType VARCHAR2(25)
Does it making any sense ?
Which version are you running? In trunk I have it as clob. =) Dooh!
as we’re enteprises we installed the stable versions …
so we’re running Openfire 3.5.2 with IM Gateway 1.2.3a
edit: i really should stated that out, sorry
No no worries =) I guess I fixed it as a pending fix for 1.2.4. Dooh!
Good to hear that ! Cuz it’s way to go and we’re starting to love openfire and spark combo in here . Cuz we want to get rid off all that mirandas and quips and skypes
Still, thanks again for fast support