Connecting to MySQL from PhotoShop/Bridge via JavaScript

Documentation, Reference material and Tutorials for Photoshop Scripting

Moderators: Tom, Kukurykus

Patrick

Connecting to MySQL from PhotoShop/Bridge via JavaScript

Post by Patrick »

I just thought I would post a recent project I did where I was required to query a MySQL database from a Adobe Bridge JavaScript (you can easily apply these examples and snippits to a PhotoShop JavaScript with little/no code alterations). There are many, many ways to accomplish what I did -- I am not claiming this is the best (in fact, I am quite certain this is a bit of a backwards solution ), but it worked for me and as a result I use it daily. Hopefully it can act as a starting point for others looking to use database connectivity in their scripts.

The Problem: On a daily basis, I am responsible for adding meta keywords to hundreds of images. We had an existing MySQL database that had all the information needed to generate acceptable keywords for the images, but to date I had been adding the keywords by hand from Adobe Bridge CS2. My life would be much easier if I could automate the process of adding keywords, but Adobe JavaScript has no built in functions to access a database.

My Proposed Solution: I have been learning PHP casually over the last couple years, so I know that if I can get the JS to load a PHP file, I can access the DB this way. Thanks to xbytors great information on executing batch files from JS, I decided to have the JS generate a batch file everytime it is run that would execute a PHP script. When the PHP script is executed, its result would be a CSV file with the information that the DB query returns. I would then have JS read in the CSV file to get the keywords I need to use later in the JS.

Conceptually it is pretty simple: JS first creates a .bat file that runs a PHP script, that PHP script querys a MySQL db and creates a .csv file with the results, and then the JS reads in the .csv file to see the results.

Side Note - My Setup: I did this all on a Windows XP Pro. I also have PHP/Apache installed on my machine locally. In order for this exact solution to work like I am doing it, you will need to install PHP on your computer (the .bat files I generate use php from the command line). Outside of PHP, I don't believe anything else special is required - this should work in any version of Windows.

The Code:

First thing I want to do is generate my .bat file. I want it to switch to the directory that I want to save the results .csv file and run the script, so it will look like this:


BAT
Code: Select allmode 60,10
@echo off
ECHO Retrieving keywords from MySQL...
c:
cd c:\metadata\
php keywords.php searchword

Here is my JavaScript code to create and execute this .bat file:


JavaScript
Code: Select all// reference the csv
var txt = new File("/c/metadata/keywords.txt");
// reference batch file
var bat2 = new File("/c/metadata/mysql.bat");

// delete the old txt
if (txt.exists) {
   txt.remove();
};

// delete the old bat
if (bat2.exists) {
   bat2.remove();
};


// write new batch file
bat2.open("w");
bat2.writeln("mode 60,10");
bat2.writeln("@echo off");
bat2.writeln("ECHO Retrieving keywords from MySQL...");            
bat2.writeln("c:");
bat2.writeln("cd c:\\metadata\\");
bat2.writeln("php keywords.php " + style);
//bat.writeln("pause");bat2.writeln("exit");
bat2.close();

// delay execution if batch file isnt on disk yet   
if (!bat2.exists) {
   $.sleep(250);
};
    
// run batch file
bat2.execute();

After this part of the script is executed, we are running a PHP script from MySQL that generates the results. Here is how my PHP script looked:


PHP
Code: Select all<?php

// get style # passed from cmd line
$style = $_SERVER['argv'];

// open mysql connection
$link = mysqli_connect("0.0.0.0", "user", "pass", "db");

// check mysql connection
if (!$link) {
   printf("Connect failed: %s\n", mysqli_connect_error());
   exit();
};

// establish MySQL query
$query = "SELECT `FIELD` FROM `TABLE`
WHERE `FIELD` like '".$style[1]."';";


// send query
if($result = mysqli_query($link, $query)) {

   // define file location
   $ourFileName = "../../../metadata/keywords.txt";
   // open file
   $fh = fopen($ourFileName, 'w') or die("can't open file");

   // retrieve results
   while ($row = mysqli_fetch_array($result, MYSQLI_NUM)) {
      // write to file
      fwrite($fh, $row[0] .",");   // site(s)
      fwrite($fh, $row[1] .",");   // category(s)
      fwrite($fh, $row[2]);      // vendor
   };
   
   // close file
   fclose($fh);
   
};

?>

This PHP script uses the keyword passed from the command line execution of the PHP script as the base for the query. Once it is finishes, it creates the .csv with the results.

Now back in the JavaScript, I put this code under the earlier code that created the .bat file:


JavaScript
Code: Select alltxt.open("r");    // open file
var keywords = txt.read();
txt.close();    // open file

var kchunks = keywords.split(",");

This reads in the .csv file I mad (I named it .txt in my script, but it is comma seperated). The result is the variable kchunks being an array of all the results of my query.

That is pretty much it. If you have any questions about this process feel free to ask, it is a bit akward but it gets the job done.

HTH,
Patrick
Andrew

