Adding long text to a text field generates an error
Ben Ramsey
set up just fine. The database works just fine, and ASP is connecting
to the database through a DSN just fine. The only problem I seem to
have is adding a large amount of text to a text column in the database.
I can enter up to 8,118 characters (including spaces), but when I go
over that amount, I receive the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
Multiple-step OLE DB operation generated errors. Check each OLE DB
status value, if available. No work was done.
This error doesn't mean much to me. I've spent a month researching it
and trying to figure out what it means in our situation, but I have yet
to resolve it. Thus, I'm turning to this list to see if anyone has any
ideas or has tackled this problem before.
I've tried using the appendChunk method in ASP to add chunks to the
field in the database (this worked before for us on a Linux box running
ChiliSoft ASP), but I get another error saying that the method is not
supported. We are using psqlODBC to connect with the DSN--I do not know
if this driver has anything to do with this circumstance.
I am currently using ADO to update the database, but I have used a
standard SQL INSERT statement for testing, and I get the same error and
problem. When I've run an INSERT query against the database itself
(from the command prompt), it won't update the record when I try
inserting more than 8,118 characters into a text column.
I've double-checked to make sure the column is of datatype "text" and
not anything else. It is. So, it should accept unlimited text, right?
Has anyone seen this problem before? I need help ASAP because our
client is refusing to pay until this is resolved.
Thanks,
Ben
Jeff Eckermann
definitions and the sql you are using may be helpful
also.
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend
__________________________________
Do you Yahoo!?
Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
http://hotjobs.sweepstakes.yahoo.com/signingbonus
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
Ben Ramsey
> definitions and the sql you are using may be helpful
> also.
I was ready to post my code and table definition and everything, and
then I opened up the driver settings for the DSN that connects to the
database, clicked on the "Datasource" options button, which launched a
window with all sorts of options. Here's what I found:
"Text as LongVarChar" is checked.
"Max LongVarChar" is set to 8190.
Should I uncheck "Text as LongVarChar"? What will it do if I uncheck
it? If I shouldn't uncheck it, then what should I set "Max LongVarChar"
to in order to allow unlimited amounts of text?
-Ben
Andrew Ayers
> Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
> Multiple-step OLE DB operation generated errors. Check each OLE DB
> status value, if available. No work was done.
>
> This error doesn't mean much to me. I've spent a month researching it
Google on "Multiple-step OLE DB operation generated errors" - you will
get a ton of hits on this. From what I remember, there are Windows API
calls to enumerate through the error(s) that were generated. If you can
do this, sometimes it might shed some light on what the problem is.
Also - google on "80040e21" (the error number - perhaps also the longint
version, as well) - from what I have googled, this error seems to be
because you are trying to insert more data than the field size will hold
(?), as you note...
> I've double-checked to make sure the column is of datatype "text" and
> not anything else. It is. So, it should accept unlimited text, right?
This is what is bizarre - yeah, it should hold "unlimited" (actually, I
think the limit is 2 gig). Now, you noted in a later email the settings
in your driver setup. This might be the issue - play around with it. Set
it to a higher number, and try inserting that much, then more - and see
if when the error occurs changes. If so, then you know that is it. You
might also uncheck the "treat as longvarchar", see if that helps. If you
have to have this checked, and setting the number to something larger
helps - then set it to a *very* large number, something you would never hit.
What is really perplexing me is that I have a VB app that uses such
fields, and I left the driver settings as is, and I haven't run into any
problems (and this app does have data in these fields in excess of 8190
bytes).
Hope something of this helps...
Andrew Ayers
Phoenix, Arizona
-- CONFIDENTIALITY NOTICE --
This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the intended addressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use, copy, disclose or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email, and delete the message. Thank you.
Jeff Eckermann
"Text" in this context refers to the PostgreSQL
datatype "text". Whether the setting matters depends
on whether "text" is what you are using, and on what
LongVarChar maps to in your environment. For example,
in MS Access it maps to "memo", which has limitations
(e.g. cannot be included in the where clause of a
query).
I think better just to up the number. Pick a big
number, and see what happens. While you are at it, up
the Max VarChar setting, too.
__________________________________
Do you Yahoo!?
Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
http://hotjobs.sweepstakes.yahoo.com/signingbonus
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
Ben Ramsey
> datatype "text". Whether the setting matters depends
> on whether "text" is what you are using, and on what
> LongVarChar maps to in your environment. For example,
> in MS Access it maps to "memo", which has limitations
> (e.g. cannot be included in the where clause of a
> query).
>
> I think better just to up the number. Pick a big
> number, and see what happens. While you are at it, up
> the Max VarChar setting, too.
Well, I am using the PostgreSQL datatype "text" in this context, so that
should work. Also, why should I raise Max VarChar over the default
varchar setting of 255?
I guess what I'll do is just play around with it as someone suggested.
I really didn't know those settings were there until you asked me to
post the ODBC driver settings. I had completely overlooked them.
Thanks a bunch!
-Ben
Jeff Eckermann
> > datatype "text". Whether the setting matters
> depends
> > on whether "text" is what you are using, and on
> what
> > LongVarChar maps to in your environment. For
> example,
> > in MS Access it maps to "memo", which has
> limitations
> > (e.g. cannot be included in the where clause of a
> > query).
> >
> > I think better just to up the number. Pick a big
> > number, and see what happens. While you are at
> it, up
> > the Max VarChar setting, too.
>
> Well, I am using the PostgreSQL datatype "text" in
> this context, so that
> should work. Also, why should I raise Max VarChar
> over the default
> varchar setting of 255?
I guess it doesn't matter if you are using the text
datatype. AFAIK varchar(n) maps to ODBC VarChar, so
it would matter if you were using varchar(n).
>
> I guess what I'll do is just play around with it as
> someone suggested.
> I really didn't know those settings were there until
> you asked me to
> post the ODBC driver settings. I had completely
> overlooked them.
>
> Thanks a bunch!
We haven't established that this works, yet ;-)
__________________________________
Do you Yahoo!?
Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
http://hotjobs.sweepstakes.yahoo.com/signingbonus
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majo...@postgresql.org so that your
message can get through to the mailing list cleanly
Ben Ramsey
> 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
Ben Ramsey
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
Jeff Eckermann
set "CursorLocation" to "adUseClient".
Try that and let us know how it goes.
--- Ben Ramsey <b...@benandliz.com> wrote:
=== message truncated ===
__________________________________
Do you Yahoo!?
Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes
http://hotjobs.sweepstakes.yahoo.com/signingbonus
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
Ben Ramsey
NewsRS.Update:
Microsoft Cursor Engine error '80004005'
Insufficient base table information for updating or refreshing.
/cora/coraModules/news/newsUpdate.asp, line 1054
Jeff Eckermann wrote:
>I found a potential answer in the archives:
>set "CursorLocation" to "adUseClient".
>Try that and let us know how it goes.
>
>
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majo...@postgresql.org
Hiroshi Inoue
http://www.geocities.jp/inocchichichi/psqlodbc/ .
regards,
Hiroshi Inoue
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to
> majo...@postgresql.org)
>
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Ben Ramsey
Ben Ramsey
from the other one, but that doesn't exactly matter because it appears
to be working exactly the way I need it to work.
Thanks a bunch!!!
-Ben
Hiroshi Inoue
> From: Ben Ramsey
> To: Hiroshi Inoue; pgsql...@postgresql.org
> Subject: Re: [ODBC] Adding long text to a text field
> generates an error
>
>
> How is this DLL different from the one I am currently using?
The driver's version is 7.3.0208. You can see the changes after 7.3.0200
at http://gborg.postgresql.org/project/psqlodbc/projdisplay.php (View Web
CVS).
For example, the change
[7.03.0201]
1) Revise the handling of descriptors and implement SQLCopyDesc.
2) Handle data_at_execution columns for SQLSetPos or SQLBulkOperations
would fix your problem.
regards,
Hiroshi Inoue