Itemdel

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

francof
Posts: 237
Joined: Fri Apr 11, 2014 10:51 am

Itemdel

Post by francof » Thu Jul 03, 2014 1:25 pm

ciao all,
I'm in trouble to separate properly 2 items returned from a query.
when I split the 2 items

Code: Select all

 set ItemDel to TAB
   repeat for each line tLine in tRsTrovaDom
      put item 1 of tLine  & return after field "txtDomTrovate"
   end repeat
into item1 I find also a part of item2, item2 should be the content of a column of a .mdb database table. that column is "MEMO" type data and contains a text in a number of various lines (for each record).
I think this is due by the fact that between the lines of that column there is a "TAB".......
which other delimiter should I use?

best regards
franco

Klaus
Posts: 14206
Joined: Sat Apr 08, 2006 8:41 am
Contact:

Re: Itemdel

Post by Klaus » Thu Jul 03, 2014 1:47 pm

Buongiorno Franco,

well, if your database entries are MULTI.line then "repeat for each line..." WILL of course give you these result! 8)
The trick is to use OTHER item- and linedelimiter when retrieving the data from the database:

Code: Select all

...
## Optional, see below
##put numtochar(1) into tItemDel
put TAB into tItemDel
put numtochar(2) into tLineDel
put revDataFromQuery(tItemDel,tLineDel,DBConnID,tSQL) into ...
...
Then you can later do this:
...

Code: Select all

## Optional:
##set itemDel to numtochar(1)
set linedel to numtochar(2)
   repeat for each line tLine in tRsTrovaDom
      put item 1 of tLine  & return after field "txtDomTrovate"
   end repeat
...
You get the picture :D


Best

Klaus

francof
Posts: 237
Joined: Fri Apr 11, 2014 10:51 am

Re: Itemdel

Post by francof » Thu Jul 03, 2014 2:52 pm

buongiorno Klaus, thanks for help.

I must better understand your suggestion, I tried it but, I've always the two items shuffled into item 1.... boh! the only thing I understood is: TAB don't work like itemDel.
can you explain me the function of numtochar(1) numtochar(2)?

Klaus
Posts: 14206
Joined: Sat Apr 08, 2006 8:41 am
Contact:

Re: Itemdel

Post by Klaus » Thu Jul 03, 2014 3:45 pm

Hi Franco,

I use numtochar(1) and numtochar(2) as a replacememtn for the "ususal" item and linedelimiter in a sql query,
because you cannot type these character with the keyboard and so it it almost impossible that these characters
are somewhere inside of the text of the different database fields.

As you experience, if the content of a database fields is MORE than ONE line, TAB will not work here, because
"repeat for each line..." will handle the second to last lines of that ITEM as separate lines, which will ruin everything :D

Therefore I use numtochar(2) as a linedelimiter!
This makes sure MULTI line text (seoparated by CR!) will still be one item.

And because the text of database fields can also contains TABs!, I also use a different ITEMDELIMITER. :D

Could you please post the database query script?


Best

Klaus

francof
Posts: 237
Joined: Fri Apr 11, 2014 10:51 am

Re: Itemdel

Post by francof » Thu Jul 03, 2014 4:51 pm

Hi Klaus,
you are the welcome
sure, below the command with the code.
if can help the column "DOMANDA" are in both tables, while "RISPOSTA" and "RISPOSTAESATTA" are the columns (of the same type) that make me crazy :(

Code: Select all

command databaseCercaArgomento
                   ## Query the database
   put getDatabaseID() into tDatabaseID
   put "SELECT DOMANDA, RISPOSTA from TBLDOMANDE_VARIE WHERE DOMANDA LIKE '%" &  field "txtTestoDaCercare" & "%'" & \
         "UNION ALL SELECT DOMANDA, RISPOSTAESATTA from TBLDOMANDE_TUTTE WHERE DOMANDA LIKE '%" &  field "txtTestoDaCercare" & "%'" into tSQL
      
   ## Optional, see below
put numtochar(1) into tItemDel
put TAB into tItemDel
put numtochar(2) into tLineDel
put revDataFromQuery(tItemDel,tLineDel,tDatabaseID,tSQL) into tRsTrovaDom
   
   if tRsTrovaDom is empty then
      answer "Non sono state trovate domande inerenti alla ricerca."  with "Ok"
      exit databaseCercaArgomento
   end if 
   
   ## Optional:
set itemDel to numtochar(1)
set linedel to numtochar(2)
   repeat for each line tLine in tRsTrovaDom
      put item 1 of tLine  & return after field "txtDomTrovate"
   end repeat
  
   answer tsql
   answer field "txtDomTrovate"
end databaseCercaArgomento
as I said before, very probably, I don't understood your code. so at present now, I used another column instead of "RISPOSTA" and "RISPOSTAESATTA".
I select a field of the DB named (NUMn) which contains an univocal number for each record of the DB, then with this field into another query I will select the corresponding "RISPOSTA" and "RISPOSTAESATTA" (....dei miei stivali).
....and here other trouble with a scrolling field I would like to use to display the data

you said:
Klaus wrote:.......I use numtochar(1) and numtochar(2) as a replacememtn for the "ususal" item and linedelimiter in a sql query,
because you cannot type these character with the keyboard and so it it almost impossible that these characters
are somewhere inside of the text of the different database fields..........
but.... :? if these character aren't in the text, how can I use to delimiter? how can it work :?:
....I do not doubt that it works :)

