How to have newly created database read into code

[Bellballer1]

Hello all! Any help with this would be greatly appreciated.

I created a database in my stack script, using DB Browser for SQLite, and I need the table to be read by the code in the substack where I have the google Maps API. The first column of the table is "Location name", the next 2 columns are the longitude & latitude , and the final column is the zIndex. I have the global variables "gDatabaseID" and "gLocationList". I query the database and put the result into gLocationList.

function databaseGetLocations
put revDataFromQuery(tab,return,gDatabaseID,tCreateListSQL) into gLocationList
return gLocationList
end databaseGetLocations


In my substack for the google Maps API, I use the command "updateCard" to put gLocationList into sLocations. The code in the google maps api substack is below:

function getMarkerCode pName
if sMarkers[pName] is empty then
return "error: no such marker."
else
local tMarkerCode, tPlaceJson, tPlaceArray

--put "for (var i = 0; i < locations.length; i++) {"
--put "var myLatLng = new google.maps.LatLng(sLocations[1], sLocations[2]),"; ## when I use this code, I don't get any results
put "var " & pName & " = new google.maps.Marker({" & return into tMarkerCode
put "icon: 'https://chart.googleapis.com/chart?chst ... tter&chld=' + i + '|FE6256|000000|'," & return after tMarkerCode ## want zIndex # to show in Marker

put "position: new google.maps.LatLng(" & sMarkers[pName]["pos"] & ")," & return after tMarkerCode
--put "position: myLatLng," & return after tMarkerCode ## would like for the position to be read from "myLatLng" instead of from position code on live above
put "map: map," & return after tMarkerCode
--put "title: sLocation[0]," & "'" & return after tMarkerCode ## would like this to come from database table
put "title:'" & sMarkers[pName]["title"] & "'," & return after tMarkerCode
--put "zIndex: sLocations[3]," & return after tMarkerCode ## would like this to come from database table
put "});"& return after tMarkerCode

put getPlaceByLatLong(sMarkers[pName]["pos"]) into tPlaceJson
put jsonToArray(tPlaceJson) into tPlaceArray
##put tPlaceArray["results"][1]["formatted_address"]
put "(function (marker) {"&return after tMarkerCode
put "google.maps.event.addListener(marker, 'click', function (e) {"&return after tMarkerCode
put "infobox.setContent('<div id=" & quote & "infobox" & quote & " >'+'<p>'+" & quote & tPlaceArray["results"][1]["formatted_address"] &quote & "+'</p>'+'</div>');" & return after tMarkerCode
put "infobox.open(map, marker);"&return after tMarkerCode
put "markCentered(marker.position);"&return after tMarkerCode
put "});" & return after tMarkerCode
put "})(" & pName & ");"&return after tMarkerCode

return tMarkerCode
end if
end getMarkerCode

The goal is to only have to update the database tables so that the results flow automatically into tMarkerCode, and for the markers to have distinct labels (e.g. 1, 2, 3, etc.). Also want the titles and positions to come from whats in the database tables.

[quailcreek]

Your query to the DB should look something like this. Then you would parse the items in tData.

Code: Select all

put "SELECT theLatitude, theLongitude, zIndex FROM MyTable" into tSQLStatement
put revDataFromQuery(tab,cr, gDatabaseID ,tSQLStatement) into tData

set the itemDel to tab
put item 1 of tData into tLatitude
put item 2 of tData into tLongitude
put item 3 of tData into tzIndex

set the itemdel to comma

[Bellballer1]

Thanks for the response quailcreek!

Do you mean something like this?:

function databaseGetLocations

put "SELECT Locations.Location,Locations.lat,Locations.lng,Locations.zIndex FROM Locations" into tCreateListSQL
put revDataFromQuery(tab,return,gDatabaseID,tCreateListSQL) into tData

set the itemDel to tab
put item 1 of tData into tLocation
put item 2 of tData into tlat
put item 3 of tData into tlng
put item 4 of tData into tzIndex

set the itemDel to comma

end databaseGetLocations

If so, in my substack for the google maps API, how do I get the values of tLocation, tlat, tlng, and tzIndex into the proper place, since it's my understanding that "t=temporary" and is only being used in the stack script?

[quailcreek]

You really shouldn't have a . in the header name. It will cause you endless misery.
The t is for temp but it can be used anywhere. It's a way of not only identifying a temp variable but also for making sure you don't use a special word like "location" as a variable name. tLocation is OK as a variable container, but the word location is used by LC.

Code: Select all

