TZConnection, MSSQL, Windows Authentication, TDS

[miab3]

Hi Jan,

Has an additional problem with freetds-mssql.
It seems to me that the current ZEOS 7.2-7.3 (svn 3782) opens tables only in readonly mode.
This applies to new Lazarus (FPC 3.0, for example Lazarus 1.6RC2, CT).
ADD:Problem is in svn3750

Michal

[miab3]

Svn 3801(3799) fixes this bug.

Michal

[marsupilami]

Hello Michal,

I am not sure if the previously supplied sybdb.dll is correct. It lacks ICONV support. I tested it and it very specifically has problems when it comes to converting MSSQL characters to UTF-8 and most probably is only suitable for use with WIN1252 / ISO-8859-1.

So - that brings me to your project. Your project uses a connection character set setting of UTF8. This cannot work because the current Zeos trunk doesn't instruct freetds to do any character conversions. So the only option is to set the connection character set to WIN1252.

Without explicitly knowing your table names I cannot debug this problem any further. So much for that.

What I did nonetheless: I built a sybdb.dll that has iconv support. But it will not allow for better operation without modifications to Zeos. I created a list of 1000 tables with random table names. Each table name consists of 10 characters that are randomly taken from the unicode standard, are left to right characters that are explicitly uppercase or lowercase chatracters. Due to the nature of the unicode BMP these table names eem to consist mostly of eastern european characters. After creating these tables I fed the list of table names into a program that compared the table names in the list with the table names that I got from the database. It seems that the table names were delivered correctly.

Since the sybdb.dll with iconv support is too big to download I put it up for download on a server: http://www.iks.ag/downloads/sybdb.zip
This is a preliminary sybdb.dll for Windows/32Bit. Why preliminary? Because it lacks version information. Also I would like to make it depend on the iconv.dll instead of statically linking iconv, if easily possible. Also you will find the required patch to make this sybdb.dll work with Zeos. I am not sure yet which further modifications are required to make it work with the Zeos internal character set support options.

If you decide to test this you should set the connection character set to "UTF-8" for connections to MSSQL with freetds and this library.

Later on I will provide information on how to compile the current freetds with iconv support on Windows. Currently I don't intend to keep this work current as I don't have any projects using any of the dblib drivers (mssql, sybase, freetds). If anybody wants to step up to do this just let me know.

With best regards,

Jan

[marsupilami]

Hello, I just tried to put that on the Zeoslilb Wiki but for some odd reason it doesn't let me create a page. So - here is my current way to create freetds with libiconv support on Windows using Visual Studio:

How to build freetds with ICONV support:

This will also work for building freetds without ICONV support. Just skip everything that has to do with libiconv.

Step1: get and build libiconv:
libiconv is available from http://savannah.gnu.org/projects/libiconv/. Download the latest version and extract it. I used Version 1.14. Go to the bugtracker. bug #35088 (Patch for native Visual Studio 2010 compilation) contains a patch to add Visual Studio 2010 project files. Download the patch and apply it to libiconv.

Step2: build libiconv:
Now open the project file <libiconvdir>\windows\VC10\iconv.vcxproj. Accept the suggestion of Visual Studio to Upgrade to a newer project version. Set the Solution Configuration to Release and the Platform to Win32 at the top of the IDE. Now select the iconv project in the Solution Explorer. Build it by either clicking Build -> Build iconv.
You will get some files in the folder <libiconvdir>\windows\VC10\Release

Step3: Get CMake
FreeTDS suggests to use CMake for building with Microsoft Visual Studio. Cmake will be used to generate the project files for use with Visual Studio. Download it from https://cmake.org/download/ and install it.

Step4: Get Perl
Download ActivePerl from http://www.activestate.com/activeperl and install it. CMake will need it later on.

Step5: Download FreeTDS
FreeTDS can be downloaded from www.freetds.org. The version I downloaded had some issues that prevented me from generating the project files with CMake. So I downloaded the latest revision of the 0.95 branch from Github: https://github.com/FreeTDS/freetds/tree/Branch-0_95. Extract your download.

Step6: Modify CMakeLists.txt
In your FreeTDS source directory there is a file called CMakeLists.txt. Open it in the texteditor of your choice. Find the part that starts with the following lines:

if(WIN32)
config_write("/* Define to 1 if you want to use SSPI for Win32 */\n#define HAVE_SSPI 1\n\n")
config_write("/* define to format string used for 64bit integers */\n#define TDS_I64_PREFIX \"I64\"\n\n")
config_write("/* Define to 1 if you have the SQLGetPrivateProfileString function. */\n#define HAVE_SQLGETPRIVATEPROFILESTRING 1\n\n")


After these lines insert the following lines:

