为什么根据检索顺序从ADO Recordset取代正确的值？ （DB表具有NTEXT值） [英] Why are null values fetched from ADO Recordset instead of correct ones depending on the order of retrieval? (The DB table has NTEXT values)

问题描述

这是一个DB表，其中包含两个可以使用VBScript从ASP页面读取的日期时间代码我写：

 设置cmd = Server.CreateObject（ADODB.Command）
使用cmd 
 .ActiveConnection = conn 
 .CommandType = adCmdText 
 .Prepared = True 
 .CommandText =SELECT * FROM storico_corsi WHERE stc_id = 5
设置rs = .Execute 
 Response.Write（stc_scadenza = {& rs（stc_scadenza）&}，）
 Response.Write（stc_inizio = {& rs（stc_inizio）& ）
如果IsEmpty（rs（stc_inizio））然后
 Response.Write（ -  ERROR！）
结束如果
结束
  

此代码提供以下输出：

  stc_scadenza = {19/04/2014}，stc_inizio = {}  - 错误！ 
  

如果我将检索顺序交换到

  ... 
 Response.Write（stc_inizio = {& rs（stc_inizio）&}，）
 Response.Write stc_scadenza = {& rs（stc_scadenza）&}）
 ... 
  

这是我得到的（正确的）结果：

  stc_inizio = {19/02/2014}，stc_scadenza = {19/04/2014} 
  

为什么元素检索顺序有一个小的变化一个ADO记录集提供完全不同的结果？

请注意，我使用意大利语区域（ dd / mm / yyyy ）， stc_inizio 和 stc_scadenza ，而通常设置为午夜时间戳，是SQL类型 datetime

---

更新＃1 ：我通过将代码减少到更简单，更清晰操纵只有两个字段，添加空虚检查并完全删除JSON的东西。下面的一些评论参考以前的更复杂的版本。

---

更新＃2 ：如果我将SQL查询替换为

  SELECT stc_inizio，stc_scadenza FROM storico_corsi WHERE stc_id = 5 
  

或

  SELECT stc_scadenza，stc_inizio FROM storico_corsi WHERE stc_id = 5 
  

它正常工作！但为什么？这是我使用的数据库表：

  CREATE TABLE [dbo]。[storico_corsi]（
 [stc_id ] [bigint] IDENTITY（1,1）NOT NULL，
 [stc_id_ute] [bigint] NOT NULL，
 [stc_utente] [varchar]（100）NULL，
 [stc_anagrafica] [ntext ] NULL，
 [stc_id_can] [bigint] NULL，
 [stc_canale] [varchar]（500）NULL，
 [stc_FE_id] [bigint] NULL，
 [stc_quest_finale] ntext] NULL，
 [stc_quest_corretto] [ntext] NULL，
 [stc_reg_fad] [ntext] NULL，
 [stc_inizio] [datetime] NULL，
 [stc_scadenza] [datetime] NULL，
 [stc_terminato] [char]（1）NULL 
 CONSTRAINT [DF_storico_corsi_stc_terminato_1] DEFAULT（'N'），
 [stc_fine] [datetime] NULL，
 [stc_tempo] [bigint] NULL 
 CONSTRAINT [DF_storico_corsi_stc_tempo] DEFAULT（（0）），
 [stc_data_in] [datetime] NULL 
 CONSTRAINT [DF_storico_corsi_stc_data_in_1] DEFAULT（getdate（）），
 [ stc_progressivo] [ int] NULL，
 [stc_anno] [int] NULL，
 CONSTRAINT [PK_storico_corsi] PRIMARY KEY CLUSTERED（[stc_id] ASC）
 WITH（
 PAD_INDEX = OFF，
 STATISTICS_NORECOMPUTE = OFF，
 IGNORE_DUP_KEY = OFF，
 ALLOW_ROW_LOCKS = ON，
 ALLOW_PAGE_LOCKS = ON）ON [PRIMARY] 
）
 ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 
  

---

更新＃3 ：只要我在查询中包含3个 列中的任意一个，就可以复制该问题。如果我不 SELECT 他们，没有什么不好。显然， ntext 的价值观打破了记录，但是他们也以不可预测的方式去做，这取决于其他领域的检索顺序。

---

更新＃4 ：最后需要使用ADO获取BLOB / n em 。这个声明听起来有点疯狂，但这是我在这里发现的： http://p2p.wrox.com/sql-server-2000/3211-cant-pull-data-ntext-field-into-recordset.html#post78234 这与我的经验是一致的。

解决方案

根据我们进一步调查的结果，问题源于您使用 SELECT * ... （但不使用）隐式选择的 NTEXT 列。 ）

如你所说，有一个网络上的偶尔提示，至少在SELECT列表的结尾早期才能检索大型二进制字段的问题当使用ADO。 （一旦你链接到那篇文章，我确实有一个模糊的记忆，从那个昏暗和遥远的过去，我也看过这个建议。）

我会怀疑你可能会使用旧版本的ADO，而且这个问题可能已经在以后的版本中得到解决。

鉴于在这种特殊情况下你并不想要要从 NTEXT 列中检索值，您应该将 SELECT 列表限制为您实际需要的值，一切都应该可以正常运行。

请注意，通常认为最佳做法是避免使用 SELECT * ，除了快速，即时查询。明确选择所需的列有几个好处。例如，在这种情况下，即使您的 SELECT 已经完美运行，您仍然可以在 NTEXT中检索大量数据字段（可能在网络中）从数据库中删除而不使用它...