ciao
franco

Klaus
Posts: 14206
Joined: Sat Apr 08, 2006 8:41 am
Contact:

Re: Itemdel

Post by Klaus » Thu Jul 03, 2014 5:23 pm

Hi Franco,

you should try to understand how and why this technique with item- and line-delimiter actually works with "RevDataFromQuery"!

Here a corrected script, see my comments, I deleted parts of the script for readability:

Code: Select all

command databaseCercaArgomento
    ...
   ## Optional, see below
   put numtochar(1) into tItemDel
   
   ## Here you overwrite the previous set ITEMDELIMITER!
   ## DON'T! See below
   ## put TAB into tItemDel
   put numtochar(2) into tLineDel
   put revDataFromQuery(tItemDel,tLineDel,tDatabaseID,tSQL) into tRsTrovaDom
   
   if tRsTrovaDom is empty then
      answer "Non sono state trovate domande inerenti alla ricerca."  with "Ok"
      exit databaseCercaArgomento
   end if 
      
   ## Optional:
   ## This will not work if you have previously defined ANOTHER (TAB) itemdelimiter as you did above!
   set itemDel to numtochar(1)
   set linedel to numtochar(2)
   
   ## This hould work now!
    repeat for each line tLine in tRsTrovaDom
      put item 1 of tLine  & return after field "txtDomTrovate"
   end repeat
    ...
end databaseCercaArgomento
if these character aren't in the text, how can I use to delimiter? how can it work
It does work becuase these characters are NOT part of the content of the database fields!
If they WERE part, we could not use them as XXX delimiters!
You have experienced it here, if the content of a database field contains a CR, you cannot use
CR as a line delimiter when querying data!


Best

Klaus

francof
Posts: 237
Joined: Fri Apr 11, 2014 10:51 am

Re: Itemdel

Post by francof » Thu Jul 03, 2014 8:44 pm

buonasera Klaus,

obviously now, it's working.

this error had escaped me

Code: Select all

## Here you overwrite the previous set ITEMDELIMITER!
   ## DON'T! See below
   ## put TAB into tItemDel
...
but the important thing is that, in my mind I get the wrong idea that the delimiter character there must be!
I thought the TAB itemdel was used because at the end of the chunk ( the text of the column DOMANDA, or RISPOSTA) there was TAB! at present I still don' t now how it work.

yes, I should try to understand how delimiter works. belive me, before open this post I've searched for some guide or manual..... probably in the wrong manner.

ciao
franco

Klaus
Posts: 14206
Joined: Sat Apr 08, 2006 8:41 am
Contact:

Re: Itemdel

Post by Klaus » Fri Jul 04, 2014 11:39 am

Franco,

in a database you have FIELDS that hold the content and RECORDS (Rows) that keep sets of FIELDS.
Like this example of a simple address database:
Record 1:
name -> Miller
firstname -> John
age -> 40

Record 2:
name -> Smith
firstname -> Will
age -> 15
...
etc.

OK so far?

Now when you want to get ALL records (all fields of all rows) into Livecode to display you use
"select * from addresses" for the SQL command and "revdatafromquery" for the LC command.

Now HOW are the data organized when you retrieve them via Livecode?
Like this "en bloc":
MillerJohn46SmithWill15etc
???

No, the FIELDS and ROWs are of course DELIMITED, so we can differ the FIELDS and ROWs.

OK so far?

So we will get somethig like this when using the DEFAULT item and line delimiter:
Miller TAB John TAB 46 CR
Smith TAB Will TAB 15 CR
etc...

The DEFFAULT delimiters are TAB for delimiting the FIELDS and CR for delimiting the ROWs