config_write("
// iconv
#define HAVE_ICONV 1
#define ICONV_CONST
")

These lines will make sure libiconv gets used during the build process in Visual Studio. It seems that the current CMakeLists.txt just assumes that libiconv is not available on Windows and is always available on other systems.

Step7: Generate the Configuration files:
Create a directory where you want the Visual Studio project files to go and the build process to happen. This directory should not be in the FreeTDS source tree.

1) Open up cmake-gui.
2) Put the directory for your sourcecode in the field "Where is the source code". cmakegui will replace the backslashes with forward slashes. This is normal behaviour.
3) Put the newly created build directory in the field "Where to build the binaries:" Again cmakegui will replace the backslashes with forward slashes.
4) Press the configure Button. You will be presented with a lot of IDEs and build environments. Choose your Version of Visual Studio, keep the selection "Use default native compilers" and press finish.
5) Wait for CMake to finish the configuration.
6) Press the Generate button. CMake will now generate the project files for you.
7) Close cmake-gui.

Step 8: Modify the FreeTDS projects
CMake has generated the file ALL_BUILD.vcxproj in your <freetdsbuilddir>. Open it with Visual Studio. Wait for Visual Studio to load the Solution and all its subprojects. Some of the projects need to be modified because they now contain references to iconv.lib and iconv.h and Visual Studio doesn't know how to satify them.
In the solution explorer select the project in question and select Properties in the right clieck menu. The properties window will open.
For projects that require you to modify their Visual C++ Directories, select Configuration Properties -> VC++ Directories on the left hand pane. Now add <libiconvdir>\include to the Include Directories property.
For projects that require you to modify their linker settings select Configuration Properties -> Linker -> General. Add the directory <libiconvdir>\windows\VC10\Release to the Additional Library Directories property. Then select Configuration Properties -> Linker -> Input and add iconv.lib to the Additional Dependencies property.
After finishing these modifications, click ok to activate the changes and close the Property Pages window.

The following projects need to be modified:
tds: Visual C++ Directories
sybdb: Visual C++ Directories and linker settings
tdsodbc: Visual C++ Directories and linker settings
db-lib: Visual C++ Directories
tsql: Visual C++ Directories and linker settings
ct: linker settings

now right-click on the ALL_BUILD project and build it.

Note: if you only want to build the freetds dll it just might be enough to build the sybdb project. In that case modifying the tds project and the sybdb project might just be enough.

If the build succeeds, you will find the sybdb.dll in the directory <freetdsbuilddir>\src\dblib\Release

Congatulations - you now hav e working freetds dblib.

[marsupilami]

Just a note: I uploaded the necessary modifications to the SVN. Specifying UTF-8 as the connection character set should work now if FreeTDS has support for libiconv. I experienced some problems when trying these modifications with the dblib version in the Zeos SVN, so these modification might break compatibility with those old versions.

[EgonHugeist]

Hands up Jan, aweful! If i've time i'll finalize the remaing implementation for optional use of LibIconv etc.

[miab3]

Jan,

It seems that after previous and the recent updates (svn 3834-3836) your library works correctly.

Michal

[marsupilami]

I posted a more easy way to build freetds to the Zeos Wiki on sourceforge.net: https://sourceforge.net/p/zeoslib/wiki/ ... 32%20Bits/

[miab3]

Jan,

I see that after these changes(svn 3837) mssql-ntwdblib does not always work.
Did you check it?

NEW:
Although this (attached) version is probably better:

ntwdblib.dll.zip

Michal

[marsupilami]

Hello MIchal,

currently I am working on an enviromnent for tests. The idea was to also test the ntwdblib when this environment is finished. Do you have some information on the things that don't work?
With best regards,

Jan

[marsupilami]

Addendum: I just uploaded the current sybdb.dll files of FreeTDS to https://sourceforge.net/projects/zeoslib/files/3rd party/FreeTDS/. Modifications to the source to look for these libs will follow soon.

[miab3]

Hi Jan,

Library sybdb.dll Win 32/64 (svn 3843) works great.
But for not UTF-8 it:
ftp://ftp.freepascal.org/fpc/contrib/wi ... b_0.95.zip
seems to be better:


Thanks,
Michal

[gelinp]

I just checkouted with subversion last trunk 7.2 from sourceforge but same problem : Datasets are readonly ! Impossible to get it updatables...

This the code used to checkout :

Code: Select all

svn checkout svn://svn.code.sf.net/p/zeoslib/code-0/trunk zeoslib-code-0

I'm using Lazarus 1.6, Free pascal 3.0, Linux debian using SQLite-3, Linux target 32 bits.

What can I do ?

[marsupilami]

Hello gelinp,

why do you post to at thread that is about MSSSQL if youz ahve a problem using SQLite-3?
With best reghards,

Jan