ejabberd - Comments for "Getting ejabberd working with MS SQL on Windows"

ODBC VCard Mod - MS SQL

mr kevin — Wed, 08 Nov 2006 20:54:35 +0000

It's been 9 months since the last comment in this thread, but I recently came across this same problem and thought I'd post the solution I used. As I'm neither an expert in ejabberd nor Erlang, any ideas concerning a better fix would be appreciated.

I should point out that this is modified from the ejabberd 1.1.2 release, NOT the 1.0.0 release mentioned in the original topic.

First, I uncommented this line in odbc_queries.erl and recompiled:

odbc_queries.erl, line 37 wrote:

%-define(mssql, true).

Next, I modified mod_vcard_odbc.erl and recompiled:

mod_vcard_odbc.erl, lines 496 - 509 wrote:

			Val when is_integer(Val) and (Val > 0) ->
			    [" TOP ", integer_to_list(Val)];
			Val ->
			    ?ERROR_MSG("Illegal option value ~p. "
				       "Default value ~p substituted.",
				       [{matches, Val}, ?JUD_MATCHES]),
			    [" TOP ", integer_to_list(?JUD_MATCHES)]
		    end,
	    case catch ejabberd_odbc:sql_query(
			 LServer,
			 ["select",Limit," username, fn, family, given, middle, "
			  "       nickname, bday, ctry, locality, "
			  "       email, orgname, orgunit from vcard_search ",
			  MatchSpec, ";"]) of

In addition to modifying the source, I found the mssql.sql file was incorrect. The following is the appropriate schema:

MSSQL VCard schema wrote:

DROP TABLE [vcard]

CREATE TABLE [vcard] (
	[username] [varchar] (250) NOT NULL ,
	[vcard] [varchar] (2000) NOT NULL ,
	 PRIMARY KEY  CLUSTERED 
	(
		[username]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

CREATE TABLE [vcard_search] (
	[username] [varchar] (250) NOT NULL ,
	[lusername] [varchar] (250) NOT NULL ,
	[fn] [varchar] (250) NOT NULL ,
	[lfn] [varchar] (250),
	[family] [varchar] (250) NOT NULL ,
	[lfamily] [varchar] (250) NOT NULL ,
	[given] [varchar] (250) NOT NULL ,
	[lgiven] [varchar] (250) NOT NULL ,
	[middle] [varchar] (250) NOT NULL ,
	[lmiddle] [varchar] (250) NOT NULL ,
	[nickname] [varchar] (250) NOT NULL ,
	[lnickname] [varchar] (250) NOT NULL ,
	[bday] [varchar] (250) NOT NULL ,
	[lbday] [varchar] (250) NOT NULL ,
	[ctry] [varchar] (250) NOT NULL ,
	[lctry] [varchar] (250) NOT NULL ,
	[locality] [varchar] (250) NOT NULL ,
	[llocality] [varchar] (250) NOT NULL ,
	[email] [varchar] (250) NOT NULL ,
	[lemail] [varchar] (250) NOT NULL ,
	[orgname] [varchar] (250) NOT NULL ,
	[lorgname] [varchar] (250) NOT NULL ,
	[orgunit] [varchar] (250) NOT NULL ,
	[lorgunit] [varchar] (250) NOT NULL ,
	 PRIMARY KEY  CLUSTERED 
	(
		[lusername]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO

If there are any questions, I could post the actual files.

ejabberd/odbc error text

mcannon — Wed, 01 Feb 2006 20:57:40 +0000

=ERROR REPORT==== 1-Feb-2006::15:50:05 ===
E(<0.287.0>:gen_iq_handler:67): {{case_clause,
{error,
"[Microsoft][ODBC SQL Server Driver][S
L Server]Invalid column name 'vcard'. SQLSTATE IS: 42S22"}},
[{mod_vcard_odbc,process_sm_iq,3},
{gen_iq_handler,process_iq,6},
{gen_iq_handler,queue_loop,3},
{proc_lib,init_p,5}]}

Working Somewhat

mcannon — Wed, 01 Feb 2006 20:22:40 +0000

Ok, I now have it authenicating and creating users against the SQL DB. However I am unable to add a contact or modify my vcard info.

I will continue plugging away but if anyone has any idea's I would appreciate any help I can get.

Mike

Re: Getting ejabberd working with MS SQL on Windows

sander — Tue, 31 Jan 2006 21:07:54 +0000

mcannon wrote:

Now I am trying to authenicate against MS SQL and am having a lot of trouble getting it to work. I have read through posts and have downloaded the mssql.zip but I still cannot get the server to work off SQL.

Has anyone been able to get it work and would you be willing to share your experience and ncessary steps?

I have no experience with MS SQL, so I only can recommend you trying next things:

Read the instructions to apply the MS SQL patch very carefully and explain what goes wrong
Read the threads (see bottom of that page)
Try to contact Tiaan, the author of that patch (only if all the above do not help!)