put "SELECT headerName1, headerName2, headerName3 FROM MyTable" into tSQLStatement
put revDataFromQuery(tab,cr, gDatabaseID ,tSQLStatement) into tData

Your going to need this link.
http://www.w3schools.com/sql/sql_quickref.asp

[Bellballer1]

Hopefully this is my last question (smile).

Per quailcreek's suggestions, I changed the header name to the following to get rid of the periods:

function databaseGetLocations
put "SELECT Address, Latitude, Longitude, zIndex FROM Sites;" into tSQLStatement
put revDataFromQuery(tab,return,gDatabaseID,tSQLStatement) into tData

set the itemDel to tab
put item 1 of tData into tAddress
put item 2 of tData into tLatitude
put item 3 of tData into tLongitude
put item 4 of tData into tzIndex

set the itemDel to comma
end databaseGetLocations

But the part that continues to confuse me is how to get the tData items into my code? I still cannot seem to get the code to read the values from the database. Below is the code that I've been using and also code that I would like to use (i.e. the lines that start with "--" is what I would like to use, but the values from the database are not being read.

function getMarkerCode pName
if sMarkers[pName] is empty then
return "error: no such marker."
else
local tMarkerCode, tPlaceJson, tPlaceArray, tAddress, tLatitude, tLongitude, tzIndex

--put "var myLatLng = new google.maps.LatLng(tLatitude, tLongitude),";
put "var " & pName & " = new google.maps.Marker({" & return into tMarkerCode
put "icon: 'https://chart.googleapis.com/chart?chst ... tter&chld=' + i + '|FE6256|000000|'," & return after tMarkerCode

put "position: new google.maps.LatLng(" & sMarkers[pName]["pos"] & ")," & return after tMarkerCode
--put "position: myLatLng," & return after tMarkerCode
put "map: map," & return after tMarkerCode
--put "title: tAddress," & "'" & return after tMarkerCode
put "title:'" & sMarkers[pName]["title"] & "'," & return after tMarkerCode
--put "tzIndex," & return after tMarkerCode
put "});"& return after tMarkerCode

put getPlaceByLatLong(sMarkers[pName]["pos"]) into tPlaceJson
put jsonToArray(tPlaceJson) into tPlaceArray
##put tPlaceArray["results"][1]["formatted_address"]
put "(function (marker) {"&return after tMarkerCode
put "google.maps.event.addListener(marker, 'click', function (e) {"&return after tMarkerCode
put "infobox.setContent('<div id=" & quote & "infobox" & quote & " >'+'<p>'+" & quote & tPlaceArray["results"][1]["formatted_address"] &quote & "+'</p>'+'</div>');" & return after tMarkerCode
put "infobox.open(map, marker);"&return after tMarkerCode
put "markCentered(marker.position);"&return after tMarkerCode
put "});" & return after tMarkerCode
put "})(" & pName & ");"&return after tMarkerCode

answer tMarkerCode

return tMarkerCode

end if
end getMarkerCode

[Klaus]

Hi Bellballer1,

just fetch the data form the db when you need it!

1. Use your first version of your function:

Code: Select all

function databaseGetLocations 
   put revDataFromQuery(tab,return,gDatabaseID,tCreateListSQL) into gLocationList 
   return gLocationList
end databaseGetLocations

You should add some error checking in your database calls however!

2. The get the data when needed, no need to use local or globakl variables:

Code: Select all

function getMarkerCode pName
   if sMarkers[pName] is empty then
     return "error: no such marker."
   end if

   ## NOW!
   put databaseGetLocations() into tData
   set the itemDel to tab
   put item 1 of tData into tAddress
   put item 2 of tData into tLatitude
   put item 3 of tData into tLongitude
   put item 4 of tData into tzIndex

   ## Carry on with your script and use the above filled variables:
   put "var " & pName & " = new google.maps.Marker({" & return into tMarkerCode
   ...

Sounds logical when you see it, right?


Best

Klaus

[Bellballer1]

Thanks Klaus!

That is very logical! Whenever I find the right solution in LiveCode, it always seems easier than I made it out to be.

I did throw in some error checks, and I now know that the correct data and values are being carried over properly (e.g. the proper values in tAddress, tLatitude, etc.). Now I just have to write the script properly so that the variables show up correctly. For instance:

put "title: tAddress" & return after tMarkerCode ## nothing happens when I use this code.

I will continue to play around with the script so that it works properly, especially since I know the right values are in tAddress, etc. I feel I must be making another simple mistake (once again).

[Klaus]

Hi Bellballer1,

...
put "title: tAddress" & return after tMarkerCode
## nothing happens when I use this code.
...

