How to use TMSSQLConnection for MS SQL? lazarus / 16-Databases ---------------------------------------------------------------------- Nebula · 2012-09-20 18:31 I'm wondering what to 'uses' to be able to use SQL Server. According to http://wiki.lazarus.freepascal.org/SqlDBHowto TMSSQLConnection is available, but in my Lazarus v1.0 I can't find it. I can compile with the MySQL v5 conn type, but not with the MSSQLconn type which is undefined. If I use menu option View..Components.. I can see TMySQL40Connection and three others (4.1,5.0,5.1), TIBConnection, TODBCConnection, TOracleConnection etc - but not the MS one. Is it still in beta, and hasn't made it into my Win32 Lazarus v1.0 / FPC 2.6? Would I have to meddle about manually installing something, or should I give up for now and stick with ODBC? Thanks ---------------------------------------------------------------------- paweld · 2012-09-20 21:41 Quote SQLDB     Added mssql connectors (supports Microsoft SQL Server), requires fpc 2.6.1+     Added sybase connectors (supports Sybase ASE), requires fpc 2.6.1+     Added mysql 5.5 connector, requires fpc 2.6.1+ For fpc 2.6.0: ODBC or ZeosLib ---------------------------------------------------------------------- BigChimp · 2012-09-21 06:53 The Lazarus 1.0 release notes even say it: Laz 1.0 is released with FPC 2.6.0. You need at least a recent FPC fixes/2.6.1 from SVN or FPC trunk (2.7.1) ---------------------------------------------------------------------- Nebula · 2012-09-21 08:41 Ah, thank you. Can I easily grab something from wherever, copy it into the components folder and have it picked up automatically, or would it be far more involved than that? ---------------------------------------------------------------------- BigChimp · 2012-09-21 08:56 No, you'd need support for MSSQL at the FPC level first. Have a look here if you want to try that: http://lazarus.freepascal.org/index.php/topic,15135.msg90598.html#msg9 0598 ... recompile FPC (or the sqldb units at least) You'd then have to convince Lazarus to build the visual connectors for FPC 2.6.0, in components\sqldb\registersqldb.pas: Code: [Select]{$IF FPC_FULLVERSION>= 20601} {$IF DEFINED(BEOS) OR DEFINED(HAIKU) OR DEFINED(LINUX) OR DEFINED(FREEBSD) OR DEFINED (NETBSD) OR DEFINED(OPENBSD) OR DEFINED(WIN32) OR DEFINED(WIN64)} // MS SQL Server and Sybase ASE connectors were introduced in the FPC 2.7 development branch, and // backported to 2.6.1 // Operating systems should match FPC packages\fcl-db\fpmake.pp {$DEFINE HASMSSQLCONNECTION} {$DEFINE HASSYBASECONNECTION} {$ENDIF} {$ENDIF} ... change {$IF FPC_FULLVERSION>= 20601} to {$IF FPC_FULLVERSION>= 20600} Perhaps rescan fpc source files (don't know if that is necessary)... ... and recompile Lazarus IIRC, it can be done but it is a bit of fiddling. Edit: to make it brutally clear: the below is another option (and much easier) If you would like to have this connector, I would rather suggest using a snapshot of the newest Laz + FPC 2.6.1, as it has additional fixes. You could set it up separately from your Laz 1.0/FPC 2.6.0 using --primary-config-path: http://wiki.lazarus.freepascal.org/Multiple_Lazarus#Solution_for_separ ating_instances_using_multiple_config_dirs This way, you can develop with a stable FPC, and a fairly stable Laz environment that has a lot of bugs fixed. If something goes wrong, or you need to test if something works on 1.0, you can switch back to that version. Edit: added a note here http://wiki.lazarus.freepascal.org/Lazarus_Dat abase_Overview#Lazarus_and_MSSQL.2FSybase, hope that helps others in the same situation. ---------------------------------------------------------------------- Nebula · 2012-09-21 11:32 Thanks BC, hope you don't mind me correcting a typo in the wiki  Sounds complicated, most of us Windows users aren't used to recompiling apps - I think I'll wait until the next release! Still, good to know what's involved, and that's useful information for others, too. Cheers ---------------------------------------------------------------------- BigChimp · 2012-09-21 11:39 Hi Nebula, It seems a bit daunting, but doing a dual Lazarus installation as I proposed is not as hard as it looks. And that is much easier than recompiling stuff... but to each his own. Thanks for the wiki edits by the way, very welcome! ---------------------------------------------------------------------- TyneBridges · 2013-02-13 14:00 Could you give us a little more info on how to get the snapshot? I have the same problem and have just uninstalled and reinstalled Lazarus, to find that the latest version has the same issue - it uses a version of FPC that doesn't include MsSQLConnection. When I've tried to download patches for Lazarus, I have been confronted with a login prompt. Casting my memory back years to when I last used anonymous FTP, I seem to remember that the user name is just "anonymous" but I have no idea of the password, and can't find the information on the Lazarus Wiki or in Google searches. Any help would be gratefully received. ---------------------------------------------------------------------- BigChimp · 2013-02-13 14:24 Quick notes : - go to snapshots dir, see link top left, this gives a web page (http://freepascal.dfmk.hu/test/lazarus/) - download a version of Lazarus *that includes an FPC higher than 2.6.0*, e.g. Lazarus + fpc 2.6.1,  win32. For me, clicking that link just shows a download location dialog, not an FTP site - install Edit: hope that's enough, John, if not, please post again... ---------------------------------------------------------------------- TyneBridges · 2013-02-13 19:42 Thanks - I've downloaded a snapshot and will follow the instructions for a parallel installation as soon as I get time. ---------------------------------------------------------------------- TyneBridges · 2013-02-14 19:06 I'm afraid the Wiki entry "Multiple Lazarus" didn't make complete sense to me. I could follow the bit about the command line and created a shortcut with the properties "C:\lazarus\lazarus.exe --primary- config-path=c:\lazconfig". This worked - after I ran Lazarus from the shortcut, a new folder called lazconfig appeared with some config files in it. However, the Wiki then goes on to talk about "the svn version" - I should probably know the acronym but I have to admit I got completely lost in the linked article about the subversion repository. The instruction says there is no need to install Lazarus and that the installer should be run only once. Bearing that in mind, the user who wants to run two or more versions needs a simple way to "unpack" the files from the snapshot version but, since it's an EXE file and not an archive, that looks impossible. I haven't managed to do anything much with Lazarus yet, so I may just remove my original installation and install the snapshot version instead. (Is it likely that an official version with FPC 2.6.1 will be released soon?) ---------------------------------------------------------------------- taazz · 2013-02-14 22:14 There is no need to <> you can't or your shouldn't. The page talks about various installations including installing from svn directly which I avoid at this point. install the 32bits and 64bits in different directories then set the shortcuts of both to use a custom config directory and that's it everything else is not of any concern to you at this point learn first how to work with lazarus before diving in to svn and all the raizor edge installation which personally I avoid because I need a reference point which I can use that will be easy for every one to acquire. ---------------------------------------------------------------------- Nebula · 2013-02-14 22:36 ODBC works fine with MS SQL ---------------------------------------------------------------------- BigChimp · 2013-02-15 07:41 Quote from: JohnGateshead on February 14, 2013, 08:06:27 pmThe instruction says there is no need to install Lazarus and that the installer should be run only once. Bearing that in mind, the user who wants to run two or more versions needs a simple way to "unpack" the files from the snapshot version but, since it's an EXE file and not an archive, that looks impossible. I haven't managed to do anything much with Lazarus yet, so I may just remove my original installation and install the snapshot version instead. (Is it likely that an official version with FPC 2.6.1 will be released soon?) 1. The instructions on the wiki probably use this scenario: a version installed by official stable Lazarus installer b Lazarus compiled from subversion/current source code/svn Then, indeed, you needn't run the installer again. However, if you use a version installed by official stable Lazarus installer b version installed by Lazarus snapshot installer yes, of course, you'd need to run that installer in b) I can see though why you are confused - IMO the installation pages are a bit of a mess mixing various concepts. However, I've given up trying to fix them as people kept putting in their own specific workflows as opposed to clarifying and unifying existing details. 2. I'd say just installing a single version using the current FPC 2.6.1 snapshot installer is a good idea. FPC 2.6.1 is more stable/bug free than 2.6.0 (used by the official installer) and the basis for the soon-to-be-released FPC 2.6.2. When a new official Laz 1.x+FPC2.6.2 is released, you'd be able to just install that over your existing version. ---------------------------------------------------------------------- howardpc · 2013-02-15 20:18 If you're compiling for 32-bit Windows, double-check that the two dlls you refer to are 32-bit (and not 64-bit). Also, try putting them in the same folder as your application executable just for the development/testing phase. Once you have your app running successfully and debugged you can worry about the proper MS-recommended system location for the dlls. ---------------------------------------------------------------------- TyneBridges · 2013-02-15 21:39 Thanks, Howard. I removed my post previous to this when I discovered that the application would run if I put the DLLs in the application folder (i.e. where my application executable is saved) - although it seems counterintuitive to me that Lazarus wouldn't look in the folder where all the other DLLs are. However, I'm pulling my hair out because I've been stuck again for the past couple of hours on what should be a simple point - the specification of the connection parameters. I have a SQL Server Express 2005 database called Test on the same box as my Lazarus executable, to which I can connect using SQL Server Management Studio Express. I've tried every option I can think of in the Lazarus connection parameters but, every time, I get the frustratingly nonspecific error "Adaptive Server is unavailable or does not exist". The same error appears even if I deliberately specify an incorrect password, so I don't know if the problem is with the hostname, the databasename, the password or the authentication method (which doesn't seem to be selectable here). You can see below some of the ones I've tried. AConnection:= TMSSQLConnection.Create(nil); AConnection.HostName:= 'localhost'; //AConnection.DatabaseName:= 'c:\program files (x86)\microsoft sql server\mssql.1\mssql\data\Test.mdf'; AConnection.DatabaseName:= 'Test'; AConnection.UserName:= 'sa'; //AConnection.Password:= 'Incorrect'; AConnection.Password:= 'manager';  I've tried SPRINT11 (the PC name, which is the same as the hostname shown in SQL Server Management Studio) as well as 'localhost' and 'LocalHost'. I've checked in case the instance name is wrong, but all I can see is the hostname (it doesn't seem to be 'SPRINT11/SQLExpress', as I've seen before). The parameters I'm giving correspond to SQL Server authentication, not Windows Authentication. Any ideas, or pointers to example programs other than the SQLDBHowTo, which is based on Firebird? ---------------------------------------------------------------------- Nebula · 2013-02-15 23:12 I've just been playing with a local SQL Server Express (although with VB.net, LOL) and the Hostname needs to be the 'instance' name preceded by .\ i.e. .\SQLEXPRESS ---------------------------------------------------------------------- BigChimp · 2013-02-16 08:00 Well, if you mean the sqldb tutorials, they are based on Firebird+SQlite+PostgreSQL... but yes, they have no MSSQL info. IIRC, the wiki page on ODBC has examples on connecting to MSSQL including the . notation, which Nebula demonstrated. Of course, it doesn't make immediate sense to look there for mssqlconn. IIRC (as well), I did document the required naming for the built-in documentation, but it hasn't been included yet into the PDFs/CHMs, probably because current stable FPC doesn't support mssqlconn yet. Please see the attached raw documentation source, hope it may be of use, and comments welcome. ---------------------------------------------------------------------- TyneBridges · 2013-02-16 16:29 Thanks everyone for trying to help but I really am struggling here. I've followed your advice about the syntax for the hostname and now get a different error: "Server name not found in configuration files. Unknown host machine name." I've tried all of the following as the hostname SPRINT11 (i.e. my machine name, and the hostname that appears - without the initial punctuation - in the logon dialog for SQL Server Management Studio Express) SPRINT11\SQLEXPRESS .\SPRINT11 .\localhost 127.0.0.1 127.0.0.1\SQLEXPRESS .\SQLEXPRESS The host (PC) name or IP address on its own gives the original error "Adaptive Server unavailable or does not exist" but I think all the others have given the "name not found" error. Does this tell me anything useful? The server is definitely up and running because I have connected to it with Management Studio Express. The only information I can find on the "name not found" error suggests that it means the server is running on the wrong port. Microsoft's instruction "How to configure SQL Server to listen on a specific port" doesn't say which OS it's describing and referred me to a registry key that doesn't exist. However, registry searches only seem to contain the port number 1433, which I gather is the default. BigChimp's instruction ("Host and optionally port or instance") suggests that I shouldn't need anything other than the name SPRINT11, as there is no indication on my machine that SQL Server is using anything other than this as its instance name Nebula suggested using ODBC but, if I ever manage to create a working application, the intention is to install it on lots of PCs, so I was aiming for something simple to install...maybe I should try ODBC anyway. ---------------------------------------------------------------------- TyneBridges · 2013-02-16 16:58 I've done some further checking. SQL Server Configuration Manager confirms that the server is running on port 1433, and gives "Host Name" as "SPRINT11" and "Name" as "SQL Server (MSSQLSERVER)". Having read that, I tried the following in turn as the hostname   SPRINT11\MSSQLSERVER .\MSSQLSERVER ...and these don't work either. I must have tried every available option here so I guess something is broken. ---------------------------------------------------------------------- BigChimp · 2013-02-16 17:23 Quote from: JohnGateshead on February 16, 2013, 05:29:59 pmBigChimp's instruction ("Host and optionally port or instance") suggests that I shouldn't need anything other than the name SPRINT11, as there is no indication on my machine that SQL Server is using anything other than this as its instance name No, that depends on how sql server is set up. IIRC, sql server express has dynamic ports (or whatever the microsoft speak is) by default: there's a listener on IIRC udp port 1434 (SQL agent) that tells clients that want to connect to which actual port they need to connect. In olden days (SQL 2K I think, maybe earlier), the server just listened on port 1433. ---------------------------------------------------------------------- TyneBridges · 2013-02-16 17:27 Panic over for now. I've discovered the source of my failure to connect. Thanks to everyone for their help. I work with computers and constantly find myself saying that all the problems come from security and the way it's often implemented in overzealous or undocumented ways. How else can you explain the fact that the TCP/IP protocol is *disabled* by default in SQL Server (I certainly didn't disable it) and the system doesn't warn you of this in any way? Once I had enabled TCP/IP, I immediately got a "Successful connect" with the hostname "SPRINT11" - my original guess. ---------------------------------------------------------------------- BigChimp · 2013-02-16 17:32 Edit: written before John's post above. Noticed I started pontificating a bit - not personal at all, just got carried away a bit Quote from: JohnGateshead on February 16, 2013, 05:58:55 pmI've done some further checking. SQL Server Configuration Manager confirms that the server is running on port 1433, and gives "Host Name" as "SPRINT11" and "Name" as "SQL Server (MSSQLSERVER)". Having read that, I tried the following in turn as the hostname   SPRINT11\MSSQLSERVER .\MSSQLSERVER ...and these don't work either. I must have tried every available option here so I guess something is broken. Try running Code: [Select]telnet 127.0.0.1 1433 to see if SQL server is actually listening and that the firewall lets stuff through (you should just get a black screen if it does, not something like "connection refused") 1433 is *NOT* the default port anymore I remember I used the SQL Server 2008 express SQL Server Configuration manager to get it fixed to 1433; from my notes: SQL Server Configuration Manager config tool/network protocol: left shared memory enabled. Enabled TCP/IP aangezet but only on 127.0.0.1 and port 1433, not dynamic. ---------------------------------------------------------------------- BigChimp · 2013-02-16 17:35 Quote from: JohnGateshead on February 16, 2013, 06:27:01 pmI work with computers and constantly find myself saying that all the problems come from security and the way it's often implemented in overzealous or undocumented ways. I'm sure it's mentioned somewher in SQL Server Books Online though Quote from: JohnGateshead on February 16, 2013, 06:27:01 pmHow else can you explain the fact that the TCP/IP protocol is *disabled* by default in SQL Server (I certainly didn't disable it) and the system doesn't warn you of this in any way? Once I had enabled TCP/IP, I immediately got a "Successful connect" with the hostname "SPRINT11" - my original guess. Yep, or 127.0.0.1 or . may also work. I'm still fairly sure your machine doesn't listen on 1433 though if you haven't changed it explicitly, but that's fina, as long as the SQL Server Browser or Agent server or whatever it was listens on UDP 1434 and tells interested parties where to connect In my case I didn't want that extra service running as I'm sure I'd only be running 1 instance of SQL server on my dev machine anyway ---------------------------------------------------------------------- petejones · 2013-10-11 10:34 Thanks very much for this - I have been in the same position myself and would never have known that was the issue! Cheers Pete