5 Replies Latest reply on Apr 15, 2015 12:38 PM by man9ar00

    Querying SQL or Google Sheet from ExtendScript...

    jsavage77 Level 1

          Looking for a way to query JDBC databases from ExtendScript, or alternatively access data in a google spreadsheet.  I know I can import CSV but I need a solution that pulls data dynamically from a live DB.  Has anyone here attempted either of these before?

        • 1. Re: Querying SQL or Google Sheet from ExtendScript...
          man9ar00 Level 1

          You might want to take a look at this for ideas:


          database use with ExtendScript (for Adobe Illustrator) - Stack Overflow


          I would probably prefer an approach of having a wrapper script (in Java, VBScript, Python, Perl, Applescript) that handled the DB interaction and then feed the data to ExtendScript using the Adobe app's COM (e.g. VBScript) or Applescript APIs. Keeps your ExtendScript simpler this way.

          • 2. Re: Querying SQL or Google Sheet from ExtendScript...
            Silly-V Adobe Community Professional

            Would you be able to post an example snippet in either AS or VB that feeds data to JSX ?
            All I have done so far regarding this is write a javascript inside an applescript, so the JSX was dynamically created, but it was a big pain due to all escape characters, and not really good for writing and testing my JSX separately.

            • 3. Re: Querying SQL or Google Sheet from ExtendScript...
              man9ar00 Level 1

              I haven't had the need to pass data myself, but this SO post gives example of how to do so with Applescript and VBScript (separate answers for each language):


              javascript - Is it possible to execute JSX scripts from outside ExtendScript? - Stack Overflow


              this SO post was referenced in my answer in the previous SO post link.


              To summarize, it would be best you externalize your ExtendScript code into a JSX file and run the JSX file from Applescript/VB, that way you don't have to escape the ExtendScript code & can test each part independently if modularlized correctly. And when passing data, you could wrap a "main" method in the JSX file that will take the arguments/parameters passed from Applescript/VB and process accordingly.


              In terms of DB data passing, since we're talking about inter-language communication, we can't just pass a DB recordset object between the languages. So you would have to decide how you reformat the DB data to pass back & forth between the languages. Some options to consider is reformat the recordset to JSON, XML, or a CSV style two dimensional array, then stringify (i.e. convert as string) as needed, since you can't pass native JSON, XML, or array objects between the languages. JSON may be ideal since ExtendScript is a variant of javascript and can natively deal with JSON as objects (once you "eval" the JSON string into an object). Now, if you only pass simple data like a string or number and not a whole recordset, then that's easier without having to worry about data type conversion.


              This might sound like a hassle, but the other options given in the 1st SO post aren't really any easier to implement. This suggested approach minimizes the amount of layers and intermediary communications (create web service then call it from ExtendScript via HTTP, storing DB data in CSV/text file first, etc.) between the languages.


              Hope this helps.

              • 4. Re: Querying SQL or Google Sheet from ExtendScript...
                Silly-V Adobe Community Professional

                I will read this link when I get a good moment this day, however, using text files was actually my first thought.


                Thank you, as for wrapping the module jsx files, from the post I can assume that the following would work?


                tell application "Adobe Illustrator"

                  set  myScript to "function myJSX(){alert('starting my javascript'); #include myRealJSX.jsx alert('The End');}"

                  do JavaScript(myScript)

                end tell


                Or something like that, if I didn't do the quotes right.


                I am not near my mac right now, but I do wonder if it gets any results from the do JavaScript. I think it may handle errors at the least.

                • 5. Re: Querying SQL or Google Sheet from ExtendScript...
                  man9ar00 Level 1

                  Yes, you are on the right track. If you need to pass arguments though, you need to have a method in myRealJSX.jsx that takes arguments and then call that method after including the script before the end alert, passing in the arguments Applescript style (or using string concatenation of static text with variables containing the arguments). The SO post has more details. If you put the value you want to return at the end of the JSX file (e.g. "myValueToReturn;") it will be returned to Applescript, theoretically. No need to add a return statement.


                  If you are considering the text file approach, then you should probably dump your DB recordset as CSV file then read the file from JSX, dumping the DB data from another language (Java, etc. not sure about Applescript).