获取Delphi 7来使用SQL Server Compact 3.5 [英] Getting Delphi 7 to play with SQL Server Compact 3.5

问题描述

我们有一个旧的应用程序是用Delphi 7编写的。它目前连接到一个正在退休的旧的Oracle Lite数据库。选择将数据移动到Microsoft SQL Server Compact数据库的权力。在将大量时间转移到SQL CE数据库之后，我现在有责任让Delphi应用程序与新的数据库一起发挥不错。

谁应该比我更聪明（我的老板），告诉我，我应该能够简单地修改连接，一切都应该按顺序。不过，我一直在用我的显示器敲打我两天，试图在Delphi应用程序中获取ADO连接，以使用我们新的SQL CE数据库。

稍微简单的例子我正在使用：

连接是用一个名为adoConn的TADOConnection的全局对象进行的：

  procedure TGlobal.DataModuleCreate（Sender：TObject）; 
 begin 
 adoConn.ConnectionString：='Provider = Microsoft.SQLSERVER.CE.OLEDB.3.5; Data Source = path\db.sdf;'; 
 adoConn.Connected：= True; 
结束
  

不久之后，调用一个过程来填充一些消息。为了麻烦拍摄应用程序，我简化了代码以进行简单的查询，并在一个消息框中显示结果。该过程接收到SQL字符串的参数，但我现在忽略它，并手动插入一个简单的选择语句：

 过程选择（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; 
虽然没有adoQuery.Eof do 
 begin 
 //这里我刚刚创建了一个MessageDlg来输出几个字段。 
 adoQuery.Next; 
结束
 finally 
 adoQuery.Free; 
结束
结束
  

所有的编译都很好，但是当我运行应用程序时，会收到以下错误：

多步骤操作生成错误，检查每个状态值。

我已经做了一些额外的故障排除和发现在adoQuery.ExecSQL发生错误。我尝试了几种不同版本的连接字符串和一些尝试查询数据的不同方法，但是它们都是一样的。我无法连接到数据库，或者我得到那个愚蠢的多步错误。

我很感激，提前提供任何可以提供的帮助。 / p>

解决方案

不要对返回记录集的查询使用 ExecSQL p>

将 AdoQuery.Active 属性设置为 True 或使用 AdoQuery.Open 执行 SELECT 语句。

更新

更改代码后，我们会看到真正的错误，即DB_E_OBJECTOPEN。

更新2

深入挖掘之后，似乎这是一个已知的错误OLE DB提供程序和nvarchar字段大于127个字符。

这些引用似乎证实了这一点：

SO ： SQL Server Compact Edition 3.5给出了多步骤操作生成的错误简单查询的错误

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

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.

解决方案

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

这篇关于获取Delphi 7来使用SQL Server Compact 3.5的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！