Paradox Community
Search:

 Welcome |  What is Paradox |  Paradox Folk |  Paradox Solutions |
 Interactive Paradox |  Paradox Programming |  Internet/Intranet Development |
 Support Options |  Classified Ads |  Wish List |  Submissions 


Paradox Newsgroups  |  Paradox Web Sites  |  Paradox Book List  |  FAQs From The Corel FAQ Newsgroup  



Subject: FAQ:PdoxWin:BLObs and Local SQL:2002.03.26

Version 1.0 (2002.03.26)
Written by Bertil Isberg.
edited by Mike Irwin

====================
0. Introduction
====================

This FAQ addresses the handling of BLObs with the Local 
SQL engine.

Thanks to Bertil Isberg for the research.

-------------------------------
 0.1 Legal Info and Disclaimers
-------------------------------

Paradox is a trademark of Corel.
Borland Database Engine (BDE) is a trademark of Inprise.

The information provided in this FAQ is provided "as is"
and is not warranted in any way. The information provided
in this FAQ is not endorsed or authorized by Corel or
Inprise in any shape, form, or manner.

The editors claim NO responsibility for ANY illegal
activity regarding this file, or as a result of someone
reading this file.

You may distribute this file, as long as the copies are
complete, unaltered, and are in electronic form only.

-------------
 0.2 Feedback
-------------

Please send feedback in a Corel Paradox newsgroup or the
news:comp.databases.Paradox newsgroup to any of the FAQ
Team mentioned in the "FAQ: FAQ FAQ" document.
Please preface the subject of your post with the string
"PDXWIN FAQ" to alert Team members to the function of
the message.

Please specify the FAQ name and section number the
comment applies to, if any.

-------------
 0.3 Attachments
-------------

There are no attachments.


==============================
1. Problem 1
==============================

Running local sql  update statements on tables
containing blob columns like memo and formatted memo.

-------------
 1.1 Summary
-------------

SQL Property: "Generate Auxiliary tables" interferes
with the number of records updated

-------------
 1.2 Demo
-------------

To update all records containing a memo field:

update table
set memo="value"

does not work.

-------------
 1.3 WorkAround
-------------

add a where clause

update table
set memo="value"
where 1=1

==============================
2. Problem 2
==============================

update table
set memo="value"
where id < value

will run forever.

The upper boundary has to be closed
as follows:

<=value.

This also applies to a where clause like

 where  id>value1 and id < value2

==============================
3. Problem 3
==============================

update  table
set id=id

will wipe out contents of memo columns

It's in Bertil Isberg's buglist Px0386.

==============================
4. Problem 4
==============================

update table
set formattedmemo="value"

does not work. It exhibits a variety of 
problems !

-------------
 4.1 Setup Demo of Problem 4
-------------

SETUP:

Create a table Px0386c

id - I *
m1 - M 1
m2 - M 1
f1 - f 1


Add 20 records with id=1-20. Leave the memo fields blank. 
They will be updated in the examples.

Run a local sql. Generate auxiliary tables should be checked 
in the SQL properties dialog.

You have to reassign values to M1 and M2 now and then to be 
able to see the result.

-------------
 4.2 Straight use of Update statement
-------------

update px0386c
set m1="abc"

Only 16 records are updated.

-------------
 4.3 Adding an open restriction with a Where clause
-------------

update px0386c
set m2="def"
where id>0

Only 16 records are updated

-------------
 4.4 Adding an Equality restriction with a Where clause
-------------

update px0386c
set m1="bcd"
where 1=1

only 16 records are updated

==============================
5. Problem 4 Repeated
==============================

Change the SQL properties to Fast query.
Use this setting from now on.
1) still only 16 records
2) all records are updated.
3) all records are updated.

-------------
 4.1 Use two open restrictions with a Where clause
-------------

update px0386c
set m1=m2
where id>0 and id<21

will run forever

-------------
 4.2 Use an open restriction with a Where clause
-------------

update px0386c
set m1=m2
where id<21

will run forever

-------------
 4.3 A precise open restriction with a Where clause
-------------

update px0386c
set m1=m2
where id<=20

is Ok. Note that the ID corresponds exactly to the
number of rows ...

-------------
 4.4 An enclosed range
-------------

update px0386c
set m1=m2
where id>=1 and id<=20

is Ok. Again, the enclosure is exactly the sise
of the dataset

-------------
 4.5 Another enclosed range
-------------

update px0386c
set m1=m2
where id between 1 and 20

is Ok. Again, the enclosure is exactly the sise
of the dataset

-------------
 4.6 Another enclosed range
-------------

update px0386c
set m1="zzz"
 where id in(1,2,3,4,5,6,7,8,
 9,10,11,12,13,14,15,16,17,18,19,20)

is Ok. Again, the enclosure is exactly the sise
of the dataset

-------------
 4.7 Equality fails ...
-------------

This one is logged (Px0386)

update px0386c
set id=id

will wipe out data from memo columns for some 
records. These are record 9 and later.

-------------
 4.8 Formatted Memos
-------------

update px0386c
set f1="abc"
where 1=1

does not work.
You cannot update a formatted memo at all using 
Local SQL. The result is a corrupted table.
Entering Memo view on f1 field in table view 
gives:

"Error writing file.Record not tagged."

==============================
5. Comments
==============================

I have tested 1) with a table with only one blob field, 
and I still get only 16 records updated.

The behaviours described  have been verified by: 

Ivica Kolar <telpro@kvid.hr


Paradox Community Newsgroups


 Feedback |  Paradox Day |  Who Uses Paradox |  I Use Paradox |  Downloads 


 The information provided on this Web site is not in any way sponsored or endorsed by Corel Corporation.
 Paradox is a registered trademark of Corel Corporation.


 Modified: 15 May 2003
 Terms of Use / Legal Disclaimer


 Copyright © 2001- 2003 Paradox Community. All rights reserved. 
 Company and product names are trademarks or registered trademarks of their respective companies. 
 Authors hold the copyrights to their own works. Please contact the author of any article for details.