using mulit lines in database.

[sparkythex]

i was playing with the DB (database) example BooksHN.

i was looking at how your saving the opt for title & etc.
it seems that your just putting one line in to an var (something like an array)

so I started to look at what the comment field does as it looks to be mulitlined.
as my database will have mulit lines to it.
I wanted to search out how you managed multi lines.

I think i found an error.
 here are some results of testing.
In the comments field I was typing something mult lined

I used something simple, I put the same thing  to more then one record:

Code: [Select]

some thing
 sort of long      :twisted:  (copy & paste is my friend!)

it doesn't matter what is there in record one or how long it was it only saved upto my 1st enter, so it only saved the top line.
so all that got saved was:

Code: [Select]

some thing
now the next odd thing i found is that (this took me a bit to notice too)
what is in the 2nd line seems to overwrite if there is a next then one record
so what I did was in comments for rec1 put "one"; in rec2 put "two"; in rec3 put "three"
then went back to rec one i did multi line like:

Code: [Select]

111
222
333
444
so what i got was
in comments for rec1 put "111"; in rec2 put "222"; in rec3 put "333"
so i totally lost original values of
in comments for rec1 put "one"; in rec2 put "two"; in rec3 put "three".
it seems the "4"s got lost in lala land.

I really don't want this to seem like am busting your chops over free software.
I really do like it & Like that you have this forum for help.
you have helped me a lot already.
but
also your example database doesn't cover how to save the database, which i believe is somewhat a crucial part of the example.
I know there are other post on saving, but it just seems incomplete, I'm easily confused.
 in order to save it seems to be i have to use the CSV (cama seperate value)
or at least that is what i gleamed out of this other post http://tigabyte.com/forums/viewtopic.php?f=19&t=45
but not really sure how to impliment that.

but am really confused on how to get the data saved properly.
then once it is saved how to load it back up so that it can be edited again.
but that is another topic (hint, hint, hint)
      http://tigabyte.com/forums/viewtopic.php?f=22&t=14    

[Malkom]

i was playing with the DB (database) example BooksHN.

i was looking at how your saving the opt for title & etc.
it seems that your just putting one line in to an var (something like an array)

so I started to look at what the comment field does as it looks to be mulitlined.
as my database will have mulit lines to it.
I wanted to search out how you managed multi lines.

I think i found an error.
 here are some results of testing.
In the comments field I was typing something mult lined

I used something simple, I put the same thing  to more then one record:

Code: [Select]

some thing
 sort of long      :twisted:  (copy & paste is my friend!)

it doesn't matter what is there in record one or how long it was it only saved upto my 1st enter, so it only saved the top line.
so all that got saved was:

Code: [Select]

some thing
now the next odd thing i found is that (this took me a bit to notice too)
what is in the 2nd line seems to overwrite if there is a next then one record
so what I did was in comments for rec1 put "one"; in rec2 put "two"; in rec3 put "three"
then went back to rec one i did multi line like:

Code: [Select]

111
222
333
444
so what i got was
in comments for rec1 put "111"; in rec2 put "222"; in rec3 put "333"
so i totally lost original values of
in comments for rec1 put "one"; in rec2 put "two"; in rec3 put "three".
it seems the "4"s got lost in lala land.

I really don't want this to seem like am busting your chops over free software.
I really do like it & Like that you have this forum for help.
you have helped me a lot already.
but
also your example database doesn't cover how to save the database, which i believe is somewhat a crucial part of the example.
I know there are other post on saving, but it just seems incomplete, I'm easily confused.
 in order to save it seems to be i have to use the CSV (cama seperate value)
or at least that is what i gleamed out of this other post http://tigabyte.com/forums/viewtopic.php?f=19&t=45
but not really sure how to impliment that.

but am really confused on how to get the data saved properly.
then once it is saved how to load it back up so that it can be edited again.
but that is another topic (hint, hint, hint)
      http://tigabyte.com/forums/viewtopic.php?f=22&t=14    

