| Bug #44831 | Lost data in ADODB.Recordset Object | ||
|---|---|---|---|
| Submitted: | 12 May 2009 19:23 | Modified: | 18 May 2009 20:34 |
| Reporter: | Dayvison Pellegrina | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S3 (Non-critical) |
| Version: | 5.1.5 | OS: | Windows (2003) |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
[14 May 2009 8:35]
Pedro Padron
Looks like this is a problem with the RecordSet cursor. When using the Execute method of ADODB.Connection, a Forward-Only cursor is used in the returned RecordSet. If you create your own RecordSet object specifying a different CursorType (like adOpenDynamic), you can retrieve the results from the text field as many times as you want.
In the 5.1 branch, I have only tested this in 5.1.5, where the problem was reproduced. However, the same problem also occurs in the 3.51 branch. These are the ones I could test:
3.51.10 FAIL
3.51.12 OK
3.51.19 FAIL
3.51.27 FAIL
I have searched through the release notes of those versions, but I couldn't find anything relevant to this issue.
Here's how you can reproduce the problem:
CREATE TABLE test (
id INT,
name VARCHAR(255),
description TEXT
);
INSERT INTO test VALUES (1, 'Name', 'Some description');
<%
' ADODB Cursor types
Const adOpenForwardOnly = 0
Const adOpenKeySet = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3
Set Connection = Server.CreateObject("ADODB.Connection")
Connection.Open "Driver=MySQL ODBC 5.1 Driver; ..."
' Creating the RecordSet object
Set RecordSet = Server.CreateObject("ADODB.RecordSet")
' If you want to see the problem disappear, change the CursorType to adOpenDynamic
' RecordSet.CursorType = adOpenDynamic
RecordSet.CursorType = adOpenForwardOnly
RecordSet.Open "SELECT * FROM test", Connection
Response.Write "ID: " & RecordSet("id") & vbCrLf
Response.Write "Name: " & RecordSet("name") & vbCrLf
Response.Write "Description: " & RecordSet("description") & vbCrLf
Response.Write vbCrLf
Response.Write "ID: " & RecordSet("id") & vbCrLf
Response.Write "Name: " & RecordSet("name") & vbCrLf
Response.Write "Description: " & RecordSet("description") & vbCrLf
%>
Expected result:
ID: 1
Name: Name
Description: Some description
ID: 1
Name: Name
Description: Some description
Actual result
ID: 1
Name: Name
Description: Some description
ID: 1
Name: Name
Description:
As you can see, the "Description" field is not retrieved in the second call. (it isn't retrieved in any subsequent calls, actually)
[14 May 2009 8:56]
Pedro Padron
And now it gets even more interesting...
- If you retrieve a TEXT field and try to retrieve it again, it fails.
- If you retrieve a TEXT field, retrieve a BLOB field, and then retrieve the same TEXT field again... It works!
You can use this test case:
CREATE TABLE another_test (
id INT,
name VARCHAR(255)
description TEXT,
image BLOB
);
INSERT INTO another_test VALUES (1, 'Yet Another Name', 'Even more description', 'My Blob');
<%
Set Connection = Server.CreateObject("ADODB.Connection")
Connection.Open "Driver=MySQL ODBC 5.1 Driver; ..."
' Creating the RecordSet object
Set RecordSet = Server.CreateObject("ADODB.RecordSet")
Const adOpenForwardOnly = 0
' This one works with adOpenForwardOnly
RecordSet.CursorType = adOpenForwardOnly
RecordSet.Open "SELECT id, name, description, image FROM another_test LIMIT 1", Connection
Response.Write "ID " & RecordSet("id") & vbCrLf
Response.Write "Name: " & RecordSet("name") & vbCrLf
Response.Write "Description: " & RecordSet("description") & vbCrLf
Response.Write "Image: " & vbCrLf
Response.Write RecordSet("image")
' If you comment out the line above, you will not be able to retrieve
' the contents of the "description" field again
Response.Write vbCrLf & vbCrLf
Response.Write "ID " & RecordSet("id") & vbCrLf
Response.Write "Name: " & RecordSet("name") & vbCrLf
Response.Write "Description: " & RecordSet("description") & vbCrLf
Response.Write "Image: " & vbCrLf
Response.Write RecordSet("image")
%>
I assume that when retrieving the BLOB field, somehow the cursor is redefined or something like that. This way we can get the contents of the TEXT field again.
This can be proved if you try to get the contents of the TEXT field once more. If you don't get the contents of the BLOB again, you won't get the TEXT field.
The motto: You can only get a TEXT field more than once if you get a BLOB field before it.
This problem was reproduced in the same ODBC Connector versions I cited in my previous post.
I don't know exactly, but it looks like this is related to this bug:
http://bugs.mysql.com/bug.php?id=19065
You can see that the bug in that ticket only happens in 3.51.12, the only version where the cursor works as expected.
[14 May 2009 17:44]
Pedro Padron
Well, as I see this bug is duplicated... Bug #16866 Bug #24132 Bug #42385
[18 May 2009 20:34]
Jess Balint
This is a duplicate of bug#42385, please follow status there.
Description: I have a simple ASP application then I use MyODBC Driver 5.1.5 to make an interface with my MySQL database. When i try to put some data in a record set object, it's all right, but when i print a recordset twice, in the second time this data is printing blank :-( This problem occurs only with text data type. Some data about my Issue: - Server: Windows 2003 IIS6 - Database: Linux (RedHat EL5) / Mysql 5.0.67 How to repeat: An Example: <% response.buffer = true Session.LCID = 1046 Response.Charset="ISO-8859-1" Set Conexao = server.createObject("ADODB.Connection") Conexao.Open "driver=MySQL ODBC 5.1 Driver; .......... " SQL = "SELECT field_text FROM Table limit 1" Set RS = conexao.Execute(SQL) response.write "<h2> 1 - " & rs("field_text") & ".</h2>" response.write "<h2> 2 - " & rs("field_text") & ".</h2>" RS.close set RS = nothing conexao.close set conexao = nothing %> The response is: 1 - Chupacabras. 2 - . <===================== IS EMPTY :-( The correct is: 1 - Chupacabras. 2 - Chupacabras. I think it is a BUG. Can you help me ? Suggested fix: PS: I have tried to use another driver like MyODBC 3.51.** and it's function all right. But in my system i was felling MyODBC 5.1.5 is better because it is fastest than 3.51.