MySQL Select Query and AppleScript

Scripting Forums AppleScript Studio & Xcode
,
#1

Hello, i need to put my database information
in an applescript application, i create the database with
AppleScript mysql interface.

Someone can help me?
Greets!

#2

Help you do what? You’ll have to be more specific about the interface you are using. Is it ktam’s? is it an osax?

#3

Ooops sorry i miss it,
the interface is ktam

Greets

#4

i try to explain much more… :slight_smile:
i have a database with many records and many field,
ok now i want put all of this in an applescript’s gui.
i think to use a mysql’s command like “Select”

for example:
SELECT * FROM table WHERE id=1

Greets and hope some people can help me!

#5

alberto:

I use do shell script with command line MySQL commands. When doing so, you have to define a user (and a password if necessary) with each command you send to the shell. For instance, to select an entire table’s contents, this works:

set the_table to "drivers"
(do shell script "/usr/local/mysql/bin/mysql -u root " & " -D " & "'test'" & " -e \"" & "SELECT * FROM " & "\\`" & the_table & "\\`" & "\"")

In this case, the name of the database is test and I am using a variable to hold the name of the desired table in the database. I am accessing the database as the root user, which is usually fine for desktop applications. In my case, I have not set a password for the root user, but if it is needed, you must format it like this:

-u root -pYOURPASSWORD

without any spaces. The command line that this do shell script generates looks like this:

"/usr/local/mysql/bin/mysql -u root  -D 'test' -e \"SELECT * FROM \\`drivers\\`\""

and results in data that looks like this:

"driver_id name 1 Dadman 2 Weems"
Which can be easily parsed into list format, or however you want.

I got most of this information using code written by ktam. His website is here and you can download his code here.

For more detailed SELECTS, this is what I use:

set the_table to "artists"
set gid to "Willie Nelson"
set cur_art to (do shell script "/usr/local/mysql/bin/mysql -u root " & " -D " & "'dvd2sql'" & " -e \"" & "SELECT artist_id FROM " & "\\`" & the_table & "\\`" & " WHERE artist = '" & gid & "'" & "\"")

Which generates this:

"artist_id 1560"
I have also built in a few SELECTS using the LIKE parameter:

set search_string to "cash"
set sss to ("'%" & search_string & "%'")
set b to do shell script ("/usr/local/mysql/bin/mysql -u root " & " -D " & "'dvd2sql'" & " -e \"" & "SELECT artist from artists where artist LIKE " & sss & "\"")
set c to paragraphs 2 thru -1 of b as list
-->{"Johnny Cash", "Johnny Cash, The Carter Family"}

By setting c to only paragraphs 2 through -1 of the returned data as a list, you get rid of the table header and just the selected information.

Good luck, I hope this helps. I have been working on two AS Studio projects lately that are no more than GUI interfaces to MySQL databases on my machines, and am happy to help any way I can.

#6

Hi Craig,
the query works fine i use it:

set theResult to do shell script ("/usr/local/mysql/bin/mysql -u administrator -ppassword " & " -D " & "'db'" & " -e \"" & "SELECT ind_immo from vendita where rif LIKE " & sss & "\"")

but i try to rid of the table header and xcode give me this error NSInternalScriptError (8) at line

set c to paragraphs 2 thru -1 of theResult as list

why? where is the problem?

Greets!

#7

Wow. I do not know. That method has always worked for me. Have you double checked that your variable theResult does indeed contain the data from the table that you searched for?

I always put the set c to paragraphs 2 thru -1 immediately after the do shell script in the same handler. Are you sure the error is from this command, and not something afterwards?

#8

if i delere this command works fine but i have the table header…
all code is in same handler immediatley after the query…
i don’t know… :frowning:

#9

I have another idea. Do you know if any other part of your project alters AppleScript’s text item delimiters? The only other reason I can imagine that the command does not work is if the theResult contains no paragraphs at all. Try sending theResult to TextEdit or some other editor and see what format theResult exhibits.

#10

mmm how i do that, sorry but i don’t konw. many thanks for all…

ok i do that and this is the output…

#11

ind_immo
via rosmini

yes in a part of my project i must alter text item delimiters…

#12

There may be a problem when you set your delimiters back to the original delimiters, or it may be that you are extracting data from MySQL before the delimiters are set back to the original. To test it, use this:

tell application "TextEdit"
	make new document
	set text of front document to theResult
end tell

instead of set c to paragraphs 2 thru -1 of theResult. A TextEdit document will come up with the raw information from the do shell script. You can then examine the text to see why the paragraphs function may not be working.

#13

ok i adjust the delimiters, the out output is something like that

001.V name surname corso milano, 45 2 3 palazzo

and i wont this

001.V ; name ; surname ; corso milano, 45 ; 2 ; 3 ; palazzo

because if the fiels’s value is delimited i can extract the information and put this in a text field…

001.V in a text field with name rif ecc ecc…

if you have question write to me :slight_smile:

Greets