OK so far?

But this will not work if the FIELDS of the database already contain CRs as in your initial problem!
Do you know why?

Same if the database fields content already contain TABs!
Do you know why?

If you DO know, then you get nearer to complete understanding of this technique :D


Best

Klaus

francof
Posts: 237
Joined: Fri Apr 11, 2014 10:51 am

Re: Itemdel

Post by francof » Fri Jul 04, 2014 4:27 pm

Hi Klaus, thanks for your time.
Klaus wrote:......
Record 1:
name -> Miller
firstname -> John
age -> 40
......

OK so far?
.........
Ok.
Klaus wrote: ....
Now HOW are the data organized when you retrieve them via Livecode?
Like this "en bloc":
MillerJohn46SmithWill15etc
???

No, the FIELDS and ROWs are of course DELIMITED, so we can differ the FIELDS and ROWs.

OK so far?
....
Ok.
Klaus wrote: ....
So we will get somethig like this when using the DEFAULT item and line delimiter:
Miller TAB John TAB 46 CR
Smith TAB Will TAB 15 CR
etc...

The DEFFAULT delimiters are TAB for delimiting the FIELDS and CR for delimiting the ROWs

OK so far?
....
Ok.... a little less,
I thought the delimiter character was already at the end of each field of the table. instead, if I understand what you say, the delimiter is set from the LC command by code.

Code: Select all

put numtochar(1) into tItemDel
put numtochar(2) into tLineDel
put revDataFromQuery(tItemDel,tLineDel,tDatabaseID,tSQL) into tRsTrovaDom
the code above don't say "GET DATA UNTIL THAT CHARACTER" but "SEPARATE DATA WITH THAT CHARACTER AND GIVE ME THE FIRST BLOCK" (item 1)
if the character was already in the table field....
Klaus wrote: ....
But this will not work if the FIELDS of the database already contain CRs as in your initial problem!
Do you know why?

Same if the database fields content already contain TABs!
Do you know why?
....
yes, I do hope, because the revdatafromquery LC command finding that character in the string goes in tilt and separates data when it should not.
the LC engine, knows where one field ends up. the delimiter character serves to me to use those data.

LC is all new for me, I used a little visual basic language, in which the reference to the tables fields is done by using the field names declared. something like this, straight from the query:

.................Recordset.name....Recordset.firstname....Recordset.age
record 1..........Miller.....................John.....................40
record 2......... Smith.....................Will......................15

to put the name into e text field:
textName.text = Recordset.name

well, due to my english I used an hour to reply, I hope to have written something understandable (not about concepts, but in grammar) :)

ciao
franco

Klaus
Posts: 14206
Joined: Sat Apr 08, 2006 8:41 am
Contact:

Re: Itemdel

Post by Klaus » Fri Jul 04, 2014 4:37 pm

Hi Franco,
the code above don't say "GET DATA UNTIL THAT CHARACTER" but "SEPARATE DATA WITH THAT CHARACTER AND GIVE ME THE FIRST BLOCK" (item 1)
ESATTO! :D
yes, I do hope, because the revdatafromquery LC command finding that character in the string goes in tilt and separates data when it should not.
the LC engine, knows where one field ends up. the delimiter character serves to me to use those data.
Esatto di nuovo!
visual basic...
to put the name into e text field:
textName.text = Recordset.name
That is cool, but LC does not work this way. 8)
well, due to my english I used an hour to reply, I hope to have written something understandable (not about concepts, but in grammar)
If I had to reply in italian, that would take a WEEK (minimum) :D

OK, looks like you finally got it!

And now you see why setting delimiters like numtochar(1 or 2) is really helpful, right?
Imagine you stored a complete letter with TABS and multiple line in ONE database entry, then you MUST use my above mentinoed technique, since we want to
get the content of the database 1:1 and I personally do not what that LC replaces some characters like CR mjst to make it fit into a LINE or ITEM! :D


Best

Klaus

francof
Posts: 237
Joined: Fri Apr 11, 2014 10:51 am

Re: Itemdel

Post by francof » Sat Jul 05, 2014 11:13 am

Hi Klaus,
Klaus wrote: ...
If I had to reply in italian, that would take a WEEK (minimum) :D

OK, looks like you finally got it!
...
me too for deutsch. I have to thank Saint Translator of Google :)

well, I got my data. this the final (I hope) code:

Code: Select all

command databaseCercaArgomento
 ....
 ....     
