[Lazarus] TMSSQLConnection how to specify the server port?

[Bo Berglund via Lazarus]

I am writing a subversion server hook application (command line) on
Windows using FPC 3.0.4 and Lazarus 1.8.0 and I have now come to the
auditing database connection I want to implement.

The database server is an MSSQLServer Express instance on a Windows
2016 Server and it runs on a non-standard TCP port.

In an earlier thread here in Feb 2016 I asked about how to use the
TMSSQLConnection component (on a Raspberry Pi) and it was basically
running after the help I got.

At the time the server I used was running as the default instance on
port 1433 and the component probably assumes this if not told
otherwise. So I did not think about this problem back then.

But now I have to address it, so:
How can I specify the port on the host where MSSQL listens?
I find no property named "Port" in he component...

Secondly:
---------
On the Raspberry Pi I had to install a FreeTDS library in order to get
a database connection. After I got that advice I included the apt-get
install for the library in my standard script for setting up
FPC/Lazarus on a Raspberry and forgot all about it. Always working
afterwards.
But now I am back on a Windows 7 machine and here I don't have a clue
as to how one installs the FreeTDS library.

Any pointers to how it is done?

[Mattias Gaertner via Lazarus]

On Wed, 27 Dec 2017 14:29:49 +0100

Post by Bo Berglund via Lazarus
[...]
How can I specify the port on the host where MSSQL listens?
I find no property named "Port" in he component...

Have you tried HostName:='hostname:port' ?

Mattias
--

[Graeme Geldenhuys via Lazarus]

Post by Mattias Gaertner via Lazarus
Have you tried HostName:='hostname:port' ?

Correct - as per the documentation. ;-)


https://www.freepascal.org/docs-html/fcl/mssqlconn/tmssqlconnection.hostname.html


Regards,
Graeme

[Graeme Geldenhuys via Lazarus]

Post by Bo Berglund via Lazarus
But now I am back on a Windows 7 machine and here I don't have a clue
as to how one installs the FreeTDS library.
Any pointers to how it is done?

You simply download the DLL and include it in the same directory as the
EXE of your application, or install it to a global location like
System32 directory.

ftp://ftp.freepascal.org/fpc/contrib/windows/

For more details:
http://wiki.freepascal.org/mssqlconn

And if all else fails, you can also look at the official Free Pascal
documentation:

https://www.freepascal.org/docs-html/fcl/mssqlconn/tmssqlconnection.html


I've connected to SQL Server instances from FreeBSD, Linux and Windows -
all without much trouble - once I followed the documentation.

Regards,
Graeme

[Bo Berglund via Lazarus]

On Wed, 27 Dec 2017 14:05:55 +0000, Graeme Geldenhuys via Lazarus

Post by Graeme Geldenhuys via Lazarus

Post by Bo Berglund via Lazarus
But now I am back on a Windows 7 machine and here I don't have a clue
as to how one installs the FreeTDS library.
Any pointers to how it is done?

You simply download the DLL and include it in the same directory as the
EXE of your application, or install it to a global location like
System32 directory.
ftp://ftp.freepascal.org/fpc/contrib/windows/

I have tested this and no matter which of the included dll files in
the zip I put into my project directory I always get the exception:

"... exception class 'EInOutError' with message
Can not load DB-lib client library "dblib.dll". Check your
installation."

Something must be amiss.
I have downloaded zipfiles 0.95 and 1.00

Both of them contain:
Win32:
dblib_2000.dll
dblib_2008.dll
libiconv2.dll
Win64:
dblib.dll
dblib_2008.dll
libiconv.dll

I have tried the only one with the correct name (in Win64), did not
work.
Then I renamed the Win32/dblib_2008.dll to dblib.dll, not working

Right now I am only at the very first step of the application, namely
where I connect to the server:

SVNA is a class where I intend to handle the audit database:
constructor TSvnAuditer.Create;
begin
FConn := TMSSQLConnection.Create(nil);
FQuery := TSQLQuery.Create(nil);
FTrans := TSQLTransaction.Create(nil);
FConn.Transaction := FTrans;
FQuery.Transaction := FTrans;
FQuery.DataBase := FConn;
end;


procedure TestDbConnection;
begin
SVNA.Server := 'servername:1433';
SVNA.Database := 'databasename';
SVNA.User := 'username';
SVNA.Passwd := 'password';
if SVNA.OpenConnection then
begin
SVNA.CloseConnection;
end;
end;

function TSvnAuditer.OpenConnection: boolean;
begin
Result := false;
if (FServer = '') or (FUser = '') or (FPasswd = '') or (FDatabase =
'') then
begin
FErrorMsg := 'At least one of Server, Database, User or Password
has not been set!';
exit;
end;

if Connected then
CloseConnection;

FConn.HostName := FServer;
FConn.UserName := FUser;
FConn.Password := FPasswd;
FConn.DatabaseName := FDatabase;
FConn.Open; <== Exception here! Not finding the dll!
Result := Connected;
end;

[Bo Berglund via Lazarus]

