Skip site navigation (1) Skip section navigation (2)

Re: Adding long text to a text field generates an error

From: Ben Ramsey <br(at)benandliz(dot)com>
To: pgsql-odbc(at)postgresql(dot)org
Cc: Jeff Eckermann <jeff_eckermann(at)yahoo(dot)com>
Subject: Re: Adding long text to a text field generates an error
Date: 2004-01-21 17:19:58
Message-ID: 400EB4BE.50109@benandliz.com (view raw or flat)
Thread:
Lists: pgsql-odbc
It seems that I /was/ a bit to optimistic, indeed.

Here's the problem I'm having now, and I didn't know this was a problem 
until I heard it from the client.  The problem is that I can *update* 
records just fine after setting the Max LongVarChar above 8190, but I 
cannot *add* new records.  I get this error instead:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'
can't alloc Desc Handle yet
/cora/coraModules/news/newsUpdate.asp, line 1054

When I set the value back to 8190, I can add new records and update 
records just fine (as long as I don't go over that value).

Here's the code I'm using to add new records (keep in mind that this is 
ASP using ADO to update the database--do not me to switch it to a SQL 
statement, it still will not work, as I have already tried):


Set DBConn = Server.CreateObject("ADODB.Connection")
DBConn.Open "DSN=DatasourceName;UID=username;PWD=password;"

Set NewsRS = Server.CreateObject("ADODB.Recordset")
NewsRS.CursorLocation = adUseServer
NewsRS.CursorType = adOpenKeyset
NewsRS.LockType = adLockOptimistic
NewsRS.Open "tblNews", DBConn, , , adCmdTable
NewsRS.AddNew

NewsRS("DateAdd") = Now
NewsRS("Keywords") = Keywords
NewsRS("DateModified") = Now
If (StrComp(ArtType,"1") = 0) Then
     NewsRS("News") = 1
Else
     NewsRS("News") = 0
End If
If (StrComp(ArtType,"2") = 0) Then
     NewsRS("Topic") = 1
Else
     NewsRS("Topic") = 0
End If
If (StrComp(ArtType,"3") = 0) Then
     NewsRS("Review") = 1
Else
     NewsRS("Review") = 0
End If
NewsRS("ReleaseDate") = ValidDate
NewsRS("Title") = ArtTitle
If ArtPDF Then
     NewsRS("PDFLink") = 1
Else
     NewsRS("PDFLink") = 0
End If
NewsRS("PDFURL") = ArtPDFFile
If (StrComp(ArtCopyType, "HTML", vbTextCompare) = 0) Then
     NewsRS("FormatHTML") = 1
Else
     NewsRS("FormatHTML") = 0
End If
If (StrComp(ArtCopyType, "Text", vbTextCompare) = 0) Then
     NewsRS("FormatText") = 1
Else
     NewsRS("FormatText") = 0
End If
If ArtPressRelease Then
     NewsRS("PressRelease") = 1
Else
     NewsRS("PressRelease") = 0
End If
If ArtCalendar Then
     NewsRS("Calendar") = 1
Else
     NewsRS("Calendar") = 0
End If
If ArtHome Then
     NewsRS("HomePage") = 1
Else
     NewsRS("HomePage") = 0
End If
If ArtRevised Then
     NewsRS("Revised") = 1
     NewsRS("RevisedOn") = ValidRevisedDate
Else
     NewsRS("Revised") = 0
End If
NewsRS("ArticleCopy") = ArtCopy

NewsRS.Update

NewsRS.Close
Set NewsRS = Nothing
DBConn.Close
Set DBConn = Nothing


Line 1054 is NewsRS.Update.  ArticleCopy is the "text" column in the 
database.


Here is the SQL code I've dumped from PostgreSQL for "tblnews":

CREATE TABLE tblnews (
     id serial NOT NULL,
     keywords character varying(255),
     dateadd timestamp without time zone,
     datemodified timestamp without time zone,
     news boolean DEFAULT 'f',
     topic boolean DEFAULT 'f',
     review boolean DEFAULT 'f',
     releasedate timestamp without time zone,
     revised boolean DEFAULT 'f',
     revisedon timestamp without time zone,
     title character varying(255),
     pdflink boolean DEFAULT 'f',
     pdfurl character varying(255),
     pressrelease boolean DEFAULT 'f',
     calendar boolean DEFAULT 'f',
     homepage boolean DEFAULT 'f',
     enteredby character varying(50),
     formattext boolean DEFAULT 'f',
     formathtml boolean DEFAULT 'f',
     articlecopy text
);


Here are the settings I have for my DSN (ODBC settings):

Disable Genetic Optimizer (checked)
KSQO (Keyset Query Optimization) (checked)
Recognize Unique Indexes (checked)
Use Declare/Fetch (unchecked)
CommLog (unchecked)
Parse Statements (unchecked)
Cancel as FreeStmt (unchecked)
MyLog (unchecked)
Unknown Sizes = Maximum
Text As LongVarChar (checked)
Unknowns As LongVarChar (unchecked)
Bools As Char (checked)
Max Varchar = 254
Max LongVarChar = 163800
Cache size = 100
SysTable Prefixes = dd_;
Read Only (unchecked)
Show System Tables (unchecked)
LF <-> CR/LF conversion (checked)
Updateable Cursors (checked)
bytea As LO (unchecked)
Row Versioning (unchecked)
Disallow Premature (unchecked)
True is -1 (unchecked)
Server side prepare (unchecked)
Int 8 As = default
Protocol = 7.X,6.4+
OID Options: Show Column (unchecked)
OID Options: Fake Index (grayed out; unchecked and unable to check)
Connect settings (empty)


The client is very antsy about this and wants it fixed ASAP.  Any ideas?
-Ben



Ben Ramsey wrote:
> Jeff Eckermann wrote:
> 
>> We haven't established that this works, yet ;-)
> 
> 
> Guess I was a bit too optimistic. ;)
> 
> Well, I tried unchecking it, then I tried setting the value higher. 
> Nothing seemed to work.  Then, I got the nifty idea to restart IIS, and 
> it work!  It appears that unchecking it doesn't do anything.  So, I had 
> to just set it to an extremely high value.  That did the job.
> 
> Thanks!
> 
> -Ben

In response to

Responses

pgsql-odbc by date

Next:From: Jeff EckermannDate: 2004-01-21 20:30:46
Subject: Re: Adding long text to a text field generates an error
Previous:From: anthonyDate: 2004-01-21 14:40:54
Subject: Re: Can't compile psqlODBC on Windows XP using Visual Studio .NET

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group