The reason you are having problems is due to the design of the Books HN example that was only put together to store single lines of data. The lines in a field control are separated by the character having ASCII value 13 so when you add a data value comprised of multiple lines to the database it changes the total number of fields in the global variable storing that type of data resulting in a mess - the data records go out of sync.

Two approaches to solve this problem come to mind:-

1) The simple way - Before storing the value from a field control replace its line endings with another character not used in your data - perhaps '|'. The when you need to display that record just convert the special character back to Chr(13) and put it into the field control etc. Note, you don't have to replace the line ending character with just one character - you could use a whole word such as '45hstdxq' that will never appear naturally in your data.

Code: [Select]

@ in the code below - the field control contains multi-line text
@ the other 2 field controls show that the text is not corrupted
Local data,lineend,safeend

Put ChrFN(13) into lineend
Put '|' into safeend

Put field 1 into data
ReplaceAll lineend with safeend in data
Put data into field 2

ReplaceAll safeend with lineend in data
Put data into field 3

2) The more complex way - Use arrays to store your multi-line database records but this will require replacing all the global variables holding field data by an array.

Code: [Select]

@ in the code below - the field control 1 contains multi-line text
@ the other 2 field controls show that the text is not corrupted
Local okay,data,arrayname

@ name array 'database'
Put 'database' into arrayname

@ create array with 10 records, each record has 5 fields
Put ArrayCreateFN(arrayname,10,5) into okay

@ for clarity only use the first field of each record
Put field 1 into data
@ set the first field in record 1
ArrayPutValue(arrayname,1,1,data)
@ set the first field in record 2
ArrayPutValue(arrayname,2,1,data)

Put ArrayValueFN(arrayname,1,1) into field 2
Put ArrayValueFN(arrayname,2,1) into field 3


About saving your database:-
By default HyperNext saves all fields, variables but does not save arrays. When it runs a program it then tries to reload any automatically saved data.
In the Book HN example no explicit code for saving was required as it just relied on HyperNext saving everything then later reloading it.


About which method to use. If you  can be certain your data will never contain the special character/word then use method 1 otherwise use method 2. Of course with method 2 you must write a procedure to save your text data to a file. I would probably go with method 1 as method 2 would still need some delimiter to separate the record fields in you saved text file.

[sparkythex]

I like simple!

1) The simple way - Before storing the value from a field control replace its line endings with another character not used in your data - perhaps '|'. The when you need to display that record just convert the special character back to Chr(13) and put it into the field control etc. Note, you don't have to replace the line ending character with just one character - you could use a whole word such as '45hstdxq' that will never appear naturally in your data.

Code: [Select]

@ in the code below - the field control contains multi-line text
@ the other 2 field controls show that the text is not corrupted
Local data,lineend,safeend

Put ChrFN(13) into lineend
Put '|' into safeend

Put field 1 into data
ReplaceAll lineend with safeend in data
Put data into field 2

ReplaceAll safeend with lineend in data
Put data into field 3
About saving your database:-
By default HyperNext saves all fields, variables but does not save arrays. When it runs a program it then tries to reload any automatically saved data.
In the Book HN example no explicit code for saving was required as it just relied on HyperNext saving everything then later reloading it.

on the exmple project
you have the update for field9 = the commets doing:

Code: [Select]

Put field 9 into svar
If svar$='' Then
    Put '*' into svar
EndIf
Put svar into line bookIndex of commentVar

so i would put

Code: [Select]

Put field 9 into svar
If svar$='' Then

***
@ the other 2 field controls show that the text is not corrupted

Put ChrFN(13) into lineend
Put '|' into safeend

Put field 9 into data
ReplaceAll lineend with safeend in data
Put data into field 2

ReplaceAll safeend with lineend in data
Put data into field 3
    Put data into svar
EndIf
Put svar into line bookIndex of commentVar

then when i wanted to save i would do something like

Code: [Select]

@ delimiter
Put ',' into comma

@ File name
Put 'comicbook1.csv' into fname

@ Create and open file
FileGet(fname,fvar,fdets,ftypes,fpaths,fnames,fextens)
CreateBFile(fvar,findex,n)

