powered by Jive Software

User search error

I have openfire setup using a custom user database. Login works fine, chatting works fine. User search doesn’t seem to work.

When using the Pidgin client, I select Accounts > [account] > Search for Users. It shows a window asking for the directory (is there any way to bypass this?) I use the default (it shows the string shown in the openfire admin for ‘search service name’) and press Search Directory. I enter a search term ‘Charlie’, click search, and am presented with an empty list.

The Openfire logs show:

2012.05.01 11:49:06 org.jivesoftware.openfire.user.JDBCUserProvider - Invalid object name ‘ofUser’.

com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name ‘ofUser’.

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServer Exception.java:216)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatemen t.java:1515)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatem ent(SQLServerPreparedStatement.java:404)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecu te(SQLServerPreparedStatement.java:350)

at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnec tion.java:1715)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStateme nt.java:180)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerState ment.java:155)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerP reparedStatement.java:285)

at org.jivesoftware.openfire.user.JDBCUserProvider.findUsers(JDBCUserProvider.java :352)

at org.jivesoftware.openfire.user.JDBCUserProvider.findUsers(JDBCUserProvider.java :288)

at org.jivesoftware.openfire.user.UserManager.findUsers(UserManager.java:321)

at org.jivesoftware.openfire.plugin.SearchPlugin.performSearch(SearchPlugin.java:6 04)

at org.jivesoftware.openfire.plugin.SearchPlugin.performSearch(SearchPlugin.java:6 26)

at org.jivesoftware.openfire.plugin.SearchPlugin.processSetPacket(SearchPlugin.jav a:512)

at org.jivesoftware.openfire.plugin.SearchPlugin.handleSearchRequest(SearchPlugin. java:356)

at org.jivesoftware.openfire.plugin.SearchPlugin.handleIQRequest(SearchPlugin.java :294)

at org.jivesoftware.openfire.plugin.SearchPlugin.processPacket(SearchPlugin.java:2 40)

at org.jivesoftware.openfire.component.InternalComponentManager$RoutableComponents .process(InternalComponentManager.java:587)

at org.jivesoftware.openfire.spi.RoutingTableImpl.routeToComponent(RoutingTableImp l.java:352)

at org.jivesoftware.openfire.spi.RoutingTableImpl.routePacket(RoutingTableImpl.jav a:237)

at org.jivesoftware.openfire.IQRouter.handle(IQRouter.java:324)

at org.jivesoftware.openfire.IQRouter.route(IQRouter.java:121)

at org.jivesoftware.openfire.spi.PacketRouterImpl.route(PacketRouterImpl.java:76)

at org.jivesoftware.openfire.net.StanzaHandler.processIQ(StanzaHandler.java:337)

at org.jivesoftware.openfire.net.ClientStanzaHandler.processIQ(ClientStanzaHandler .java:93)

at org.jivesoftware.openfire.net.StanzaHandler.process(StanzaHandler.java:302)

at org.jivesoftware.openfire.net.StanzaHandler.process(StanzaHandler.java:194)

at org.jivesoftware.openfire.nio.ConnectionHandler.messageReceived(ConnectionHandl er.java:169)

at org.apache.mina.common.support.AbstractIoFilterChain$TailFilter.messageReceived (AbstractIoFilterChain.java:570)

at org.apache.mina.common.support.AbstractIoFilterChain.callNextMessageReceived(Ab stractIoFilterChain.java:299)

at org.apache.mina.common.support.AbstractIoFilterChain.access$1100(AbstractIoFilt erChain.java:53)

at org.apache.mina.common.support.AbstractIoFilterChain$EntryImpl$1.messageReceive d(AbstractIoFilterChain.java:648)

at org.apache.mina.common.IoFilterAdapter.messageReceived(IoFilterAdapter.java:80)

at org.apache.mina.common.support.AbstractIoFilterChain.callNextMessageReceived(Ab stractIoFilterChain.java:299)

at org.apache.mina.common.support.AbstractIoFilterChain.access$1100(AbstractIoFilt erChain.java:53)

at org.apache.mina.common.support.AbstractIoFilterChain$EntryImpl$1.messageReceive d(AbstractIoFilterChain.java:648)

at org.apache.mina.filter.codec.support.SimpleProtocolDecoderOutput.flush(SimplePr otocolDecoderOutput.java:58)

at org.apache.mina.filter.codec.ProtocolCodecFilter.messageReceived(ProtocolCodecF ilter.java:185)

at org.apache.mina.common.support.AbstractIoFilterChain.callNextMessageReceived(Ab stractIoFilterChain.java:299)

at org.apache.mina.common.support.AbstractIoFilterChain.access$1100(AbstractIoFilt erChain.java:53)

at org.apache.mina.common.support.AbstractIoFilterChain$EntryImpl$1.messageReceive d(AbstractIoFilterChain.java:648)

at org.apache.mina.filter.executor.ExecutorFilter.processEvent(ExecutorFilter.java :239)

at org.apache.mina.filter.executor.ExecutorFilter$ProcessEventsRunnable.run(Execut orFilter.java:283)

at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

at org.apache.mina.util.NamePreservingRunnable.run(NamePreservingRunnable.java:51)

at java.lang.Thread.run(Unknown Source)

SQL Server profiler shows:

declare @p1 int

set @p1=NULL

exec sp_prepexec @p1 output,N’@P0 nvarchar(4000)’,N’SELECT username FROM ofUser WHERE email LIKE @P0 ‘,N’%Charlie%’

select @p1

Openfire.script is configured:

INSERT INTO OFPROPERTY VALUES(‘jdbcUserProvider.searchSQL’,‘SELECT ua.username FROM UserAccount ua INNER JOIN [User] u on (u.UserID = ua.UserID) WHERE Locked <> 1 AND’)