另请注意，在知识库文章317016如何使用GetChunk和AppendChunk读取和写入BLOB，有一些建议使用BLOB与ADO，包括：

• 最后选择BLOB列。选择单个字段，而不是*。

I have a DB table with two datetime nullable columns that I need to read from an ASP page using VBScript.

This is the code I wrote:

Set cmd = Server.CreateObject("ADODB.Command")
With cmd
    .ActiveConnection = conn
    .CommandType = adCmdText
    .Prepared = True
    .CommandText = "SELECT * FROM storico_corsi WHERE stc_id = 5 "
    Set rs = .Execute
    Response.Write("stc_scadenza = {" & rs("stc_scadenza") & "}, ")
    Response.Write("stc_inizio = {" & rs("stc_inizio") & "} ")
    If IsEmpty(rs("stc_inizio")) Then
        Response.Write("- ERROR!")
    End If
End With

This code gives this output:

stc_scadenza = {19/04/2014}, stc_inizio = {} - ERROR!

If I swap the order of retrieval to

...
    Response.Write("stc_inizio = {" & rs("stc_inizio") & "}, ")
    Response.Write("stc_scadenza = {" & rs("stc_scadenza") & "} ")
...

this is the (correct) result I get:

stc_inizio = {19/02/2014}, stc_scadenza = {19/04/2014} 

Why is a small change in the order of retrieval of elements from an ADO Recordset delivering a completely different outcome?

Please notice that I'm using the Italian locale (dd/mm/yyyy) and that stc_inizio and stc_scadenza, while being often set to midnight timestamps, are of SQL type datetime.

---

UPDATE #1: I made the code simpler and clearer by reducing the code to the manipulation of just two fields, adding the emptiness check and removing JSON stuff altogether. Some comments below refer to previous, more complicated, versions.

---

UPDATE #2: If I replace the SQL query with

SELECT stc_inizio, stc_scadenza FROM storico_corsi WHERE stc_id = 5

or

SELECT stc_scadenza, stc_inizio FROM storico_corsi WHERE stc_id = 5

it works properly! But why? This is the DB table I'm using:

CREATE TABLE [dbo].[storico_corsi] (
    [stc_id] [bigint] IDENTITY(1,1) NOT NULL,
    [stc_id_ute] [bigint] NOT NULL,
    [stc_utente] [varchar](100) NULL,
    [stc_anagrafica] [ntext] NULL,
    [stc_id_can] [bigint] NULL,
    [stc_canale] [varchar](500) NULL,
    [stc_FE_id] [bigint] NULL,
    [stc_quest_finale] [ntext] NULL,
    [stc_quest_corretto] [ntext] NULL,
    [stc_reg_fad] [ntext] NULL,
    [stc_inizio] [datetime] NULL,
    [stc_scadenza] [datetime] NULL,
    [stc_terminato] [char](1) NULL
        CONSTRAINT [DF_storico_corsi_stc_terminato_1]  DEFAULT ('N'),
    [stc_fine] [datetime] NULL,
    [stc_tempo] [bigint] NULL 
        CONSTRAINT [DF_storico_corsi_stc_tempo]  DEFAULT ((0)),
    [stc_data_in] [datetime] NULL 
        CONSTRAINT [DF_storico_corsi_stc_data_in_1]  DEFAULT (getdate()),
    [stc_progressivo] [int] NULL,
    [stc_anno] [int] NULL,
CONSTRAINT [PK_storico_corsi] PRIMARY KEY CLUSTERED ([stc_id] ASC) 
WITH (
    PAD_INDEX = OFF, 
    STATISTICS_NORECOMPUTE = OFF, 
    IGNORE_DUP_KEY = OFF, 
    ALLOW_ROW_LOCKS = ON, 
    ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) 
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

---

UPDATE #3: The issue can be reproduced anytime I include any of the 3 ntext columns in the query. If I don't SELECT them, nothing bad happens. Apparently, ntext values break the record, but they also do that in an unpredictable way that depends on the order of retrieval of other fields.

---

UPDATE #4: BLOBs/ntexts have to be fetched last with ADO. This statement sounds a bit crazy but this is what I found here: http://p2p.wrox.com/sql-server-2000/3211-cant-pull-data-ntext-field-into-recordset.html#post78234 It's consistent with my experience.

解决方案

Given the results you're seeing as we've investigated further, it seems as though the problem stems from the NTEXT columns that you're selecting implicitly with your SELECT * ... (but not using.)

As you've noted, there are occasional hints on the web that there at least used to be problems retrieving large binary fields earlier than the end of the SELECT list when using ADO. (As soon as you linked to that article I did have a vague memory from the dim and distant past that I'd seen that advice before too.)

I'd suspect you may be using an older version of ADO, and that this problem may have been fixed in later releases.

Given that in this particular case you don't actually want to retrieve the values from the NTEXT columns, you should simply limit your SELECT list to the values you actually need, and everything should work fine.

Note that it's generally considered best practice to avoid the use of SELECT * except for quick, ad-hoc queries. Explicitly selecting the columns you need has several benefits. For example, in this case, even if your SELECT had worked perfectly, you'd still have been retrieving the potentially large amounts of data in your NTEXT fields (possibly across the network) from your database only to discard it without using it...

Note also that in KB article 317016, "How To Read and Write BLOBs Using GetChunk and AppendChunk", there's some "suggestions for using BLOBs with ADO", including:

• Select the BLOB columns last. Select individual fields, not "*".

这篇关于为什么根据检索顺序从ADO Recordset取代正确的值？ （DB表具有NTEXT值）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！