SQL Server Compact Edition DB file - DB connection in Delphi

Database
1

Hi everyone!

I am trying to use a small .sdf database file created in SQL Server Compact Edition version 4.0 on Delphi application but I cannot make a connection (ADO Connection or via UniDAC connection) to this database. I have Windows 10 64bit OS, Delphi 10.2

I found tutorial for connection to .sdf file created on 3.5 version on this link: http://slamingcode.blogspot.com/…/connecting-delphi…

It probably works on .sdf files created on 3.5 version but when I try to connect “my file”, I got error saying that database is created on different version and that I need to install specific DB provider.

So, I installed SQL Server Compact Edition v4.0, but it is only available for 64-bit system. After that, I manage to open and view database through LinqPad application (proof that DB provider was installed and that DB file is not coruupted and that DB is created in version 4 of SSCE). But, in Delphi, there was no DB provider for SSCE v4.0 on ADO connection in the list.

Based on tutorial for setup 3.5 version in the link above, I found proper (I think) registry key for v4.0 (HKEY_CLASSES_ROOT\WOW6432Node\CLSID{2006C53A-C915-41EA-BAA9-9EAB3A1FBF97}) and added Key “OLE DB Provider” with value “Microsoft SQL Server Compact OLE DB Provider”.

Now, I see DB provider in list of providers when I try to make Connection String. But, when I try to use that provider, I got Microsoft Data Link Error: “Provider is no longer available. Ensure that the provider is installed properly”. When I try to connect to this file via UniDAC TUniConnection, SQL Server provider with parameter for Compact i get error “Unspecific error”

I tried to reinstall all programs, including OS, Delphi and all other apps but It is always the same error.

Has anyone in this community tried and succeeded in making a connection from Delphi to a .sdf database file? Would it be too much to ask someone to try to make a connection to an empty database created in SSCE v4.0 (I can send this .sdf file), to try to come to a conclusion whether the problem is only in my environment or in Delphi ?

Thanks in advance!

2

Are you compiling a 64bit application?

3

Thank you very much for your question!

I didn’t get to compile time. So, I open a new project (VLC Form Application) and put ADOConnection or SCAD MSCompactConnection on the main form and try to connect to the sdf file and immediately get “Unspecific error”.

4

Usually in such cases I use a virtual machine for a similar test on other operating systems. If the error persists, the problem is general, most likely.
Hi all.

5

Thanks for the message. I will try to install another OS. I’ll let you know if I’ve been able to resolve the issues

6

Did you download Microsoft Corporation SSCERuntime_x86-ENU.exe

I think you are trying to find an ADO/ODBC driver inside the Delphi IDE which is 32bit and you’ve only installed the 64bit odbc driver.

7

This could lead to problem detection. Yes, I downloaded both x86 and x64 installations, but for v4.0 SSCE it is not possible to install x86 SSCERuntime on 64bit OS.
And for SSCE version 3.5 SSCE it is possible to install both x86 and x64 runtimes.

8

Are you happy to only have data access at runtime? If so i’ll send you a solution

9

That would be great!

10

This should allow you to generate the connection string you need.
Run it as a 64bit application.

uses
Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.StdCtrls, Data.DB,
Data.Win.ADODB;

type
TForm1 = class(TForm)
ADOConnection1: TADOConnection;
Button1: TButton;
Memo1: TMemo;
procedure Button1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;

var
Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
begin
ADOConnection1.ConnectionString := PromptDatasource(application.Handle,ADOConnection1.ConnectionString);
Memo1.lines.text := ADOConnection1.ConnectionString;
end;

end.

11

Thank you very much for these messages!

Unfortunately, this doesn’t work for me. When I start this program (64bit) and click the button, I get the wizard for creating the connection. When I choose the db provider for MSCE, I get the same message as when I try to make a connection in buildtime - “Provider is no longer available. Ensure that the provider is installed properly”.

If it works for you, then I know something is wrong with my settings or program installations…

There is probably some catch for the SSCE v4.0 DB provider to work properly on Delphi. I assume this because even the runtime installation for version 3.5 of the SSCE DB provider does not work on delphi (ADO Connection) until certain changes are made to the registry and the instructions for these changes are listed in the link in my post. But there are no instructions anywhere to customize the SSCE v4.0 installation to work properly in Delphi. I tried to make changes similar to what was done for v3.5 but apparently I failed.

12

Are you able to connect from, say, Visual Studio?

13

Hi Eivind!
I didn’t try to connect from Visual Studio but I tried to connect from LinqPad app and I done that successfuly. LinqPad app also use standard DB provider for SSCE v4.0 from Microsoft. That’s why I think that DB provider is OK but there is some issue with Delphi and SSCE DB provider.

Also, I tried to connect using Devart DAC components (UNIDac or SDAC). I created ticket on Devart support page. They answered me and told me that they have reproduced the issue and will investigate its origin.