On Wed, 27 Dec 2017 17:32:38 +0100, Bo Berglund via Lazarus

Post by Bo Berglund via Lazarus
On Wed, 27 Dec 2017 14:05:55 +0000, Graeme Geldenhuys via Lazarus

Post by Graeme Geldenhuys via Lazarus
You simply download the DLL and include it in the same directory as the
EXE of your application, or install it to a global location like
System32 directory.
ftp://ftp.freepascal.org/fpc/contrib/windows/

I have tested this and no matter which of the included dll files in
I have downloaded zipfiles 0.95 and 1.00
dblib_2000.dll
dblib_2008.dll
libiconv2.dll
dblib.dll
dblib_2008.dll
libiconv.dll

I had to use BOTH the dblib.dll AND the libiconv.dll from Win64 dir...
I had no idea I was building a 64 bit app....

Post by Bo Berglund via Lazarus
constructor TSvnAuditer.Create;
begin
FConn := TMSSQLConnection.Create(nil);
FQuery := TSQLQuery.Create(nil);
FTrans := TSQLTransaction.Create(nil);
FConn.Transaction := FTrans;
FQuery.Transaction := FTrans;
FQuery.DataBase := FConn;

InitialiseDBLib('dblib.dll'); <== Had to add this

Post by Bo Berglund via Lazarus
end;

And this required adding dblib to the uses clause.

This last part was from a discussion I googled:
http://www.pilotlogic.com/sitejoom/index.php/forum/forums-general/3849-cannot-open-tmssqlconnection-component
which initially seemed to be about "CodeTyphon", whatever that is.

But with these items in place I got a connection and could execute a
query.

[Ondrej Pokorny via Lazarus]

Post by Bo Berglund via Lazarus

Post by Bo Berglund via Lazarus
constructor TSvnAuditer.Create;
begin
FConn := TMSSQLConnection.Create(nil);
FQuery := TSQLQuery.Create(nil);
FTrans := TSQLTransaction.Create(nil);
FConn.Transaction := FTrans;
FQuery.Transaction := FTrans;
FQuery.DataBase := FConn;

InitialiseDBLib('dblib.dll'); <== Had to add this

Post by Bo Berglund via Lazarus
end;

And this required adding dblib to the uses clause.

Strange, I connect to MSSQL without InitialiseDBLib in both 64bit and
32bit applications. I only add dblib.dll and libiconv.dll to the exe.

Ondrej
--

[Bo Berglund via Lazarus]

On Wed, 27 Dec 2017 20:52:00 +0100, Ondrej Pokorny via Lazarus

Post by Ondrej Pokorny via Lazarus
Strange, I connect to MSSQL without InitialiseDBLib in both 64bit and
32bit applications. I only add dblib.dll and libiconv.dll to the exe.

You are right. I commented out the initialization line and it still
works.
So I must have changed things in a different order, but I don't know
how it happened.
In any case I don't get exceptions anymore, but I have not verified
data retrieval either. I just ran a stored procedure via FQuery.Open
of an EXEC string.

As a side thing, I was not aware that Lazarus was building 64 bit
applications as default. That is why the 32 bit dll:s would not work I
assume.

How do I set the project for a Win32 target?
I found one place in the project options (Compiler/Config and Target),
but when I change it from "Default" to Win32 there is a warning saying
this:
"The current FPC has no config file. It will probably miss
some units. Check your installation of fpc."

But I have done nothing else than installing from the Lazarus 1.8
installer (lazarus-1.8.0-fpc-3.0.4-win64.exe), which also gets me fpc
3.0.4. So I don't know how to "check my installation".

On Linux I always build from sources and so I know for sure where
.fpc.cnf is located, not so on Windows....

[Ondrej Pokorny via Lazarus]

Post by Bo Berglund via Lazarus
As a side thing, I was not aware that Lazarus was building 64 bit
applications as default. That is why the 32 bit dll:s would not work I
assume.
How do I set the project for a Win32 target?

The easiest is to use 32bit FPC.

