RE: Problem with LONGTEXT field - deep kapasi - com.mysql.lists.win32

4 messages in com.mysql.lists.win32RE: Problem with LONGTEXT field

From	Sent On	Attachments
deep kapasi	18 Sep 2002 21:42
Yves LUDWIG	19 Sep 2002 05:23
deep kapasi	21 Sep 2002 05:10
Mike Hillyer	21 Sep 2002 09:16

Subject:	RE: Problem with LONGTEXT field
From:	deep kapasi (dbka...@yahoo.co.in)
Date:	09/21/2002 05:10:21 AM
List:	com.mysql.lists.win32

Hi,

Thx for replying,

Well I don't know on which other DB's you tested, but I have tested in MSSQL7 & MsAccess and its working properly with it.

Below is the connection string I used to connect to MSSQL7 server cnn.Open _ "PROVIDER=SQLOLEDB.1;Data Source=localhost;Initial Catalog=test", "sa", "" Insert the above connection string in below code and test it again.

And also if you remove ";OPTION=35"(someone suggested in response to below mail) part from the MySQL Connection string then its working properly, so I don't think its an expected behaviour.

I am using below mentioned query in one of my application which was supporting MSSQL7 & MsAccess as backend and which is already being used by our clients since last 1 years without any problems, the below problem started when we decided to support MySQL as a backend so I changed the connection string accordingly.

Regards, Deep

--- Venu <ve...@mysql.com> wrote: > Hi !

-----Original Message----- From: deep kapasi [mailto:dbka...@yahoo.co.in] Sent: Wednesday, September 18, 2002 9:43 PM To: MySQL; myo...@lists.mysql.com Cc: ve...@mysql.com Subject: Problem with LONGTEXT field

Hi,

I am using MySQL 4.0.3 (max-nt) and MyODBC 3.51.03 and facing strange problem while accessing a LONGTEXT Column, to generate the Bug pls follow the below step

Platform: Window 2k Server Application: Visual Basic 6.0 (No Service Pack)

1. Run following SQL Script

USE Test; CREATE TABLE `testing` ( `ID` int(11) NOT NULL auto_increment, `FLD1` longtext, PRIMARY KEY (`ID`) ) TYPE=InnoDB;

INSERT INTO Testing(FLD1)

VALUES("AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA"),

("BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB");

2. Start a New Visual Basic Project (Standard .exe) 3. Add reference of ADO (any ver 2.0, 2.1 OR 2.5. I have tested with 2.0) 4. Paste the below code in Form_Load() event

Private Sub Form_Load() Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset

cnn.Open "Driver={MySQL ODBC 3.51

Driver};SERVER=localhost;DB=test;UID=root;PWD=;OPTION=35"

Set rst = cnn.Execute(CommandText:="SELECT * FROM Testing", Options:=adCmdText) With rst If IsNull(.Fields("FLD1").Value) Then MsgBox "IT IS NULL" Else MsgBox "IT IS NOT NULL" End If If IsNull(.Fields("FLD1").Value) Then MsgBox "IT IS NULL" Else MsgBox "IT IS NOT NULL" End If End With End Sub

4. Now Run the project. It will display 2 MessageBox, first messagebox will display "IT IS NOT NULL" while second will display "IT IS NULL". Isn't it Strange? How did the value of FLD1 changed to NULL?.

Same problem is not coming if client-side recordset is created i.e. replace following line in above code Set rst = cnn.Execute(CommandText:="SELECT * FROM Testing", Options:=adCmdText) with below 3 lines Set rst.ActiveConnection = cnn rst.CursorLocation = adUseClient rst.Open Source:="SELECT * FROM Testing", Options:=adCmdText

Now run the project again and both time messagebox will display "IT IS NOT NULL" message.

Is it a MySQL Bug OR Am I missing something?

It is expected results; as per the above code snippet. I tested the same piece of code with other Db's and

you get the same results when using server side cursor.

The reason for this is; When the first fetch is made, it gets the complete data; and when you call second check for NotNull without repositiong the resultset cursor; the driver returns SQL_NO_DATA_FOUND as it is already returned the data complete data of the field to the client.

The simple alternative is to reposition your cursor before the second fetch using rst.movefirst or relevent one.

Regards, Venu

-- For technical support contracts, go to https://order.mysql.com __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Mr. Venu <ve...@mysql.com> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Developer /_/ /_/\_, /___/\___\_\___/ California, USA <___/ www.mysql.com

---------------------------------------------------------------------

Please check

"http://www.mysql.com/Manual_chapter/manual_toc.html"

before posting. To request this thread, e-mail myod...@lists.mysql.com

To unsubscribe, send a message to the address shown in the List-Unsubscribe header of this message. If you cannot see it, e-mail myod...@lists.mysql.com instead.