mORMot Open Source

Bascy

Member

From: The Netherlands

Registered: 2012-06-22

Posts: 108

DB_FIELDS definition for MSSQL contains error

Using an OleDB connection to a MS SQL server, i get an error when it tries to create the AuthUser table with the following statement:

SQLite3DB.TSQLRestServerStaticExternal.ExecuteDirect('CREATE TABLE AuthUser (ID  bigint PRIMARY KEY, LogonName nvarchar(20) NOT NULL UNIQUE,DisplayName nvarchar(50),PasswordHashHexa nvarchar(40),GroupRights bigint,Data varbinary(max))',(...),(...),False)

Seems like the definition in DB_FIelds is wrong here:

  (' nvarchar(%)',' bigint',' float',' money',' datetime',' nvarchar(max)',' varbinary(max)'),

I guess the "max" should be substituted with the actual maximum: 8000

Last edited by Bascy (2012-09-13 08:46:00)

Bascy

Member

From: The Netherlands

Registered: 2012-06-22

Posts: 108

Re: DB_FIELDS definition for MSSQL contains error

And while your fixing this .... the DB_SQLLIMITCLAUSE for MSSQL should not be top(%) but top %

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,655

Website

Re: DB_FIELDS definition for MSSQL contains error

AFAIK

nvarchar(max) sounds OK to the MS SQL standard - see http://msdn.microsoft.com/en-us/library/ms186939

the same for top(%) - see http://msdn.microsoft.com/en-us/library/ms189463

or did I miss something from the official MSDN docs?

Bascy

Member

From: The Netherlands

Registered: 2012-06-22

Posts: 108

Re: DB_FIELDS definition for MSSQL contains error

I dont know if you missed something .. but in my setup of MSSQL 2005 and using OleDB connection .. these things need to be changed to get it working..
Have you seen it working with your initial values?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,655

Website

Re: DB_FIELDS definition for MSSQL contains error

I tried with Jet and MS SQL Server 2008, without problem.

Those parameters were already available for MS SQL Server 2005, as stated by http://msdn.microsoft.com/en-us/library … (v=sql.90)

nvarchar ( n | max )
Variable-length Unicode character data. n can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size, in bytes, is two times the number of characters entered + 2 bytes. The data entered can be 0 characters in length.

So I guess the issue is elsewhere.

Bascy

Member

From: The Netherlands

Registered: 2012-06-22

Posts: 108

Re: DB_FIELDS definition for MSSQL contains error

haha now i understand .. our MS SQL is not 2005 ... its 2000!

and the definition of nvarchar says:

nchar and nvarchar
Character data types that are either fixed-length (nchar) or variable-length (nvarchar) Unicode data and use the UNICODE UCS-2 character set.

nchar(n)

Fixed-length Unicode character data of n characters. n must be a value from 1 through 4,000. Storage size is two times n bytes. The SQL-92 synonyms for nchar are national char and national character.

nvarchar(n)

Variable-length Unicode character data of n characters. n must be a value from 1 through 4,000. Storage size, in bytes, is two times the number of characters entered. The data entered can be 0 characters in length. The SQL-92 synonyms for nvarchar are national char varying and national character varying.

Remarks
When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.

Last edited by Bascy (2012-09-13 15:03:33)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,655

Website

Re: DB_FIELDS definition for MSSQL contains error

MS SQL 2000 is not a supported target for mORMot...
But I suspect it won't be an issue for users.
This version has been deprecated since years.
See http://support.microsoft.com/lifecycle/ … 11&p1=2852