Re: A bit of philosophy about Database events
Re: A bit of philosophy about Database events
- Subject: Re: A bit of philosophy about Database events
- From: Jeff Jungblut <email@hidden>
- Date: Wed, 17 Mar 2010 08:48:03 -0700
- Thread-topic: A bit of philosophy about Database events
Title: Re: A bit of philosophy about Database events
on 3/16/10 4:01 PM, Stockly, Ed at email@hidden wrote:
>> I haven't heard of MySQL3.app, do you mean MacSQL? I used to use MacSQL
>
> May have been referring to SQLite3...
>
> Run this AppleScript to see the man page:
>
> set manFile to (path to documents folder as Unicode text)
> set manFile to manFile & "sqlite3-ManPage.pdf"
> set manFilePath to quoted form of POSIX path of manFile
> do shell script "/usr/bin/man -t sqlite3 | /usr/bin/pstopdf -i -o " & manFilePath
> tell application "Preview" to open (file manFile as alias)
Run this AppleScript to see my response:
set theResponse to mysql_query("SELECT message FROM messages WHERE recipient = 'Ed Stockley'")
display dialog (theResponse as text)
on mysql_query(theQuery)
tell application "http://cleverdev.net/xmlrpc/xmlrpc.php"
set theData to call xmlrpc {method name:"myApp.doQuery", parameters:{"mySuperSecretPassword", theQuery}}
end tell
return theData
end mysql_query
I have experience in moving data from Quark to MySQL for web layout, or from MySQL to Quark for print layout, using XML-RPC and PHP. I didn't give much detail in my last post about how, and when I started these projects back in 2003 I wish I'd seen some sample code to understand how simple it can be instead of pulling my hair out working around quirks in the few scriptable database clients that are out there. Seeing that you work at a newspaper, you probably already know this stuff, but for the benefit of others....
On the AppleScript side, here's a basic query:
property rpcAPIKey : "mySuperSecretPassword"
property rpcServer : "http://myniftyserver.com/privateapi/xmlrpc.php"
-- insert something
mysql_query("INSERT INTO employees (firstname, lastname) VALUES ('" & escapeSQL(firstName) & "', '" & escapeSQL(lastName) & "')")
-- get a table as a list of lists of strings
set theResult to mysql_query("SELECT firstname, lastname, department, title FROM employees ORDER BY lastname")
-- These handlers do all the database work.
-- Queries are passed to a PHP script using XMLRPC
-- and results are returned as an AppleScript list of lists.
on mysql_query(theQuery)
set RS to my callServerMethod("doQuery", {rpcAPIKey, theQuery})
return RS
end mysql_query
on callServerMethod(methodName, params)
using terms from application "http://localhost/"
tell application rpcServer
set theData to call xmlrpc {method name:"myApp." & methodName, parameters:params}
end tell
end using terms from
try
return theData
on error
return false
end try
end callServerMethod
on escapeSQL(theText)
set newText to theText
set backslash to "\\"
set singlequote to "'"
set doublequote to "\""
-- Oh, heh heh, this syntax requires Satimage.osax. Rewriting in pure applescript is left as an exercise for the reader
set newText to change backslash into backslash & backslash in newText
set newText to change singlequote into backslash & singlequote in newText
set newText to change doublequote into backslash & doublequote in newText
return newText
end escapeSQL
Here’s the PHP code in the file xmlrpc.php:
<?php
// Download the Incutio XML-RPC Library (IXR.php) from
// http://scripts.incutio.com/xmlrpc/
require_once('IXR.php');
// Connect to our database
$db_host = "localhost"; // or the mysql hostname:port you wish to connect to
$db_user = "my_database_user_name";
$db_pass = "my_database_password";
$db_name = "myapp";
$db_ptr = mysql_pconnect($db_host, $db_user, $db_pass) or exit();
mysql_select_db($db_name, $db_ptr) or exit();
// Create the server and map the XML-RPC method names to the relevant functions
$server = new IXR_Server(array(
'myApp.doQuery' => 'doQuery',
'myApp.getTime' => 'getTime'
));
// Do a SQL query and return the result set as a list of lists
function doQuery($args) {
// queries require a password, for some outward appearance of security
$rpcAPIKey = 'mySuperSecretPassword';
$clientKey = array_shift($args);
$query = array_shift($args);
if ($clientKey == $rpcAPIKey) {
return listFromResult(mysql_query($query));
} else {
return new IXR_Error(-6502, 'Invalid API key');
}
}
// Return result set as a list of lists
function listFromResult($result) {
$data = "">
while ($row = mysql_fetch_row($result)) {
$data[] = $row;
}
return $data;
}
// Simple method you can call just to test if the xmlrpc connection is working
function getTime($args) {
return date('H:i:s');
}
?>
--
Jeff Jungblut
email@hidden
Senior Graphic Designer & Web Developer
Uptown Publications
_______________________________________________
Do not post admin requests to the list. They will be ignored.
AppleScript-Users mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
Archives: http://lists.apple.com/archives/applescript-users
This email sent to email@hidden