Connecting to MySQL from PhotoShop/Bridge via JavaScript

Post by Andrew »

Hi Patrick

Is there that much benefit from using php. Can you not execute the mySQL commands from the bat file and get mySQL to write the results to a text file which JS can read?

Andrew
Patrick

Connecting to MySQL from PhotoShop/Bridge via JavaScript

Post by Patrick »

Hi Andrew, to be honest I do not know how to do a MySQL query that outputs a text file - I bet it can be done, and it would be a very good alternative. The main reason I chose PHP is I was already familiar with it, so it was the most convenient choice at the time.

Doing it directly from MySQL would save you from having PHP installed, but you would need to have MySQL installed locally instead I would think.

Patrick
Andrew

Connecting to MySQL from PhotoShop/Bridge via JavaScript

Post by Andrew »

I'm just scratching my head and trying to remember where I got to but I'm pretty sure I did manage to get mySQL to write output to text (which means it is easy since my days working with mySQL is about 10) and I did manage to fire up a query from a bat file. I must have a look on my old PC to see if I can find anything. I do think that from a general user perspective being able to limit it to JS and mySQL is of benefit (actually I began scripting via php and it is a great language but it adds another layer of challenge for the average PS JS script writer - a bit like the Flash discussion on another thread).

Andrew
Mike Hale

Connecting to MySQL from PhotoShop/Bridge via JavaScript

Post by Mike Hale »

Andrew wrote:...actually I began scripting via php and it is a great language but it adds another layer of challenge for the average PS JS script writer ...

I know, so much to learn so little time.

If you have mysql installed to run on the localhost you can use cmd and dos pipes to run the sql and create the data file

Code: Select alldos shell>mysql -uusername -ppassword database < sqlFile.sql > data.txt

I just started working with mysql myself and don't yet have php installed. But I have been able to create text files from a sample database using the commandline above. The docs also say you can use the -tee option to log output to a file but I haven't been able to get that to work.

Mike
Mike Hale

Connecting to MySQL from PhotoShop/Bridge via JavaScript

Post by Mike Hale »

I don't think this is akward at all Patrick. In fact I liked the idea so much I thought I would see if I could do the same with an Access database.

I don't have PHP installed, so I used VBS instead to query the database and write the text file. The outline is much the same as using PHP. The only difference from a scripting standpoint is that the vbs file doesn't need a batch file to run it.

Here is the vbs file I created

EDIT: I removed the sample script here because I now have a generic script that will can connect to any ODBC datascource that accepts a DNS-less connection. See the post below

And the execute() method of File is all that is needed to run the vbs.

This works for Excel too!

I'm so glad you posted this. Like you, I have scripts that I run often that need a csv file. It's great that the scripts can now update the csv themselfs.

Mike
Mike Hale

Connecting to MySQL from PhotoShop/Bridge via JavaScript

Post by Mike Hale »

I have been playing with Patrick's basic outline to create a csv from a data scurce.

I now have a generic vbs script that will connect to either an Excel xls file or an Access mdb file. The connection options are supplied to the vbs file as arguments. Here is the VBS file.

Code: Select allDIM args, num, fso, txtFile, rs, db, F, Head

set args = WScript.Arguments
num = args.Count
 if num < 3 then
  WScript.Quit 1
end if

Set db = CreateObject("ADODB.Connection")
db.Open args.Item(0)
set rs = db.execute( args.Item(1) )
Set fso = CreateObject("Scripting.FileSystemObject")

Set txtFile = fso.CreateTextFile( Args.Item(2), True)
For Each F In rs.Fields
  Head = Head & ", " & F.Name
Next
Head = Mid(Head,3)
txtFile.Writeline(Head)
txtFile.Write(rs.GetString(,,", ",vbCrLf,""))
rs.Close
db.Close
txtFile.Close

And here is a sample JS script that creates a bat file to run the vbs script for Excel.

Code: Select allvar vbsFile = new File('/c/temp/sqltest3.vbs');
var batFile = new File('/c/temp/sqltest.bat');
var xlsFile = new File('/c/temp/filmmix.xls');
var csvFile = new File('/c/temp/vbs_sql_test.txt');
var connStr = ' "' + 'Driver={Microsoft Excel Driver (*.xls)}; DriverId=790;  Dbq='
    connStr = connStr +  xlsFile.fsName + '; DefaultDir=' + xlsFile.parent.fsName + '"';
var sqlStr = ' "SELECT * FROM Film"';

batFile.open('w');
batFile.write('"' + vbsFile.fsName + '"' + connStr + sqlStr + ' "' + csvFile.fsName + '"');
batFile.close();
batFile.execute();

The files don't have to be in the same folder. But unlike Patrick's PHP/MySql, the files do have to be on a local drive.

To use the same vbs script to create the csv from Access change the connecton string to

Code: Select all"Driver={Microsoft Access Driver (*.mdb)}; Dbq=c:\somepath\mydb.mdb;"

