Looking for ejabberd docs?

To access the most up-to-date ejabberd documentation, please visit docs.ejabberd.im »

Getting ejabberd working with MS SQL on Windows

Submitted by mcannon on Mon, 2006-01-30 18:54

I have installed version 1.0.0 of ejabberd running under Windows server and it is working fine. 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?

Thank you,

Michael

Re: Getting ejabberd working with MS SQL on Windows

Submitted by sander on Tue, 2006-01-31 21:07.

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

Working Somewhat

Submitted by mcannon on Wed, 2006-02-01 20:22.

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

ejabberd/odbc error text

Submitted by mcannon on Wed, 2006-02-01 20:57.

=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}]}

ODBC VCard Mod - MS SQL

Submitted by mr kevin on Wed, 2006-11-08 20:54.

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.