Getting Delphi 7 to play with SQL Server Compact 3.5

Question

We have an old application that was written in Delphi 7. It is currently connected to an old Oracle Lite database that is being retired. The powers that be have chosen to move the data to a Microsoft SQL Server Compact database instead. After sepending a good amount of time moving everything over to the SQL CE database, I am now tasked with getting the Delphi application to play nice with the new databases.

The people who are supposed to be smarter than I am (my boss), tell me that I should be able to simply modify the connection and everything should be back in order. However, I have been banging my head against my monitor for two days trying to get the ADO connection in the Delphi application to work with our new SQL CE database.

A slightly simplified example of what I'm working with:

The connection is made in a global object with a TADOConnection named "adoConn":

procedure TGlobal.DataModuleCreate(Sender: TObject);
begin
    adoConn.ConnectionString := 'Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=path\db.sdf;';
    adoConn.Connected := True;
end;

Shortly after this, a procedure is called to populate some messages. In an effort to trouble shoot the application, I've simplified the code to make a simple query and show the results in a message box. The procedure receives a parameter for the SQL string, but I'm ignoring it for now and manually inserting a simple select statement:

procedure Select(const SQL: string);
var
    adoQuery : TADOQuery;
begin
    adoQuery := TADOQuery.Create(nil);
    try
        adoQuery.Connection := Global.adoConn;
        adoQuery.SQL.Text := 'select * from CLT_MESSAGES';
        adoQuery.ExecSQL;
        While not adoQuery.Eof do
        begin
            // Here I just created a MessageDlg to output a couple of fields.
            adoQuery.Next;
        end;
    finally
        adoQuery.Free;
    end;
end;

Everything compiles just fine, but when I run the application I get the following error:

"Multiple-step operation generated errors. Check each status value."

I've done some additional trouble-shooting and found that the error is happening at adoQuery.ExecSQL. I've tried several different versions of the connection string and a couple different ways of trying to query the data, but it all ends up the same. I either can't connect to the database or I get that stupid "Mutliple-step" error.

I appreciate, in advance, any assistance that can be offered.

score 5 · Accepted Answer · edited May 23 '17 at 12:05

5

Don't use ExecSQL for queries that return recordsets.

Set either the AdoQuery.Active property to True or use AdoQuery.Open to execute a SELECT statement.

UPDATE

After changing your code we see the real error which is DB_E_OBJECTOPEN.

UPDATE2

After digging deeper it seems that this is a known bug in the OLE DB provider and nvarchar fields bigger than 127 characters.

these references seem to confirm this:

SO: SQL Server Compact Edition 3.5 gives "Multiple-step operation generated errors" error for simple query

ref1: http://www.tech-archive.net/Archive/SQL-Server/microsoft.public.sqlserver.ce/2008-07/msg00019.html

ref2: https://forums.embarcadero.com/thread.jspa?messageID=474517

ref3: http://social.msdn.microsoft.com/Forums/en-US/sqlce/thread/48815888-d4ee-42dd-b712-2168639e973c

edited May 23 '17 at 12:05

Community

1
1

answered Jan 08 '13 at 19:26

whosrdaddy

11,720
4
50
99

Thanks for the advice. Replacing `adoQuery.ExecSQL` with `adoQuery.Open` got rid of the "Multiple-step" error but replaced it with a new error that reads: "Object was open" `adoQuery.Active := True` gives the same result. – SteveO Jan 08 '13 at 19:48
After replacing the `adoQuery.ExecSQL` with your suggested replacements, my sample code gives the "Object was open" error. In an effort to simplify things and try to dig a little deeper, I created a new project that creates a TADOQuery object, sets the ConnectionString, sets the SQL text, then opens the TADOQuery object (instead of using the TADOConnection object). The results are the same, when the application tries to open the TADOQuery object, it returns the "Object was open" error. – SteveO Jan 08 '13 at 20:13
1

That was it. The table had a column that was set to 500 characters. After reducing the column to 127 characters I was able to run the code with no errors. Adjusting the column to 128 characters caused the error to return. I really appreciate you finding this for me. Unfortunately, I'm going go have to go back to the drawing board because a limitation like this is not acceptable. I just want to go on the record and state that it is bad enough that the limitation is there, but it is also pretty pathetic that the error message was completely misleading. Thanks again for your help. – SteveO Jan 08 '13 at 23:52
@SteveO: Glad I could help. Just ditch SQL CE, there are plenty of good alternatives out there (firebird, sql server express, mysql,..) – whosrdaddy Jan 09 '13 at 06:08

score 2 · Answer 2 · answered Jun 03 '13 at 14:44

2

Changing the cursor type to server side solved the 127 char issue for me :)

answered Jun 03 '13 at 14:44

TinyRacoon

4,655
2
23
22

Getting Delphi 7 to play with SQL Server Compact 3.5

2 Answers2

Linked