The Access driver doesn't need the DriverID and DefaultDir arugements.

With either Excel or Access the vbs will dump the returned recordset with field headers into a csv file.

This was tested with Office 2000 versions running on WinXP Home.

Mike
Andrew

Connecting to MySQL from PhotoShop/Bridge via JavaScript

Post by Andrew »

I've been experimenting with this trying to do it directly to mysql. The reason for this is because I prefer not to have people dependent on the extra layer of VB Script.

My understanding of command line interaction is minimal, including issues to do with paths, but I have got the rudiments of the process working. The method and madness reflects my ignorance but it should allow others to see how it works.

Create a text file, called mysqlTest.txt containing a series of mysql queries (each ending in semi-colon):

Code: Select allSELECT * FROM test.pet WHERE name LIKE "F%";

Here test is a database and pet is a table in test which contains some entries with column name starting with F. mysqlTest.txt is in the same folder as the mysql exe file in my case /c/mysql/bin. I am sure it need not be.

Create a bat file called mysqltest.bat:

Code: Select allcd ../../../mysql/bin/
mysql -u forest -e "source mysqlTest.txt" > myOutput.txt
notepad

notepad ?#!!? That's there because it's the only way I could find to make the command prompt window stay open which means I can see error messages.

The cd ../../../mysql/bin/ is to navigate from where I personally start off when I open the command prompt window, to the mysql.exe file I want to run. There will be much better ways of doing this but it does work on my system.

the -u forest is my username and that user has access to the test database. In my case I do not use a password but you may need to include that -p mypass.

the -e "source mysqlTest.txt" tells it to read the text file and execute it in mysql.

You can also use
Code: Select allcd ../../../mysql/bin/
mysql -u forest < mysqlTest.txt > myOutput.txt
notepad

For me this shows up in the command prompt with 0<mysqlTest.txt 1>myOutput.txt. but it does work.

the >myOutput.txt is where the output gets written to. This shows up in folder \c\mysql\bin, so for example >../myOutput.txt moves it up a folder to /c/mysql. Again I am sure xbytor can tell you how to gain real control over these paths.

It does not matter if the output file already exists, it just gets overwritten.
You run the bat file with a javascript that does:

Code: Select allfunction runMySQLBat() {
var f = File(pathtomysqlTest.bat);
f.execute();
}

You can reuse that bat file, using javascript to write the SQL queries in mysqlTest.txt as you require them, and retreiving results from myOutput.txt.

I think using the output file like this is probably the best method because it can already exist and it can contain output from a long list of queries. But there is an alternative that might work if you are having problems with the > myOutput.txt part. You can try writing the output from each individual query to an external file. In this case the file must not already exist (so you will have to delete it with js before running the bat file. In this case leave out the > myOutput.txt but make the SQL look something like:

Code: Select allSELECT * FROM test.pet WHERE name LIKE "F%" INTO OUTFILE "thissqloutput.txt";

This time only the single query will be returned in the output file - it shows up in the database subfolder this time /c/mysql/data/test/.

You can have any SQL command you want in the mysqlTest.txt file so you can add data create new tables, whatever.

So, where this gets to is: js can input data to a mysql database, can send queries to that database and can read the resulting output. That is as far as I am going to go with it since I don't need it myself but it might be useful to others.

The good thing about this is that you have to generate the SQL query one way or another anyway and this does away with going via VB script. But in reality more people are probably using Access then mysql so for them the VBScript method may be preferable.

Andrew
Mike Hale

Connecting to MySQL from PhotoShop/Bridge via JavaScript

Post by Mike Hale »

Andrew wrote:notepad ?#!!? That's there because it's the only way I could find to make the command prompt window stay open which means I can see error messages.
You can replace notepad with cmd to leave the window open for debugging.
It does not matter if the output file already exists, it just gets overwritten.
You can also append the results to the output file instead of overwitting by using >>myoutputfile.txt. If the file doesn't exists, it will be created and if it does the optput will be added to the end.
The good thing about this is that you have to generate the SQL query one way or another anyway and this does away with going via VB script. But in reality more people are probably using Access then mysql so for them the VBScript method may be preferable.
I don't really like the need for VBS when dealing with Access or Excel either. But as they don't have a command line interface like MySQL does it was the only way I could find to create a JS-Database interface using either as the data source.

That is why I tried to make the vbs generic so it can be used without having to change it as well as the bat file every time it used. I think that your, Patrick's, and my approach are all valid depending on the data source.

Altough there is a ODBC driver of MySQL, I would use your approach instead because of the command line interface offered by MySQL.

In an ideal world there would be a bulit-in database interface in JS. However whether it php-bat-txt-js, sql-bat-txt-js, or vbs-bat-txt-js intermediate files are needed to connect.

Mike
Andrew

Connecting to MySQL from PhotoShop/Bridge via JavaScript

Post by Andrew »

Useful comments Mike, you're way ahead of me with this stuff, so how do you get the command interface to remain open while debugging?

Andrew