put numtochar(1) into tItemDel
put numtochar(2) into tLineDel
put revDataFromQuery(tItemDel,tLineDel,tDatabaseID,tSQL) into tRsTrovaDom
   
if tRsTrovaDom is empty then
   answer "Non sono state trovate domande inerenti alla ricerca."  with "Ok"
   exit databaseCercaArgomento
end if
      
set itemDel to numtochar(1)
set lineDel to numtochar(2)
   
   repeat for each line tLine in tRsTrovaDom
      put item 1 of tLine & return after field "txtDomTrovate"
      put item 2 of tLine & return after field "txtRispostaTrovata"
   end repeat
    ....
end databaseCercaArgomento
now I must use it.... I put the DB field "DOMANDA", is a set of questions, into a Scrolling Field "txtDomTrovate" and when I click on one of them (a line) I would to show the corresponding answer, the DB field "RISPOSTA", into another txt field.
the code above relative to item 2 is just to make a test... in this case into that txt field goes all item 2.
how can I refer to the correct item2 of the clicked item1?
I thought to put both items into se same object (Basic Table Fied or a Data Grid), hide (how) the column of item2 (the answers) and show only the answer (item2) corresponding to the clicked question (item1)

best
franco

Klaus
Posts: 14206
Joined: Sat Apr 08, 2006 8:41 am
Contact:

Re: Itemdel

Post by Klaus » Sat Jul 05, 2014 11:48 am

how can I refer to the correct item2 of the clicked item1?
Sorry, don't understand. Where and what item clicked?

francof
Posts: 237
Joined: Fri Apr 11, 2014 10:51 am

Re: Itemdel

Post by francof » Sat Jul 05, 2014 1:43 pm

Klaus wrote: Sorry, don't understand. Where and what item clicked?

Code: Select all

put item 1 of tLine & return after field "txtDomTrovate"
here I put all the values of item1 into a field called "txtDomTrovate", then I select one of these lines clicking by mouse..... I hope it is a viable way to show the corresponding item2 into another field on my card.

ciao
franco

Klaus
Posts: 14206
Joined: Sat Apr 08, 2006 8:41 am
Contact:

Re: Itemdel

Post by Klaus » Sat Jul 05, 2014 2:09 pm

Buongiorno Franco,

ah, you mean a LIST field! :D

That will not work this way, because your "item 2" may contain CRs, right?
If yes, then you cannot use LINES.

You could use a custom property, which is like a variable but is attached to a LC object.
Maybe something like this (out of my head):

Code: Select all

command databaseCercaArgomento
  ....
  ....     
  put numtochar(1) into tItemDel
  put numtochar(2) into tLineDel
  put revDataFromQuery(tItemDel,tLineDel,tDatabaseID,tSQL) into tRsTrovaDom
   
  if tRsTrovaDom is empty then
     answer "Non sono state trovate domande inerenti alla ricerca."  with "Ok"
     exit databaseCercaArgomento
  end if
      
  set itemDel to numtochar(1)
  set lineDel to numtochar(2)

  ## We will first collect the data, see below
  put empty into tAllItems2
   
   repeat for each line tLine in tRsTrovaDom
      put item 1 of tLine & return after field "txtDomTrovate"

      ## Collect all ITEM2s and store it in a custom property of fld "txtDomTrovate"
      ## We also need another LINE delimiter for these data:
      put item 2 of tLine & numtochar(2) after tAllItems2
   end repeat

   ## Clean up all data, also in your list fld "txtDomTrovate", so the user cannot click an EMPTY line
  delete last char of fld "txtDomTrovate"
  delete last char of tAllItems2

  ## Now se tteh custom property, so we can later access it:
  set the cAllItems2 of fld "txtDomTrovate" to tAllItems2
    ....
end databaseCercaArgomento
Then in the script of your list field "txtDomTrovate" do this:

Code: Select all

on mouseup

  ## What line has been clicked:
  put the hilitedline of me into tLineNumber

  ## Prepare to access custom property:
  set linedel to numtochar(2)
  
  ## Now display the corresponding ITEM 2 in another field:
  put line tLineNumber of the cAllItems2 of me into fld "whereever you want to display this info"
end mouseup
Best

Klaus

francof
Posts: 237
Joined: Fri Apr 11, 2014 10:51 am

Re: Itemdel

Post by francof » Sat Jul 05, 2014 4:35 pm

accidenti Klaus, in a flash you've solved all :D

not even in two lives I could have to do it. it is useless to tell you that all work fine :D
This experience was very helpful to me to learn new things.

I'll see you at the next
best
franco

Post Reply