![]() |
![]() |
|
![]() |
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. ![]() |
![]() |
|