Hiya folks.
I have a MySQL operation that comes back with an array of 3-item arrays. Is there any way I can check to see what it is? Not much feedback outside the last entry into the master array, so the main array is hidden somewhat. I can’t figure out how to see the thing.
Any advice appreciated.
Cheers
Could you show us the code, or part of it? log command is normally the way to show contents of a variable while running the script. You need to open the event log of script editor to see it.
How do you connect to MySQL? Using the command line, external application or an osax?
I think you’re mistaken what I’m asking. The MySQL is just fine. The splitting of records and fields is just fine. I iterate through these and shove them into the following array:
{
{r1f1, r1f2, r1f3}
{r2f1, r2f2, r2f3}
{r3f1, r3f2, r3f3}
}
The problem is that I can’t see the final array. I’d like to, even if it’s in the results. I am not familiar with the log command.
Actually I just found a way. Set another variable to the master array. It shows up in Results.
Well there you go.
You know of any more elegant solution without creating another variable?
Cheers
We are some people here interested in a disclousure on this, for different reasons, I for instance, would like to learn how to do it, in a way that obviously works for you. (I don’t need to know your real database or table names, or any passwords, I just want to learn how you do it. 
Thanks.
Sorry for the late reply. I’m having Internet issues. Certainly, I will post the concept and code.
Essentially I wanted a way to record the current date, the title and URL of a website that I wanted to archive in a list. MySQL was a go-to for this. Seeing that I use Safari, I would need an AS hook.
To write to the database, I have that 3-field database on my workstation. I write using this AS which I call with Launchbar:
tell application "Safari"
set myU to the URL of the current tab in window 1
set myT to the name of the current tab in window 1
if myT begins with "â–¶ " then
-- Youtube Title leader for playing videos - take this stuff out
set myT to (characters 3 thru end of myT) as text
end if
end tell
set mysql_db to "myDB"
set mysql_table to "urls"
set mysql_user to "myUsername"
set mysql_host to "localhost"
set mysql_pw to "myPassword"
set myStatement to "/usr/local/mysql/bin/mysql -u " & mysql_user & " -h " & mysql_host & " -p" & mysql_pw & " -e \"INSERT INTO " & mysql_db & "." & mysql_table & " (title, url, creation_date) VALUES ('" & myT & "','" & myU & "', now())\";"
do shell script myStatement
Once in a while, I’d like those records returned to me so I can do something with them. I want to take the single string return which is delimited by “\r” and “\t” for records and fields, and shove them into my own array. Each entry is a record. Each entry is an array itself, of the fields returned of creation_date, url and title.
set mysql_db to "myDB"
set mysql_table to "urls"
set mysql_user to "myUsername"
set mysql_host to "localhost"
set mysql_pw to "myPassword"
set statement to "/usr/local/mysql/bin/mysql -u " & mysql_user & " -h " & mysql_host & " -p" & mysql_pw & " -e \"SELECT creation_date, url, title FROM " & mysql_db & ".urls ORDER BY id ASC\";"
set results to do shell script statement
set entries to splitString(results, "
")
set finished to {}
repeat with i in entries
set innerItems to splitString(i, " ")
set end of finished to innerItems
end repeat
-- take out first record as it is solely fieldnames
set final to rest of finished
set a to final
set textOut to ""
set counter to 0
-- build final text
repeat with i in final
set counter to counter + 1
set textOut to textOut & ((counter as text) & ". " & ((first item of i as text) & ": <a href=\"" & second item of i as text) & "\" target=\"_blank\">" & third item of i as text) & "</a>
"
end repeat
-- provide a method for final text viewing
set b to textOut
on replace_chars(this_text, search_string, replacement_string)
set AppleScript's text item delimiters to the search_string
set the item_list to every text item of this_text
set AppleScript's text item delimiters to the replacement_string
set this_text to the item_list as string
set AppleScript's text item delimiters to ""
return this_text
end replace_chars
to splitString(aString, delimiter)
set retVal to {}
set prevDelimiter to AppleScript's text item delimiters
log delimiter
set AppleScript's text item delimiters to {delimiter}
set retVal to every text item of aString
set AppleScript's text item delimiters to prevDelimiter
return retVal
end splitString
I go from there to create a BBEdit file.
Any questions, feel free to ask.
Cheers
On the command line you can use the option --skip-column-names which will not print the headers of the column. When I want HTML code I use concat and/or concat_ws functions in MySQL to print out in HTML form so I don’t have to do that in AppleScript. Your code could look like:
set exec to "/usr/local/mysql/bin/mysql --skip-column-names"
set query to "SET @ResultRowNr = 0;
SELECT
CONCAT(@ResultRowNr:=@ResultRowNr+1,
\". \",
`creation_date`,
\": <a href=\\\"\",
`url`,
\"\\\" target=\\\"_blank\\\">\",
`title`,
\"</a>\")
FROM
`urls`"
set exec to exec & " -u root" --set username
set exec to exec & " -h localhost" --set server
set exec to exec & " -p<fill in your password>" --set password
set exec to exec & " -D urls" --set db
do shell script exec & " <<<" & quoted form of query
Hi there. Ya this is the first time I have used the command line to go in for data.
Well, therefore it was important for me to know which client you were using
Here a solution that will create a complete HTML file and opens the file with Safari from the /tmp folder.
set tmpFile to do shell script "echo /tmp/html_mysql$$.html"
set exec to "/usr/local/mysql/bin/mysql --skip-column-names"
set query to "SET @ResultRowNr = 0;
SELECT
CONCAT(@ResultRowNr:=@ResultRowNr+1,
\". \",
`creation_date`,
\": <a href=\\\"\",
`url`,
\"\\\" target=\\\"_blank\\\">\",
`title`,
\"</a></br>\")
FROM
`urls`"
set exec to exec & " -u root" --set username
set exec to exec & " -h localhost" --set server
set exec to exec & " -p<fill in your password>" --set password
set exec to exec & " -D urls" --set db
set HTMLCode to do shell script exec & " <<<" & quoted form of query & " > " & tmpFile
do shell script "open " & tmpFile & " -a Safari"
OK, it was AppleScript. The question was about the parsing of text into arrays and viewing them, instead of using AppleScript as a client to generate static pages. There’s a whole myriad of choices that I’ve been using over a decade to do that instead of relying on AppleScript. In fact I found it hilarious that the command line could return mysql data.
Thanks for sharing, dabee!
I was thinking that the return might be being coerced to linefeed.
A do shell script with altering line endings (default true) will coerce the line feeds into return and will strip the last linefeed. Splitting the rows using return as text item delimiter is actually correct.