@ Write data to file
WriteBVariable(findex,titleVar)
WriteBVariable(findex,comma)

WriteBVariable(findex,authorVar)
WriteBVariable(findex,comma)

WriteBVariable(findex,publisherVar)
WriteBVariable(findex,comma)

WriteBVariable(findex,commentVar)
WriteBVariable(findex,comma)

@ Close file
CloseBFile(findex)that is going to separate everything & save it too correct?

but then how to  put that back?

[Malkom]

You could use something like this to read it back into a field and skip the commas:-

Code: [Select]

@ Read from a named file, assumes format known
Local fname,fvar,fdets,ftypes,fpaths,fnames,fextens
Local findex,fend

local comma,mess

@ delimiter
Put ',' into comma

@ File name
Put 'comicbook1.csv' into fname

FileGet(fname,fvar,fdets,ftypes,fpaths,fnames,fextens)
OpenAsBFile(fvar,findex)

@ ++ loop over file until no data remaining +++
Put EndBFileFN(findex) into fend
While (fend<>1)
    ReadBVariable(findex,mess)
    if mess$<>comma then
        Put mess after field 2
    EndIf
    Put EndBFileFN(findex) into fend
EndWhile

CloseBFile(findex)

[sparkythex]

I have been trying to mess with this for a long time now.

I still can't seem to get multi lines in the "book hn" project.  (or my project.)
I'm at the point that I can't go any further till I get this feature to work.

How do I get the field to not push the 2nd & 3rd lines in to the next record?

[Malkom]

Here are two procedures that might help. Note, HyperNext has no user-defined functions so the global variable returnValue is used to get returned values.

(1) It converts multiline text into a text block that has no line endings and so can be easily saved/retrieved to/from a text file.

the procedure is called ConvertToBlock and its parameter is stext

Code: [Select]

Global returnValue
Local sep1,sep2

Put stext into returnValue

Put ChrFN(13) into sep1

Put '~*~' into sep2

ReplaceAll sep1 with sep2 in returnValue


(2) It converts block text into text with line endings and so can be used in the database.

the procedure is called ConvertToLines and its parameter is stext

Code: [Select]

Global returnValue
Local sep1,sep2

Put stext into returnValue

Put '~*~' into sep1

Put ChrFN(13) into sep2

ReplaceAll sep1 with sep2 in returnValue

To use, the procedures assume multi-line text is in field 1, block text goes to field 2 but could be saved as a record in a file, and original text goes into field 3

Code: [Select]

Global returnValue
Local s1

@ convert line endings
Put field 1 into s1

Call ConvertToBlock(s1)
Put returnValue into field 2


@ reinstate line endings
Put field 2 into s1

Call ConvertToLines(s1)
Put returnValue into field 3

[sparkythex]

MAYBE I miss read something here.
the db is not letting it update.

Ok Keep in mind I'm using the example BooksHN.prj.

so when the new button is hit it shows this:

Code: [Select]

Call UpdateRecord
Call CreateRecord
Call DisplayRecord

in the main code for procs:  UpdateRecord
it shows:

Code: [Select]

@@@ use for end of line.
@@@ ¶⁂⁋
Global titleVar,authorVar,publisherVar,commentVar
Global bookIndex
Global returnValue
Local s1

Put field 6 into returnValue
If returnValue$='' Then
    Put '*' into returnValue
EndIf
Put returnValue into line bookIndex of titleVar

Put field 7 into returnValue
If returnValue$='' Then
    Put '*' into returnValue
EndIf
Put returnValue into line bookIndex of authorVar

Put field 8 into returnValue
If returnValue$='' Then
    Put '*' into returnValue
EndIf
Put returnValue into line bookIndex of publisherVar

