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’)