not surprising, you are passing the STRING tAddress and not the value of that variable!

Do like this:
...
put "title:" & tAddress & CR after tMarkerCode
## Save some time and simply use CR instad of return
...

Best

Klaus

[Bellballer1]

Hi Klaus!
Thank you for everything! Your tips have worked, no surprises there! Now just 2 minor questions (smile).

I have 2 lines of data in my database table, but right now only the first line is being read. Do I set the itemDel to "repeat", so that the next line from the table is read? Again, here is what I have so for (where only the 1st line of the table is being read):

put databaseGetLocations() into tData
set the itemDel to tab
put item 1 of tData into tAddress
put item 2 of tData into tLatitude
put item 3 of tData into tLongitude
put item 4 of tData into tzIndex

I need it to loop through and read the next line for the next location (i.e. the next address, lat/long and zIndex).

Question 2 involves the zIndex. The zIndex is a number (numbers 1 and 2, and maybe more numbers later), that I want to appear inside my location marker on the map. I am using this code:

put "icon: 'https://chart.googleapis.com/chart?chst ... tter&chld=' + i + '|FE6256|000000|'," & return after tMarkerCode

However, I'm not quite sure how to get the zIndex number into the marker. I've searched stock overflow for examples, which I have found, but its not working for me. Any tips would be greatly appreciated. Thanks again! Hope I'm not being too much of a pain (smiling again).

[Klaus]

Hi Bellballer1,

I have 2 lines of data in my database table, but right now only the first line is being read..

you mean you have 2 RECORDS in your database, right? Speaking the same language is really helpful
Hm, your SQL:

Code: Select all

...
put "SELECT Locations.Location,Locations.lat,Locations.lng,Locations.zIndex FROM Locations" into tCreateListSQL
put revDataFromQuery(tab,return,gDatabaseID,tCreateListSQL) into gLocationList 
...

will actually fetch ALL records from that table, so you should do something like this.
I use an array, here, easier to read and maintain, since we do not know how many "tAddress" etc. variables we might need!

Code: Select all

...
put databaseGetLocations() into tData
## With the above mentioned SQL, tData will contain ALL records from that table, separated by CR
put empty into tLocArray
put 1 into tCounter
set the itemDel to tab

repeat for each line tLine in tData
  put item 1 of tLine into tLocArray[tCounter]["Address"]
  put item 2 of tLine into tLocArray[tCounter]["Latitude"]
  put item 3 of tLine into tLocArray[tCounter]["Longitude"}
  put item 4 of tLine into tLocArray[tCounter]["zIndex"]
  add 1 to counter
end repeat
...

Now you can access the different Adresses, Latitudes etc like:
...
put tLocArray[1]["Address"] into tAddress1
...
or use it directly:
...
put "title:" & tLocArray[1]["Address"] & CR after tMarkerCode
...
You get the picture.

Question 2 involves the zIndex. The zIndex is a number (numbers 1 and 2, and maybe more numbers later), that I want to appear inside my location marker on the map. I am using this code:
...
put "icon: 'https://chart.googleapis.com/chart?chst ... tter&chld=' + i + '|FE6256|000000|'," & return after tMarkerCode
...

I thought I already explained this?
I think the number you are talking about in this example is -> FE6256|000000
Then you can:
...
put "icon: 'https://chart.googleapis.com/chart?chst ... tter&chld=' + i + '|" & tLocArray[1]["zindex"] & "|'," & return after tMarkerCode
...

Best

Klaus

[Bellballer1]

Klaus you are beautiful!!

All of your tips have worked! The RECORDS in my database table are now being read properly (and it helps to speak the same "language"...sorry about that). Like you said in an earlier post, it all sounds logical when I see it.

The 2nd part of my question from yesterday actually involved the number (in this case, the"zIndex") that goes inside the marker. It now looks like this:

put "icon: 'https://chart.googleapis.com/chart?chst ... tter&chld= " & tLocArray[1]["zIndex"] & "|FE6256|000000|'," & CR after tMarkerCode

The only trouble I am having with this is it's using the zIndex from the last record in the table for each marker (i.e. all of my markers appear on the map with the same number (zIndex)). The zIndex from the 1st record in my database is not being saved in the marker. I suspect it has something to do with where I place the "add 1 to counter" and "end repeat" command. I will figure it out though!

Thanks tremendously for your help!!

[Klaus]

Hi Bellballer1,

Klaus you are beautiful!!

thank you for your kind words, but I already have a steady girlfriend!


Best

Klaus