@@@@@@@@@
@@ original lines
@@@@@@@@
@Put field 9 into returnValue
@If svar$='' Then
@    Put '*' into returnValue
@EndIf
@Put svar into line bookIndex of commentVar

    Put field 9 into returnValue
    If returnValue$='' Then
 

    @***
    @ the other 2 field controls show that the text is not corrupted

    Put ChrFN(13) into lineend
        @Put ChrFN(13) into sep2

    Put '¶⁂⁋' into safeend
        @Put '~*~' into sep1

    ReplaceAll safeend with lineend in returnValue
   
    @@ convert line endings
    @@ Put field 1 into s1 (local)
    Put field 9 into data
@@@@@@
Call ConvertToBlock(data)
Put returnValue into field 4

@ reinstate line endings
Put field 4 into data

Call ConvertToLines(data)
Put returnValue into field 1
@@@@@@@@@@@
    @@@ReplaceAll lineend with safeend in data
    Put data into field 2

    ReplaceAll safeend with lineend in data
    Put data into field 3
        Put data into svar
    EndIf
    Put svar into line bookIndex of commentVar

[Malkom]

Yes this having to convert to safe and then converting back to the original can be confusing.

Basically in your Update method just call ConvertToBlock and store its returnValue in the correct line of the global var commentVar:-

Code: [Select]

@ -- Update record ---
Global returnValue
Local data

Put field 9 into data
Call ConvertToBlock(data)

Put returnValue into line bookIndex of commentVar

When you need to display that record the use the ConvertToLines method:-

Code: [Select]

@ -- Display record ---
Global returnValue
Local data

Put bookIndex of commentVar into data

Call ConvertToLines(data)

Put returnValue into field 9

To get this working, do it in steps - get it working with just a single line in the comment field.
Then when that works introduce the ConvertToBlock and ConvertToLines procs - it should still work

Then start using multi-lines in the comments.

This way it should be easier to see where the problem occurs.

 

[sparkythex]

ok here is what happens now.
 
It is pretty much the same problem only now i get extra repeat if i use numbers instead of words.

When I put

1
1

in to the comments of rec one.
I ended up with a 1, in rec one, two, three.
Likewise if I edit the rec two to show

2
2

it will put that out three records as well.  (before it would just space out based off how many lines. Now it seems to put in an extra one.)
when i used word

aaaaa
aaaaa

it seemed to only space that out in to two (since I only have the two lines)

[Malkom]

ok here is what happens now.
 
It is pretty much the same problem only now i get extra repeat if i use numbers instead of words.

When I put

1
1

in to the comments of rec one.
I ended up with a 1, in rec one, two, three.
Likewise if I edit the rec two to show

2
2

it will put that out three records as well.  (before it would just space out based off how many lines. Now it seems to put in an extra one.)
when i used word

aaaaa
aaaaa

it seemed to only space that out in to two (since I only have the two lines)

It looks like the text in the comment part of each record has line endings in it so they are pushed over into the line of the next record comment.

The text in the comments section of each record needs to be free of line endings. This original text  can then be used to produce text with line endings that is to be put into a field on the card. So if the records are free of line endings, their text can be copied to a temporary variable which then has any line endings added back using the ConvertToLines, then the temporary variable is placed in the field.

[sparkythex]

It looks like the text in the comment part of each record has line endings in it so they are pushed over into the line of the next record comment.

The text in the comments section of each record needs to be free of line endings. This original text  can then be used to produce text with line endings that is to be put into a field on the card. So if the records are free of line endings, their text can be copied to a temporary variable which then has any line endings added back using the ConvertToLines, then the temporary variable is placed in the field.

Ah yeah,  that was the whole point of my questions.
I want/need to save a multi-line field.
I'm really starting to believe it some sort of limit to the software.  Which is a major bumber.  I really wanted to have it sorted like a database.

[tigabyte]

It looks like the text in the comment part of each record has line endings in it so they are pushed over into the line of the next record comment.

The text in the comments section of each record needs to be free of line endings. This original text  can then be used to produce text with line endings that is to be put into a field on the card. So if the records are free of line endings, their text can be copied to a temporary variable which then has any line endings added back using the ConvertToLines, then the temporary variable is placed in the field.

