View Issue Details

Jump to NotesJump to History
This bug affects 1 person(s).
 4
IDProjectCategoryView StatusDate SubmittedLast Update
13140Bug reportsOtherpublic2018-01-08 15:122018-01-26 14:51
Reporterfvanderstarre Assigned Tomarkusfluer 
PrioritynoneSeveritypartial_block 
Status closedResolutionunable to reproduce 
Product Version3.0.0-rc.x 
Summary13140: Storing character data in ms-sql
Description

Character data are not stored natively as UTF8 data in ms-sql. Instead, UCS-2 is used. To prevent problems database update statements for strings should read: (example) "UPDATE dbo.survey_881359 SET [lastpage]=2, [datestamp]='2018-01-08 14:33:30', [881359X1826X30711]= (N'ccaron č - scaron š'), [881359X1826X30698]='1', [881359X1826X30700]='' WHERE ID=17".
Mind query text: ...= (N'character string'), to be specified when using mssql as a database!

Steps To Reproduce

Try entering "č" as a character in some question text or text answer. It will be stored as "c" when using a ms-sql database.

Additional Information

Strangely enough, "š" will not give any trouble (as long as database text column is defined as nvarchar() or nchar() or ntext.
This may be more of a CDbCommand issue, not so much LimeSurvey itself?

TagsNo tags attached.
Bug heat4
Complete LimeSurvey version number (& build)3.0.1 build 171228, 2.6.4lts build 170202, in fact any...
I will donate to the project if issue is resolvedNo
BrowserAny
Database type & versionMS-SQL 2008 R2
Server OS (if known)Linux RHEL 7
Webserver software & version (if known)Apache 2.4.6
PHP Version5.6.33

Users monitoring this issue

There are no users monitoring this issue.

Activities

c_schmitz

c_schmitz

2018-01-24 16:30

administrator   ~46026

No problems here with MSSQL 2012 on a Windows machine.
I think it is a freeTDS/dblib driver issue.
c_schmitz

c_schmitz

2018-01-24 16:32

administrator   ~46027

@fvanderstarre Have you set your FreeTDS version properly? See https://manual.limesurvey.org/Troubleshooting#No_questions_are_shown_when_using_MSSQL_Server_with_FreeTDS_on_Linux
fvanderstarre

fvanderstarre

2018-01-25 07:49

reporter   ~46057

In our system we have to use: TDSversion = 0. If set to 8, we get an error:
"General error: 20018 Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier."
c_schmitz

c_schmitz

2018-01-25 09:23

administrator   ~46062

This is obviously a server configuration error. See https://stackoverflow.com/questions/5414890/mssql-query-issue-in-php-and-querying-text-data for possible solutions.
fvanderstarre

fvanderstarre

2018-01-26 09:28

reporter   ~46104

Carsten, I don't agree. See this table taken from the freeTDS.org web page:
Product TDS Version Comment

Microsoft SQL Server 2000 7.1 Include support for bigint (64 bit integers), variant and collation on all fields. Collation is not widely used.
Microsoft SQL Server 2005 7.2 Includes support for varchar(max), varbinary(max), xml datatypes and MARS[a].
Microsoft SQL Server 2008 7.3 Includes support for time, date, datetime2, datetimeoffset.
Microsoft SQL Server 2012 or 2014 7.4 Includes support for session recovery.
N/A 8.0 FreeTDS will alias this version to 7.1 for backwards compatibility reasons, but this should be avoided due to future compatibility concerns. See note below on obsolete versions.

Furthermore, in LS version 2.05 plus build 141229 several database column definitions were changed from varchar(..) to nvarchar(..), like <title> in <questions> for instance, but ONLY IN THE INSTALLER, NOT the update script. So we have missed this in our installation where we updated from one versio to the other due to the large number of ongoing surveys we have here (several hundred).
Even without this driver issue we are unable now to store certain characters in our database!
fvanderstarre

fvanderstarre

2018-01-26 14:51

reporter   ~46113

Also, if character like 'č' is already in the database, it is retrieved and displayed OK, but it is lost when changes to e.g. question text are made in the application front end.

Issue History

Date Modified Username Field Change
2018-01-08 15:12 fvanderstarre New Issue
2018-01-08 15:20 LouisGac Assigned To => markusfluer
2018-01-08 15:20 LouisGac Status new => assigned
2018-01-24 16:30 c_schmitz Note Added: 46026
2018-01-24 16:32 c_schmitz Note Added: 46027
2018-01-25 07:49 fvanderstarre Note Added: 46057
2018-01-25 09:23 c_schmitz Note Added: 46062
2018-01-25 09:24 c_schmitz Status assigned => closed
2018-01-25 09:24 c_schmitz Resolution open => unable to reproduce
2018-01-26 09:28 fvanderstarre Note Added: 46104
2018-01-26 14:51 fvanderstarre Note Added: 46113