Post by Bo Berglund via Lazarus
But I have done nothing else than installing from the Lazarus 1.8
installer (lazarus-1.8.0-fpc-3.0.4-win64.exe

You installed the 64bit version. Install the 32bit. Otherwise you have
to setup a cross compiler (I never did that so I may be wrong):
http://wiki.lazarus.freepascal.org/Cross_compiling

Ondrej
--

[Graeme Geldenhuys via Lazarus]

Post by Bo Berglund via Lazarus
But I have done nothing else than installing from the Lazarus 1.8
installer (lazarus-1.8.0-fpc-3.0.4-win64.exe), which also gets me fpc
3.0.4. So I don't know how to "check my installation".

As Ondrej said... If you look at the name of the installer file, you
installed the Win64 version of Lazarus and FPC. If you wanted the 32-bit
version, download the installer file that mentions win32 in the name. If
you do the latter, you can always later install the official FPC win64
cross-compiler which will then allow you to target both 32-bit and
64-bit Windows.

Regards,
Graeme

[Bo Berglund via Lazarus]

On Thu, 28 Dec 2017 10:13:11 +0000, Graeme Geldenhuys via Lazarus

Post by Graeme Geldenhuys via Lazarus

Post by Bo Berglund via Lazarus
But I have done nothing else than installing from the Lazarus 1.8
installer (lazarus-1.8.0-fpc-3.0.4-win64.exe), which also gets me fpc
3.0.4. So I don't know how to "check my installation".

As Ondrej said... If you look at the name of the installer file, you
installed the Win64 version of Lazarus and FPC. If you wanted the 32-bit
version, download the installer file that mentions win32 in the name. If
you do the latter, you can always later install the official FPC win64
cross-compiler which will then allow you to target both 32-bit and
64-bit Windows.

Strange, I wanted to have another look at the site from where I
downloaded the installer, but now I can no longer find it! My memory
tells me it was using a layout with a sidebar with a "Downloads" link
that brought me to a page on the same site where there was a list of
downloadable exe files. And there was also a bit of text explaining
which file to grab. I thought it was the Lazarus home site...
The actual download link I used from that site was (after examining my
download history, downloaded 2017-12-19):
https://10gbps-io.dl.sourceforge.net/project/lazarus/Lazarus%20Windows%2064%20bits/Lazarus%201.8.0/lazarus-1.8.0-fpc-3.0.4-win64.exe

Now whatever I try search-wise I always end up at SourceForge download
page or other with no extra text as to which one to select. Can't fine
the one I got it from. Have the lazarus sites been redesigned in the
last week?

In any case:
------------
The reason I selected the win64 file was that my earlier experience
with installers like these was that if there were two to selct from
(win32 or win64) it was in order to select which environment the
program would run under (Windows 32 or 64 bit). In this case on my
Windows 7 x64 laptop.
But I am not used to select installer according to capabilities
(except for licensed or unlicesnsed etc). So it never dawned on me
that it would also only build 64 bit applications....

Now that I have the win64 version installed how do I go about making
it possible to build for both 32 and 64 bit environments?

In my Delphi XE5 environment I can do this easily in the project
options where the target can be switched for each configuration.

[Graeme Geldenhuys via Lazarus]

Post by Bo Berglund via Lazarus
But I am not used to select installer according to capabilities
(except for licensed or unlicesnsed etc). So it never dawned on me
that it would also only build 64 bit applications....
Now that I have the win64 version installed how do I go about making
it possible to build for both 32 and 64 bit environments?

In my experience, there are two methods to achieve this.

1) Use the win32 FPC compiler and the win64 FPC cross-compiler. In
Lazarus you set up fpc.exe as your compiler. In the Project's Compiler
Settings, it will pass a -T command line parameter to fpc.exe which
in turn will execute the correct ppc*.exe executable to generate
the appropriate binary for your project.

2) This option doesn't use a cross-compiler. Instead I start off with
64-bit versions of FPC and Lazarus. I normally use FreeBSD for
development, but the same configuration can be applied to Windows or
Linux. I install FPC is a specific directory layout so it supports
multiple targets with one fpc.cfg file. For example:

c:\fpc-3.0.4
\x86_64-win64
\bin
\lib
\share
\i386-win32
\bin
\lib
\share


The fpc.cfg search paths and such with then use the built-in macros to
resolve the correct path location. For example, my FreeBSD system have
lines as follows:


-Fu/data/devel/fpc-$fpcversion/$fpctarget/lib/fpc/$fpcversion/units/$fpctarget


Adapt the fpc.cfg for windows to match your FPC install hierarchy and
appropriate $fpc* macros.

In Lazarus IDE I then use "IDE Options -> Environment -> FPC" to select
between the FPC compilers and related FPC Source directories.

The same can be done (actually even easier) in MSEide because MSEide has
better IDE/Project macro support. So in MSEide I have different keyboard
shortcuts to compile my projects for different targets. I don't have to
toggle any global settings.

Post by Bo Berglund via Lazarus
In my Delphi XE5 environment I can do this easily in the project
options where the target can be switched for each configuration.

Delphi simply hides much of the underlying configuration from the
developer - after all, I hope that is why you pay them so much. Delphi
ships with different compilers (depending on the version you bought
(Starter, Pro, Enterprise, Studio, Architect, add-on packages
installed). Depending on the version, in then enables you to specify new
targets in the Project Options dialog, which in turn uses different
search paths and compilers.



CodeTyphon is a spin-off of Lazarus, which ships with many
cross-compilers all ready for you out of the box. They did some
excellent work in simplifying that process and getting later updates.
This is closer to the experience you get with Delphi. There are some
other stuff I don't like or agree with about that project, but that is a
personal opinion and another matter.


Regards,
Graeme

[Graeme Geldenhuys via Lazarus]

Post by Ondrej Pokorny via Lazarus
Strange, I connect to MSSQL without InitialiseDBLib in both 64bit and
32bit applications. I only add dblib.dll and libiconv.dll to the exe.

Same here.


Regards,
Graeme

--