Q: When I used HSQL, I frequently used the “Memo” data type.
It isn’t available with the Firebird embedded engine, which I believe is “the future”.
What can I use instead to store multi-line fragments of text? I can’t find the CLOB[BLOB SUB_TYPE TEXT] which some say is the answer, and which seems to have very little documentation.
(Answers have been provided! (^_^) For them, see BELOW this explanation of ways the of ways I was going wrong.)
======
The following essay will put flesh on those bare bones. Maybe you too have been going wrong where I was in my hours of trying something that others say is no problem?
This isn’t the first time I have struggled with this. (This follows on from How do I create field for multi-line text? (Firebird) - #3 by Ratslinger[link text](How do I create field for multi-line text? (Firebird) - #3 by Ratslinger. That no longer works for me, and it uses CBLOG, which I have been warned brings its own problems for some users.)
Below there is an explanation of a tiresome road that has taken much of a day to travel. I am leaving it, to help others in the future to FIND this information. I HAVE searched… both the forum and with Google search. Found little already available. Thanks to madMatt and a kind person who sent private messages, I finally got “there”!
All this on Windows 10. Earlier, which some of this comes from, I was using Libre Office 7.0.3.1 Throughout, I am using Firebird as an EMBEDDED engine. Not connecting to an external Firebird engine.
AFAIK, you can only put a CBLOB [BLOB] field in a table during the table’s initial creation. I mean you can only make it part of the schema then. Once it is there, you can, of course, put data into that field when and as you create records in the table. (That was true under LO 7.0.3.1. PLEASE KARMA it has been fixed!) Happily, for multi-line text, it turns out you don’t need any variant of CBLOB. (I don’t like using obscure elements of any system.)
Discovered along the way, but irrelevant to final answer… maybe:
If I create a table and then work directly with the table… not a good idea, remember… I can “do things”. A field consisting of multiple lines of text can be entered. You use ctrl-enter to start a new line within the CBLOB field…
… and then drag the bottom edge of the bottom row of the table down, to make the cells higher, so you can SEE your multi-line data.
Some say that should be shift-enter. Maybe “the answer” for Linux or Mac? It’s ctrl-enter that works for Windows 10.
Sigh. So… “works”. I can get part-way down the road.
I couldn’t when I wrote the above, get a form to “work” for that table.
More seriously, mistakes can be costly and extremely inconvenient. There’s not always a simple way to cancel out of where you can be taken when attempting this stuff.
Under LO 7.0.3.1. It’s early days, but 7.0.5.2 seems LESS prone to that, but still crashes my system far more often and more intractably than any other app I’ve spent time with in the past 20 years.) There wasn’t for me, anyway. I got questions about “do I want to convert some HSLQ stuff in my database to Firebird” (that’s not a perfect quote). (Neither “yes” nor “later” got me anywhere… except maybe deeper.) Even Device Manager has trouble “killing” a crashed LO sometimes. (Don’t give up too soon, if you have other things open that you don’t want to re-start… End Task multiple times, and be patient.)
For no reason I can grasp, before 7.0.5.2 I was asked sometimes for the password for a SQL database on the computer. As with the HSQL modal dialog, neither option worked… in any sensible way.
Now… SOMETIMES, if I said “cancel” or “later” again and again… including to a dialog (“insert field”? I forget)… I could get out" without Device Manager. 10 to 20 such “cancels” to get out of a database with 1 table, two fields, recently opened from scratch… not a happy bunny. And sometimes it either wouldn’t work or needed more cancels than I was going to try.
So why bother with LO? Because for many things it works BRILLIANTLY. I only have these hassles when trying to learn the right way to do new things.
So- Lesson 1- update your LO to at least 7.0.5.2 to get this to “work”… well, work better.
I don’t think I did anything differently the first time I tried again under LO 7.0.5.2, but somehow, the field for notes had CBLOB[BLOB SUB_TYPE TEXT] as an option. That sub-type has been mentioned to me before, but I could never find/ access it until now.
(Remember: the final answer for me doesn’t involve CBLOB, but it isn’t widely discussed. May have a place for other things.)
Immediately after restarting my machine after upgrading to 7.0.5.2, I did a new table from scratch. Some of the following may seem “obvious”… but something has been getting in the way of success, so maybe my “obvious” includes bad ideas. I’m going to this effort so the person who tries this next doesn’t have to “guess” at the forks along the road to where I seem to have found success.
Used the “old”, pre-upgrade to 7.0.5.2 Windows “Start” menu LO Base button on my start menu… and the database wizard opened. Apparently, my “download/ install” took care of “re-attaching” it to the right thing. (I did nothing to “clear” my old LO before installing 7.0.5.2)
Said create a new database, using embedded Firebird, clicked “Next”, NOT “Finish”. Beware that one.
I said, “Yes, register”. (Newbies: Just Do it. Don’t worry, it isn’t going to “register” your database with Big Brother.)
…and say “Open the table for editing after file saved”. (Newbies: You are only “creating a container” at the moment. Setting up tables, etc, IN the “container” is to come.
And NOW you click “Finish”, and save it in a sensible folder, under a sensible name.
The main Base project management window opens, in the “Tables” section.
Invoke “Create table in design view”
First field: “ID”/ Integer/ AutoValue… it will be made the table’s primary key for you… the “key” icon in the narrow left column will tell you that this has been made the primary key.)
(PS- TURNS OUT THIS isn’t the answer… well, not the one I will try to “make work”. But it “works” on some levels, and is what some people recommend. Explained further, when you read on.)
Second field: “MultiLine”: CBLOB[BLOB SUB_TYPE TEXT]
! ! ! That’s the only type of CBLOB on offer now! THAT is NEW in 7.0.5.2 (This is good… it is the type of CBLOB that was previously recommended by someone who wasn’t having trouble with multi-line text) ! ! !
(Don’t try to change the length of the field (you can’t)… or worry about it… Each record will only use the storage space it needs.)
Save your design. Close the dialog.
Double-click on the newly created table in the main Base project management window… yes, at first I am going to work directly with the table. Bad practice.
Make the cells of the grid about 6 lines high by dragging the bottom edge of the bottom row of the grid down.
Enter two records. Use ctrl-enter to start a new line in the data you supply to each record’s “MultiLine” field. (It may be shift-enter in some environments.)
Close the table. Agree to the save, if it asks you, which it should if you didn’t save the second record after altering it.
Double click on your new table’s name in the main Base project management window.
Presto! A table! WITH multi-line “memos” (old skool name).
===
Onward…
(By the way- there are other ways to the same destination. I am giving ONE of the ways that SEEMS to work, now… it didn’t more than an hour ago when I started this, but it does now, after the LO version update. If YOU are having trouble, try EXACTLY this route through the quagmire.)
Go to the “Forms” part of the main Base project management window, and invoke “Use wizard to create new form”.
As you only have one table so far, it will assume that the form is for that table. When you have more, or maybe some queries, it will ask you which table(s)/ query(s) you want the form to display.
Use the double “>” to “move” all of the table’s fields into the “Fields in the form” column.
After Field Selection click “Next”.
Do nothing in “Subforms”, just click “Next”.
IN ARRANGE CONTROLS, go for “Columnar-labels on top”… first alternative to the left of the default “As Data Sheet”.
NOW you can click “Finish”.
Give the form a name that is DIFFERENT from the table’s name. (I usually just add “DE”, for “Data Entry”, to the table’s name… which is offered as a default name. Another Just Do It.)
Try “Work with the form”.
Either that didn’t work for me, or it “worked” after a 20 second hiatus, during which time I tried to cancel the first attempt before doing what is above again. Sigh. (Eventually, I did save it, and haven’t seen that behavior since.)
The form should open… and you can move between records… BUT YOU WON’T SEE YOUR DATA in the “box” labeled “MultiLine”. Sigh. But we can fix that!! (^_^)
Open the form for edit.
You’ll need the “Form Controls” toolbar. (Use “View/Toolbars” to be sure you have that.)
Click the “Text Box” add-control icon and then “drag out” a textbox on the form.
Rt-click. Control properties. Data tab.
Set the control’s Data Field to be the field in your table named “MultiLine”.
Run the form. Presto! You should be able see your data! (^_^) Fingers crossed that it worked for you.
Don’t, by the way, try to work with the data in your table BOTH via the table directly and via your form at the same time. Or even edit anyting when it is also open (in a separate window) for viewing. That seems to be a trigger of the really awful crashes. You can often get away with it for a while, which tempts me, again and again, to go down that road.
Remember: The data isn’t “in” the table just because it is on your screen, either by using the table view or the form view. Each is just some stuff on the screen that intially came from the table… but may have been changed by you in the meantime.
Why is it a really bad idea to have a table “open” via both, at the same time? What if you change what’s in a field… twice… one via each interface to the table? (to different values, without saving your changes. (Either “by hand”, or by moving to a different record (which does an automatic “save record”.)
It is a nuisance, but if you want to go back and forth, SHUT table (direct access) or form (mediated access) BEFORE opening a view into the table by the other route.
So… solved!
But.
But if you go the CBLOB route, madMatti kindly warns us that we will be in a world of hurt if we try to use data from the database in a report, or with Writer. (Apparently, Writer is unable to deserialize the datatype. (And reports output to Writer.)
Thankfully, he has an answer that solves two problems… but it causes another.
Please award his answer some Karma points by giving it a “thumbs up”.
He said (words to the effect)…
I switched from using CBLOB for multiline data to using VARCHAR
With VCHAR I
can save multiline data just
fine.
He also identified some problems with using the CBLOB datatype for storing text.
You probably CAN “save” an old form with some editing, but I’ll write up how to do a new one from scratch, to display multiline data from a VARCHAR-type field.
Obviously, you need a table with at least one VARCHAR-type field. Be sure the “length” property for that control is set to something high enough for your needs.
Put just few very simple records in your table, with single-line data in the VARCHAR field. You can do this the “basic” way: access the table directly.
CLOSE the table.
Go to the “Forms” section of the main Base project management window.
Invoke "Use Wizard to create a form for the table. Proceed as before. You should get something that almost works.
Close the newly created form.
Re-open it in “Edit” mode.
Tricky Bit! Beware! Without this, what comes after it “won’t work”.
In a wizard-created simple form, the parts (text boxes) that display what is in each field are grouped with labels for those text boxes.
You need to access the properties of the text box.
Use ctrl-click to select just the text box. Then right-click on it, and ask for the CONTROL properties. (Not the FORM properties.)
((Digression starts…
Alternatively, you can ungroup the two as follows:
Right-click on the control that is for displaying the field you want to use for multi-line data. Invoke “Ungroup”.
The label and the textbox for the field were “grouped” because usually that is helpful… but it means you can’t access some properties.
…Digression ENDS))
On the General tab, scroll down to “text type”; specify “multi-line”. (You may want to turn scrollbars on while you’re at it.)
I found that for me the “Text lines end” property was already set to “LF (Unix)”, which is recommended by someone who knows more about these things than I do.
((“Unnecessary” frill: If people may be using your form to ENTER data into the database, it may be helpful to set the viewing control’s text length limit to whatever limit you set on that datum in the specification of the table. But I found something weird going on:
My TABLE was set up to allow 30 characters in that field. My FORM, in a deliberate “mistake”, was set up to allow 40 characters in the box for that field. I entered a datum that consisted of 35 characters. Closed down the database, doing saves all along. Re-opened it. The field that had been… and still was… set up with a “length” value of 30 was quite happily holding a 35 character value. Hmm. (I double checked that by looking at the table directly, too. ((“Frill” ends))
Click the “x” in the upper right of the Control Properties dialog to close it. Use the normal “drag a corner” method to change the size of the control to what will be needed for the data you intend to enter.
And now you have a database with a multi-line field, and a form for viewing or entering such data!
While entering or editing a datum, you only need to press enter to start a new line. (Not the easily-forgotten “ctrl-enter” I used under the system I started with.)
But now I have a (small) new problem.
Though I know I shouldn’t, I often work with the data in a table “directly”, i.e. I open the table from the “Table” part of the main Base project management window, and work on the data there. Shouldn’t be “neccessary”. Just make yourself a form using the “data sheet” template. Working with that will LOOK like working with the table directly. I would do that! But in the past, I’ve sometimes found that the form didn’t always work as well as direct access. Maybe the nice programming people have fixed that!
Working with the table directly, I can introduce new-lines into the text of a CLBLOB multi-line field. (You use ctrl-enter, as mentioned a while back. OR maybe shift-enter, on some systems.) I haven’t (yet!) found a way to insert a new-line into the text of a VARCHAR field. Sigh.
Furthermore, if I look at the data via direct access to the table, I don’t see the new-lines. They aren’t LOST, but with “direct” access to a table,
The quick
brown fox
jumped over
… is displayed as…
The quick brown fox jumped over
Thoughts?
What a relief! After HOURS on the above, I then went off to see if the result would “play nicely” with Writer.
I’m delighted to tell you that it did… at least it did well enough for my wants!
After writing the above, I foolishly I tried to get a report out of the system. (I say “foolishly” because my brain was mush after the long day fighting with these things.)
And it looked like my multi-line data was being squashed to a single line!
Not the problem it seemed!
I only needed to make the control the multi-line text was to be displayed in high enough, and all was well. WHEW!
See
http://sheepdogguides.com/fdb/fdb1addrlab.htm
For the details of generating address labels from a database.
===============================
Comments, questions, etc would be VERY WELCOME!
