31 Replies Latest reply on Oct 24, 2014 9:31 AM by CSparrow

    Getting data from SQL database

    Roy Marshall Level 1

      Hi

       

      I am stil trying to get some basic information stored in a database table on my SQL Server.

       

      I can do this by embedding an Applescript do carry out the shell command 'curl' as below:

       

      myFullScriptString = "do shell script \"curl -0  localhost/asset.php?asset_id="+assetID+"\"";

      myForceError = app.doScript(myFullScriptString, ScriptLanguage.APPLESCRIPT_LANGUAGE);

      return(myForceError);

       

      but I dont want to be limited to OSX.

       

      Is there a way this can be put into a native socket comment using a structure as below?

       

      reply = "";

      conn = new Socket();

      conn.open("192.168.91.184:8888");

      conn.write(" GET /myfile.php?" + variousVariable + " HTTP/1.0\n\n");

      reply = conn.read(999999);

      conn.close;

       

      Cheers

       

      Roy

        • 1. Re: Getting data from SQL database
          John Hawkinson Level 5

          Hmm, the dangers of answering threads in reverse chronological order. Apparently you are familiar with the Socket api.

          Something like that should work, yes.

           

          But your question is quite oddly phrased. I assume you've tried that and it does not work [else why would you be posting]? But you do not tell us how it fails!

           

          How does it fail?

          • 2. Re: Getting data from SQL database
            Roy Marshall Level 1

            Hi.

             

            I have spent some  time playing with the code below:

             

            reply = "";

            conn = new Socket();

            conn.open("localhost:80");

            conn.write(" GET /getassetstatus.php?enc=10.1.1.234~38~11~13446~PASSWORD");

            reply = conn.read(999999);

            conn.close;

             

            where the php file on my computer "getassetstatus.php" takes the string, and returns a number.  This works when being passed as a curl commend 2 layers deep inside doScript, but I want to take out the Applescript dependancy.

             

            I dont know what the "GET" does, and the code I used as a base had "HTTP/1.0\n\n" at the end of the url.

             

            With all my tests, the variable reply is always empty.

             

            If anyone can help, I would be grateful.

             

            Cheers

             

            Roy

            • 3. Re: Getting data from SQL database
              John Hawkinson Level 5
              conn.write(" GET /getassetstatus.php?enc=10.1.1.234~38~11~13446~PASSWORD");
              

              I dont know what the "GET" does, and the code I used as a base had "HTTP/1.0\n\n" at the end of the url.

               

              With all my tests, the variable reply is always empty.

              You need the <SPACE>HTTP/1.0\n\n! Why did you take it out? Check the protocol spec, http://www.ietf.org/rfc/rfc2616.txt.

               

              You also have a leading space that looks wrong. So:

               

              conn.write("GET /getassetstatus.php?enc=10.1.1.234~38~11~13446~PASSWORD HTTP/1.0\n\n")
              
              • 4. Re: Getting data from SQL database
                Roy Marshall Level 1

                HI.

                 

                I did an attempt with the missing code previously, and several other variations.  All not giving me the single value I was expecting back.

                 

                What I do get however is this

                 

                HTTP/1.1 200 OK Date: Sun, 02 Oct 2011 17:21:01 GMT Server: Apache/2.2.17 (Unix) mod_ssl/2.2.17 OpenSSL/0.9.8r DAV/2 PHP/5.3.4 X-Powered-By: PHP/5.3.4 Content-Length: 1 Connection: close Content-Type: text/html  2

                 

                Only now I realize that this IS correct, as the number I was wanting is the 2 at the end!

                 

                DOH!

                 

                Thnaks agaoin John, jou have been most helpful and patient.

                • 5. Re: Getting data from SQL database
                  John Hawkinson Level 5

                  Sigh...

                  I'm sorry, Roy, I really thought that the example in the JS Tools Guide told you how to seperate the actual response from the server's headers. But it doesn't!

                   

                  Anyhow, RFC2616 tells us:

                   

                  6 Response

                   

                     After receiving and interpreting a request message, a server responds

                     with an HTTP response message.

                   

                         Response      = Status-Line               ; Section 6.1

                                         *(( general-header        ; Section 4.5

                                          | response-header        ; Section 6.2

                                          | entity-header ) CRLF)  ; Section 7.1

                                         CRLF

                                         [ message-body ]          ; Section 7.2

                   

                  So, that means the message body is seperated by two CRLF pairs. Except that because of API magic each CRLF turns into a single \n. So you want to look for \n\n.

                   

                  So, you should be able to seperate it like this:

                   

                  body = reply.substr(reply.indexOf("\n\n")+2);
                  
                  • 6. Re: Getting data from SQL database
                    Roy Marshall Level 1

                    Cheers John

                     

                    I have the "javascript tools guide cs3" but dont seem to have the CS5 version.  Do you know where I can get it?

                     

                    Anyway, I didn't think to look in this document, I actually forgot about it, but I will try to familiarise myself with this.

                     

                    Out of interest I used

                    myDataToUse = reply[reply.length-1];

                    As I am only interested in a single digit!

                     

                    Thanks Again.

                     

                    Roy

                    • 7. Re: Getting data from SQL database
                      Harbs. Level 6

                      It should be available for the help menu in the ESTK.

                       

                      Harbs

                      1 person found this helpful
                      • 8. Re: Getting data from SQL database
                        John Hawkinson Level 5

                        I have the "javascript tools guide cs3" but dont seem to have the CS5 version.  Do you know where I can get it?

                        Harbs is certainly right, but you should bookmark the Adobe InDesign Scripting Resources page.

                        But it links to http://wwwimages.adobe.com/www.adobe.com/content/dam/Adobe/en/products/indesign/pdfs/JavaS criptToolsGuide_CS5.pdf.

                         

                        Out of interest I used

                        myDataToUse = reply[reply.length-1];
                        

                        As I am only interested in a single digit!

                        You realize this is a bad idea...what if it goes to ten? Or if you try to repurpose the code for something else? Etc.

                        1 person found this helpful
                        • 9. Re: Getting data from SQL database
                          Roy Marshall Level 1

                          Yeah, I realise this, but for this one use, I will only ever be looking for a number indicating a status, that will always only be 1, 2 or 3.

                           

                          I hear what you are saying though, and will bear in mind in the future.

                           

                          Cheers

                           

                          Roy

                          • 10. Re: Getting data from SQL database
                            Roy Marshall Level 1

                            One more quick question, I want to use this same method to pass a log entry to a PHP page which I have been doing up to now with the doscript.

                            I have tried the same technique:


                            conn.write("GET /sendSrcError.php?e=" + errorText+" HTTP/1.0\n\n");

                            but nothing is being written by the php. I am not trying to get anything back, just posting, so maybe a different metod is needed.

                            Can you advise here?

                             

                            Cheers

                             

                            Roy

                            • 11. Re: Getting data from SQL database
                              John Hawkinson Level 5

                              Sorry I missed your question yesterday, Roy.

                               

                              Are you trying to send a second query in the same connection as the first? Try a seperate connection.

                               

                              What is "errorText" Can it contain spaces? Please $.writln() out the exact text being sent and post it here...

                              • 12. Re: Getting data from SQL database
                                Roy Marshall Level 1

                                The text I am passing has been through an encodeURI so all spaces etc are web valid.

                                I will send the complete line when I can...

                                • 13. Re: Getting data from SQL database
                                  John Hawkinson Level 5

                                  I am standing by for the complete line. But are you re-using the same connection, yes or no?

                                  • 14. Re: Getting data from SQL database
                                    Roy Marshall Level 1

                                    Hi John.

                                     

                                    No, its a new connection, in a totally different function.

                                     




                                    myServerAddress = "automation.yappp.net:80";

                                    conn = new Socket();

                                              conn.open(myServerAddress);

                                     

                                              conn.write("GET /sendSrcError.php?e=" + errorText+" HTTP/1.0\n\n");

                                              conn.close;

                                     

                                    and this is the old code using the curl...

                                        myFullScriptString = "do shell script \"curl -0  "+ myServerAddress + "sendSrcError.php?e=" + errorText + "\"";

                                    This works perfect.

                                     

                                    I am sure it is something small I am doing wrong...

                                    • 15. Re: Getting data from SQL database
                                      John Hawkinson Level 5

                                      As I said, please $.writeln() the complete line being sent and post it here.

                                      • 16. Re: Getting data from SQL database
                                        John Hawkinson Level 5

                                        Err, this code can't be write:

                                         



                                        myServerAddress = "automation.yappp.net:80";

                                            myFullScriptString = "do shell script \"curl -0  "+ myServerAddress + "sendSrcError.php?e=" + errorText + "\"";

                                        This works perfect.

                                        Because it would give you "curl -O automation.yappp.net:80sendSrcError.php?e=something" with no slash before sendSrcError.

                                        Care to clarify?

                                        • 17. Re: Getting data from SQL database
                                          Roy Marshall Level 1

                                          Yes, but as I said, they are 2 separate functions, and I didn't copy the server address in to my last post for the curl. Sorry to mislead...

                                           




                                          myServerAddress = "http://automation.yappp.net/";

                                           

                                          also, I am a total newby to this part of Javascript, I have only been living purely in InDesign, and not ventured out into the web side till now. I dont even know HTML to my shame! 

                                          Please can you clarify what you mean by this?

                                          As I said, please $.writeln() the complete line being sent and post it here

                                          If you dont get to reply, I will talk to my son tonight who will tell me how to do what you are asking, and i will post what you are asking!

                                           

                                          Sorry, but I am not going to pretend to know anything I dont know!

                                          • 18. Re: Getting data from SQL database
                                            Roy Marshall Level 1

                                            Can you explain in more detail what you are asking?

                                            • 19. Re: Getting data from SQL database
                                              Roy Marshall Level 1

                                              As I am showing my ignorance in not knowing what you are asking, here are the 2 functions. One works, and the other not.

                                               

                                              This Does not post anything.

                                              function combinedErrorReportWithSocket(errorText)   //errorText is something like "Library not found"

                                                   { 

                                                   myServerAddress = "automation.yappp.net:80";

                                                   errorText = encodeURI(errorText);

                                                   conn = new Socket();

                                                   conn.open(myServerAddress);

                                                   conn.write("GET /sendSrcError.php?e=" + errorText+"\ HTTP/1.0\n\n");

                                                   reply = conn.read(999999);

                                                   conn.close;

                                                  }

                                               

                                               

                                              This works OK, and adds a line at the top of the web page.

                                               

                                              function combinedErrorReport(errorText)   //errorText is something like "Library not found"

                                                   {

                                                   myServerAddress = "http://automation.yappp.net/";

                                                   errorText = encodeURI(errorText);

                                                  myFullScriptString = "do shell script \"curl -0  "+ myServerAddress + "sendSrcError.php?e=" + errorText + "\"";

                                                  myForceError = app.doScript(myFullScriptString, ScriptLanguage.APPLESCRIPT_LANGUAGE);

                                                  }

                                               

                                              go to http://automation.yappp.net/sendSrcError.php?p=logs   to see the logs being logged.

                                              Feel free to add some entries.

                                              • 20. Re: Getting data from SQL database
                                                SuperMacGuy Level 2

                                                I don't want to muddy things up, but if you want some other options for OSX, I've been using some scripts and frameworks to talk to an MS-SQL db, and it should work for some other SQL db's also. Send me a PM if interested.

                                                • 21. Re: Getting data from SQL database
                                                  John Hawkinson Level 5

                                                  Sorry about that!

                                                  Can you explain in more detail what you are asking?

                                                  Sure! I wasn't trying to be cryptic, and I guess I missed your first request for clarification.

                                                   

                                                  We want to know exactly what is being sent to the server, so we can understand what might be wrong.

                                                  So, just add this line beforehand:

                                                  $.writeln("SENDING: <<"+"GET /sendSrcError.php?e=" + errorText+"\ HTTP/1.0\n\n"+">>");
                                                  Conn.write("GET /sendSrcError.php?e=" + errorText+"\ HTTP/1.0\n\n");
                                                  

                                                  Then it will print the GET line on the console and you can paste it here and we can see what (if anything) is wrong with it.

                                                  • 22. Re: Getting data from SQL database
                                                    Roy Marshall Level 1

                                                    Hi John.

                                                    I have done that (Thanks for the instructions)

                                                    Here is the result:

                                                     

                                                    SENDING: <<GET /sendSrcError.php?e=text%20To%20Send HTTP/1.0

                                                     

                                                    >>

                                                     

                                                    Thanks

                                                     

                                                    Roy

                                                    • 24. Re: Getting data from SQL database
                                                      John Hawkinson Level 5

                                                      Sorry for the delay, I was gone for the long weekend.

                                                      I should also have asked you, as part of the diagnostic process, to tell us what was in reply.

                                                      This question is being continued in http://forums.adobe.com/message/3963484#3963484

                                                      I don't know about other people, but I find that practice annoying. Stick with your thread, don't try to bump it. IMNSHO.

                                                       

                                                      Anyhow, the problem appears to be that your server does not accept HTTP/1.0 queries in this case, it requires HTTP/1.1. I am not sure why that would be it seems surprising to me.

                                                       

                                                      So, you should restructure your code like this, to be HTTP/1.1 compliant, which basically means including the Host: header to support HTTP/1.1 Virtual Hosting:

                                                       

                                                      function combinedErrorReportWithSocket(errorText)
                                                          //errorText is something like "Library not found"
                                                           {  
                                                           myServer = "automation.yappp.net"; myPort=80;
                                                           myServerAddress=myServer+myPort;
                                                           errorText = encodeURI(errorText);
                                                           conn = new Socket();
                                                           conn.open(myServerAddress); 
                                                           conn.write("GET /sendSrcError.php?e=" + errorText+
                                                              " HTTP/1.1\nHost: "+myServer+"\n\n");
                                                           reply = conn.read(999999);
                                                           conn.close;
                                                          }
                                                      
                                                      • 25. Re: Getting data from SQL database
                                                        John Hawkinson Level 5

                                                        Whoops, typo. Left out the colon, should be:

                                                         

                                                        myServerAddress=myServer+":"+myPort;
                                                        
                                                        • 26. Re: Getting data from SQL database
                                                          Roy Marshall Level 1

                                                          Hi John.

                                                           

                                                          Thats perfect.

                                                           

                                                          I have been feeling so frustrated with this up to now. I wont pretend to understand the solution, maybe in time I will, but I am a happy bunny now.

                                                           

                                                          Thanks again,

                                                           

                                                          Roy

                                                          • 27. Re: Getting data from SQL database
                                                            Dr. TYPO

                                                            hi all,

                                                            just wanted to reactivate this topic as we've proudly released an innovation: http://www.porky.io

                                                             

                                                             

                                                            porky is a ESTK/JavaScript extension/functions library and you can use various data types like SQL databases, XML- and JSON files as a variable source.

                                                             

                                                             

                                                            So: create entire publishing workflows based on your JavaScript code!

                                                             

                                                             

                                                            Happy coding :-)

                                                             

                                                             

                                                            Cheers

                                                            • 28. Re: Getting data from SQL database
                                                              csparrowsugoi

                                                              HI Dr. TYPO

                                                               

                                                              I'm interested in your innovation and downloaded/installed it, but when i follow the instructions and double click the "start-porky-data-source-access.jsx" as it states I get a warning that says "not yet implemented, sorry"

                                                               

                                                              I am using CC, would that be the issue?



                                                              Nevermind, looked at the code and noticed it was applescript.  Do you have a PC workaround?


                                                              • 29. Re: Getting data from SQL database
                                                                Dr. TYPO Level 1

                                                                Thanks for your interest in porky!

                                                                 

                                                                On OS-X we have a php interpreter (which handles the database connection) already included out of the box.

                                                                 

                                                                The launcher script for the windows platform will be implemented pretty soon – I'll let you know as soon as it is ready!

                                                                • 30. Re: Getting data from SQL database
                                                                  Dr. TYPO Level 1

                                                                  Hi csparrowsugoi,

                                                                   

                                                                  the Windows version of porky (JavaScript library with database access for Adobe InDesign) is now available!

                                                                   

                                                                  Please check porky.io and follow the installation guide in the download section.

                                                                   

                                                                  Cheers

                                                                  • 31. Re: Getting data from SQL database
                                                                    CSparrow Level 1

                                                                    thank you kindly sir. I'll check it out!  Hopefully I'm smart enough to get by installation lol

                                                                    • 32. Re: Getting data from SQL database
                                                                      CSparrow Level 1

                                                                      Amazing.. works like a charm. I think I can easily work this for a direct to mySQL connection using the SQLLite examples.  Kudos!