Ah yeah,  that was the whole point of my questions.
I want/need to save a multi-line field.
I'm really starting to believe it some sort of limit to the software.  Which is a major bumber.  I really wanted to have it sorted like a database.

Creating software can be frustrating and its natural to sometimes blame the tools but in this case I think its a error in your code. I have managed to get muti-line text in and out using the ConvertToBlock and ConvertToLine routines so I think they work.

Debugging is often a hard part of programming and all one can do is try to identify where the problem might be occuring so some debug output can be placed to track the cause down. You have already made a lot of progress so can probably get this working

[sparkythex]

I was sure I put the code like you provided, the area you said to.
but did I put it in the wrong spot?
or do I have something backwards?
-----------------
UpdateRecord

Code: [Select]

@@@ use for end of line.
@@@ ¶⁂⁋
Global titleVar,authorVar,publisherVar,commentVar
Global bookIndex
Global returnValue
Local data

Put field 6 into returnValue
If returnValue$='' Then
    Put '*' into returnValue
EndIf
Put returnValue into line bookIndex of titleVar

Put field 7 into returnValue
If returnValue$='' Then
    Put '*' into returnValue
EndIf
Put returnValue into line bookIndex of authorVar

Put field 8 into returnValue
If returnValue$='' Then
    Put '*' into returnValue
EndIf
Put returnValue into line bookIndex of publisherVar

@@@@@@@@@
@@ original lines
@@@@@@@@
@Put field 9 into returnValue
@If svar$='' Then
@    Put '*' into returnValue
@EndIf
@Put svar into line bookIndex of commentVar
@@@@@@@

Put field 9 into data
Call ConvertToLines(data)

Put returnValue into line bookIndex of commentVar
then
  ConvertToLines (stext)

Code: [Select]

@@@ use for end of line.
@@@ ¶⁂⁋
Global returnValue
Local sep1,sep2

Put stext into returnValue

Put '¶⁂⁋' into sep1

Put ChrFN(13) into sep2

ReplaceAll sep1 with sep2 in returnValue

Code: [Select]

UpdateRecord
@@@ use for end of line.
@@@ ¶⁂⁋
Global titleVar,authorVar,publisherVar,commentVar
Global bookIndex
Global returnValue
Local data

Put field 6 into returnValue
If returnValue$='' Then
    Put '*' into returnValue
EndIf
Put returnValue into line bookIndex of titleVar

Put field 7 into returnValue
If returnValue$='' Then
    Put '*' into returnValue
EndIf
Put returnValue into line bookIndex of authorVar

Put field 8 into returnValue
If returnValue$='' Then
    Put '*' into returnValue
EndIf
Put returnValue into line bookIndex of publisherVar

@@@@@@@@@
@@ original lines
@@@@@@@@
@Put field 9 into returnValue
@If svar$='' Then
@    Put '*' into returnValue
@EndIf
@Put svar into line bookIndex of commentVar
@@@@@@@

Put field 9 into data
Call ConvertToLines(data)

Put returnValue into line bookIndex of commentVar
then
   DisplayRecord

Code: [Select]

Global titleVar,authorVar,publisherVar,commentVar
Global bookIndex, returnValue
Local data

Put line bookIndex of titleVar into field 6
Put line bookIndex of authorVar into field 7
Put line bookIndex of publisherVar into field 8
@@@@
@ org
@Put line bookIndex of commentVar into field 9
@@@@@
Put line bookIndex of commentVar into data
Call ConvertToLines(data)

Put returnValue into field 9

@ --- show book number ---
Local pdets,npages

Put bookIndex into pdets
Append ' / ' onto pdets
Put LinesFN(titleVar) into npages
If npages=0 Then
    Increment npages
EndIf
Append npages onto pdets
TextSetValue(2,pdets)

[Malkom]

Just an update:-

HN v4.x has an SQLIte database now working with a rough demo project.

This works on both Windows and OS X.

Note, if you are going to store multi-line text in your own variables, in an array/list format then the above posts are still relevant.

There are also built in arrays that can handle multi-line text but they do not save their state on exit whereas HN variables and text fields do.