Delphi 2010 + MS SQL Server Compact 3.5

Hello,

Can anybody give me an example on how to conect to SQL Server Compact 3.5 from Delphi 2010 in a win32 app?  Preferable ADO but any working method will do...

/richard
soozhCEOAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mahdi78Commented:
0
senadCommented:
Just link the adoconnection to your database (build the connection string) and then just link your tables to use the adoconnection.Process is fairly simple...
0
soozhCEOAuthor Commented:
hello... think link to the tutorial was not working this morning... i can try later... i understand that you need to build a connection string... but with what ole driver?  Whats the format of the string?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

soozhCEOAuthor Commented:
ok now i have looked at the turorial and i can see that it is not appropriate because a) it is for .Net platform, and b) it does not mention Compact 3.5

I appreciate the replies to my question but sometimes I get the feeling that the experts dont really know themselves and are just putting out comments/links in the hope they will pick up points.

Read the question ADO to SQL Server Compact under Win32.

Thanks.
0
senadCommented:
SQL Server Compact is embedded database but I think it still uses OLE DB provider for SQL Server to build the connection.Did you try ?
0
senadCommented:
0
senadCommented:
any news on this ?
0
soozhCEOAuthor Commented:
i have put this project to one side for a couple of weeks...  but the question should stay open.
0
soozhCEOAuthor Commented:
I am still working on this - please keep the question open...
0
CatSpokeCommented:
soozh, I am also interested in this and also not having much luck (I get an "unspecified error"). Maybe we can help each other. This is what I tried:

I made a little application using the DBGo components (I use the DBGo components to connect to SQL server non-compact all the time):

Added a TADOConnection and a TADOQuery component to a form with a button and a memo

Connection string I am using is:
Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=c:\SDI_test\dwarfdata_dw.sdf;
(all other connection properties are default but I turned off the default prompt)

in Object inspector: ADOQuery1 uses ADOConnection1 for its Connection property

The code is:

unit sdi_test_u;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, DB, ADODB;

type
  TForm1 = class(TForm)
    ADOConnection1: TADOConnection;
    ADOQuery1: TADOQuery;
    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.Connected := true;
  ADOQuery1.Open;
  while NOT(ADOQuery1.EOF) do
  begin
    Memo1.Lines.Add(ADOQuery1.FieldByName('DW_at_name').AsString);
    ADOQuery1.Next;
  end;
  ADOQuery1.Close;
end;

end.
0
soozhCEOAuthor Commented:
Hello Catspoke!

I have been on holiday the last few weeks and just returned.  I am now working on this problem again.

Have you had any luck?

0
senadCommented:
CatSpoke
First thing :
Remove the onbutton click to trigger connection and do the connected true in object inspector at design time.
Then test the connection.
When you set the connection to true,do you get any errors?
0
senadCommented:
also try use something like this .... (using table - query)

with ADOQuery1 do begin
     AdoQuery1.SQL.Clear;
     ADOTable1.first;

    While ADOTable1.EOF = false do begin
AdoQuery1.SQL.Text:=' ....'
ADOTable1.Next;
  ADOQuery1.ExecSQL;
 
0
CatSpokeCommented:
Soozh, I forgot about this. Sorry. But I got it working for me, but I have to set the connection string at run time. (never at design time, design time just does not work with DBGO. I don't know why).
So my FormCreate is like this:

procedure TForm1.FormCreate(Sender: TObject);
begin
  ADOConnection1.ConnectionString := 'Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=C:\\dwarfdata_dw_Keep.sdf;';
  try
    ADOConnection1.Connected := true;
    ADODataset1.Active := true;
  finally
 end;
end;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
senadCommented:
funny usage of 'try' and 'finally' (whatever for in this case...) .... :-)
0
soozhCEOAuthor Commented:
i have tried again but i think i am missing the SQLSERVER.CE.OLEDB.3.5 driver.  Any idea where this can be download from?
0
CatSpokeCommented:
I can't remember if this actually added anything that wasn't already there on my particular PC but you can try this (I bet you already have though):

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=e497988a-c93a-404c-b161-3a0b323dce24
0
soozhCEOAuthor Commented:
Just want to check... your app is Win32?  I think i am missing the correct oledb driver...
0
CatSpokeCommented:
Yep, WIN32 app made with Delphi 2010 using DBGO database components. Design time does not work, run timke does.
0
soozhCEOAuthor Commented:
hej...
 
I have got it to work... but only reading data from the database.  My app refuses to insert data.  Is this a problem with the cursor type?
0
CatSpokeCommented:
INSERTing works for me:
MY example SQL is as follows:
INSERT INTO DW_TAG_VARIABLE (DW_PARENT,DW_ADDR,DW_AT_NAME,DW_AT_TYPE) VALUES (0,2,'HELLO',0)

CursorType=ctKeySet
CursorLocation=clUseClient

Button2 handler does the insert

procedure TForm1.Button1Click(Sender: TObject);
begin
  ADOConnection1.ConnectionString := 'Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=C:\\dwarfdata_dw_Keep.sdf;';
  ADOConnection1.Connected := true;
  ADOQuery1.Open;
  while NOT(ADOQuery1.EOF) do
  begin
    Memo1.Lines.Add(ADOQuery1.FieldByName('DW_at_name').AsString);
    ADOQuery1.Next;
  end;
  ADOQuery1.Close;
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
  ADOConnection1.ConnectionString := 'Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=C:\\dwarfdata_dw_Keep.sdf;';
  ADOConnection1.Connected := true;
  ADOQuery2.ExecSQL;
end;

0
soozhCEOAuthor Commented:
actually insert statements work for me, but i tried using dataaware components and cound not get the dataset into insert mode.

Have you had any luck there?
0
CatSpokeCommented:
Sorry. can't help you there. I haven't tried doing anything with data aware components. For my particular data acess requirements I just use queries with regular components and manage all that myself in code. (Note: Doing it that way seems to help with record locking issues with regular SQL servers, if I just store up all the INSERTS, DELETES and UPDATES in a string list and send them in a burst when a user hits a save button vs setting up transactions and doing commits and rollbacks.)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.