Posted by mravel in SWF Studio V3 on Dec 15 2008, 08:54 am

Can I use the ADO plugin to directly (without submitting to an asp page) open a connection & INSERT or UPDATE values on a MS access database hosted on a webserver. If yes how would the connection string change, what would the syntax be.

Do you know any windows webspace provider hosts who would allow a direct connection. If so please let me know because i couldn't find any.

Thanks


Posted by northcode in SWF Studio V3 on Dec 15 2008, 09:30 am

You CAN remotely access servers like MySQL via ADO but Access doesn't provide a server it's a file based system so you won't be able to make access work over the web unless there's a third party ADO provider that makes such a connection possible.


Posted by mravel in SWF Studio V3 on Dec 15 2008, 09:49 am

The reason i thought of the above was because i need to read memo fields from local databases using several swf-studio created projects on a number of client PCs and save the values to a database on a web server. I know i can use the http plugin and asp to send most fields, but the memo field poses a problem because its too large to be part of the name/value part of a querystring. Any idea how i can go about doing this?


Posted by northcode in SWF Studio V3 on Dec 15 2008, 10:08 am

If you use a POST instead of a GET you can pass as much data as you like. The server side script has to read the request data instead of the query string but that should be mostly transparent to you using things like ASP or PHP.


Posted by mravel in SWF Studio V3 on Dec 15 2008, 10:27 am

some code to help me out please. The only way i know to use the http plugin is thru forming a querystring and passing it to an asp page & then using
Trim(Request("name")) to get the values into variables.

I tried going thru the http example but the php file you post to isnt included so that part of the code i couldn't study or modify for my use.

I had earlier got some modified code of yours to work
   // Tell the plugin what resource we want, and where to get it.
   ssCore.HTTP.host({host: "users5.titanichost.com"});
   ssCore.HTTP.protocol({protocol:"http"});
   ssCore.HTTP.port({port: "80"});
   ssCore.HTTP.resource({resource: "/qazaq/formin.asp"});
   ssCore.HTTP.method({method: "POST"});
   // Specify the content-type so the server knows what to do with the data.
   ssCore.HTTP.headers({headers:"Content-Type: application/x-www-form-urlencoded\r"});
   
   ssCore.HTTP.start();

   // Tell the plugin what form data to send in the request.
       // finaldataset obtained & formed from database using ado
   ssCore.HTTP.request({data: finaldataset});
   // Send the data to the host, and read the response.
   ssCore.HTTP.read();
   
   ssCore.HTTP.stop();


would this work for a long memo field


Posted by northcode in SWF Studio V3 on Dec 15 2008, 12:45 pm

There's not much difference between POST and GET except for where you put the data. To pass data via POST you add it to a request buffer. The data in this buffer can take whatever form you like.

If you're already set up to handle URL encoded form data you can do that too - that's what I've shown in the example below. The perl script I've used exists at northcode.com and you can use it for testing if you like, all it does is dump out whatever you send it.


ssCore.init(); 
ssDefaults.synchronousCommands = true;

data = "data=" + escape("put whatever you want in here, but your server side script has to parse it!");

ssCore.HTTP.host({host:"northcode.com"});
ssCore.HTTP.method({method:"POST"});
ssCore.HTTP.port({port:"80"});
ssCore.HTTP.resource({resource:"/cgi-bin/test.pl"});
ssCore.HTTP.protocol({protocol:"HTTP"});
 <B>ssCore.HTTP.request({data:data});</B>
ssCore.HTTP.headers({headers:"Content-Type: application/x-www-form-urlencoded\n\n"});
ssCore.HTTP.start({});

ssCore.HTTP.read({}, {callback:onRead});

function onRead(return_obj, callback_obj, error_obj)
{
   var r = ssCore.HTTP.getBuffer({});
   
   ssCore.FileSys.writeToFile({path:"startdir://result.html", data:r.result});
   ssCore.Shell.open({path:"startdir://result.html"});
   
   ssCore.HTTP.stop({});
}
You'll need the HTTP plugin and the FileSys plugin for this example. I made it write the results of the POST to an HTML file (the perl script returns HTML in this case) and then used Shell.open to display it.


Posted by mravel in SWF Studio V3 on Dec 15 2008, 02:52 pm

i think i finally got it. Still not seeing the results though. I have a loop running to assign values read from the database to variables and then using the http plugin to post information to the asp page. But the loop runs so fast that nothing happens. How do i introduce a delay in the loop so that the http plugin has time to post to the asp page. The setinterval function i've used doesn't seem to do the trick.

// Forms the finaldataset.
for (var i = 0; i<my_array.length; i=i+5) {
ssCore.App.showMsgBox({prompt:"start loop"});
var vfullname = my_array[i];
var vgender = my_array[i+1];
var vbloodgroup = my_array[i+2];
var vhistory = my_array[i+3];
var vdoctorname = my_array[i+4];
var finaldataset = "fullname=" + vfullname + "&gender=" + vgender + "&bloodgroup=" + vbloodgroup + "&history=" + vhistory + "&doctorname=" + vdoctorname;

     
// Tell the plugin what resource we want, and where to get it.
ssCore.HTTP.host({host: "users5.titanichost.com"});
ssCore.HTTP.protocol({protocol:"http"});
ssCore.HTTP.port({port: "80"});
ssCore.HTTP.resource({resource: "/qazaq/formin.asp"});
ssCore.HTTP.method({method: "POST"});
// Specify the content-type so the server knows what to do with the data.
ssCore.HTTP.headers({headers:"Content-Type: application/x-www-form-urlencoded\r"});

ssCore.HTTP.start();
// Tell the plugin what form data to send in the request.
ssCore.HTTP.request({data: finaldataset});
// Send the data to the host, and read the response.
ssCore.HTTP.read();
ssCore.App.showMsgBox({prompt:"start delay"});
setInterval( function(){ trace("interval called"); }, 30000 );
ssCore.App.showMsgBox({prompt:"end delay"});
ssCore.HTTP.stop();

}


Posted by northcode in SWF Studio V3 on Dec 15 2008, 03:17 pm

ssCore.HTTP.read is asynchronous only, you need a callback function to figure out when it is finished. Have another look at my example. Instead of a normal loop, you should break it up so you call the guts of your loop each time the HTTP.read completes, until you're done.


Posted by mbd in SWF Studio V3 on Dec 15 2008, 04:08 pm

Note about setInterval:
setInterval doesn't stop the Flash Player from executing, which, by your trace messages, looks like what you were expecting.

The function you specify in the call to setInterval is called after the delay you set, but the next line of code is executed right after the setInterval call itself.

Check the Flash docs and do some experimentation. setInterval is a call to the Flash Player to execute a function with some parameters (optionally) after a given time. Even then, the execution is based on framerate. Remember, the Flash Player's roots are animation, so framerate matters.


Posted by mravel in SWF Studio V3 on Dec 16 2008, 04:11 am

Derek's right. I was thinking setinterval would pause the loop. I'll take up his suggestion to look thru the flash docs for a suitable method.


Posted by mbd in SWF Studio V3 on Dec 16 2008, 07:04 am

You won't find a method in Flash that will pause the Flash Player. That's not how you want to handle this. Take Tim's advice and look at callbacks.

The way callbacks work is that you call a command and specify a function that will be called when data is to be returned. That lets Flash continue doing what it's doing and when data is ready your function is called. This makes the call asynchronous, which is what you want. You do not want to stop the Flash Player from executing.

When you call a command synchronously the Flash Player is held up until data is returned so that it's ready for the next line of code. However, asynchronous calls can't work this way. There are times when synchronous calls make sense, but in some cases they really don't. HTTP.read is one of those cases. You don't want the Flash Player to hold up while your application waits for a remote server to respond and/or a file to completely download. Instead, you issue the command, and let the application receive an event when the command is complete/data is ready.

Take a look at ActionScript API > Asynchronous Commands.

Note that in the help all commands display a synchronous and asynchronous usage, except those that are asynchronous-only, in which case it is specified as such. For example, the Synchronous Mode example for ssCore.HTTP.read is marked as "This method can only be called asynchronously."


Posted by mravel in SWF Studio V3 on Dec 17 2008, 04:51 am

Thanks for the tip and for taking the time to reply. I'm going thru the asynchronous commands in the docs trying to work the problem out.


Posted by mravel in SWF Studio V3 on Dec 19 2008, 04:02 pm

I'm trying to retrieve all records from my database and post to an asp page but its not working. The same code works if instead of the variable mystring which holds the records from the database i supply a simple text variable finaldataset. i.e. if i replace myString with finaldataset in ssCore.HTTP.request it inserts the string data perfectly. The asp page works perfectly as well, since if i directly type out the pagename in the browser followed by the data it inserts the records in the online database. Can you tell me what's causing the problem?

ssCore.init();
ssDefaults.synchronousCommands = true;
//get all records from the database
ssCore.ADO.close();
// ============================================================
// Setup our connection and return data format.
// ============================================================
ssCore.ADO.setConnectString({connectString: "Provider=Microsoft.Jet.OLEDB.4.0; Persist Security Info=False; Data Source=" + ssGlobals.ssStartDir + "\\ozone.mdb;"});
ssCore.ADO.setTable({name: "patient"});

ssCore.ADO.setRowFormat({format: "DLM"});
ssCore.ADO.setDelimiter({delimiter: ","});
ssCore.ADO.setSeparator({separator: ","});

ssCore.ADO.setSQL({sql:"SELECT fullname, gender, bloodgroup, history, doctorname FROM patient ORDER BY fullname"});

// Open the database/table defined above.
var return_obj = ssCore.ADO.open();

if (return_obj.success)
{
   
    // if the open completed successfully we get all of the rows from the recordset as XML
    return_obj = ssCore.ADO.getRows();

    if (return_obj.success)
    {
        // if there were records to be returned we display them in the SWF Studio Trace tab
        ssDebug.trace(return_obj.result);
      var myString = return_obj.result;
    }
    else
    {
        // if an error occurs we display the description in the SWF Studio Trace tab
        ssDebug.trace("ERROR: "+return_obj.Error.description);
    }
}
else
{
    // if an error occurs we display the description in the SWF Studio Trace tab
    ssDebug.trace("ERROR: "+return_obj.Error.description);

    // we also display the adoError output parameter for the open method, which will be
    // populated if there is an ADO-specific error message returned from the ADO provider
    ssDebug.trace(" adoError: "+return_obj.adoError);
}

   
   
   //POST data to asp page
   // Tell the plugin what resource we want, and where to get it.
   ssCore.HTTP.host({host: "users5.titanichost.com"});
   ssCore.HTTP.protocol({protocol:"http"});
   ssCore.HTTP.port({port: "80"});
   ssCore.HTTP.resource({resource: "/qazaq/formin.asp"});
   ssCore.HTTP.method({method: "POST"});
   // Specify the content-type so the server knows what to do with the data.
   ssCore.HTTP.headers({headers:"Content-Type: application/x-www-form-urlencoded\r"});

   var finaldataset = "fullname=bobby&gender=kbobby@xyz.com&bloodgroup=None&history=dsadad&doctorname=doctor";   
   ssCore.HTTP.start();

   // Tell the plugin what form data to send in the request.
   ssCore.HTTP.request({data: myString});
   ssDebug.trace(myString);
   // Send the data to the host, and read the response.
   ssCore.HTTP.read();
   
   ssCore.HTTP.stop();


btw. If you're compiling the above code "http://users5.titanichost.com/qazaq/db/PageDB2.asp" displays the records in the database that the above code posts to - i.e. if the said code does its job.


Posted by northcode in SWF Studio V3 on Dec 19 2008, 08:40 pm

The data you get back from ADO.getRows is going to look nothing like the data you're using in your finaldataset variable. You've told the ADO plugin to return data with rows separated by commas and fields separated by commas, that's nowhere close to the application/x-www-form-urlencoded data you've told the server side script to expect. You're going to have to transform the results of the call to getRows into the proper format or change your server side script to use what you're sending. You're also trying to send multiple rows with output from ADO where you're only sending 1 row of data in finaldataset. What is your script expecting? Can it deal with multiple rows (forms) or is it expecting them one at a time?


Posted by mravel in SWF Studio V3 on Dec 20 2008, 03:25 am

Okay take a look at the code below which assembles the finaldataset exactly like the finaldataset string variable from before. It reads values from the database and assembles finaldataset one row at a time and tries to use a loop to submit it to the asp page one row at a time. But whats going wrong is that firstly it's just sucessfull in submitting the last row of the database and secondly it removes all carriage returns and new lines from the database field. Where am i going wrong?


ssCore.init();
ssDefaults.synchronousCommands = true;

ssCore.ADO.close();
// ============================================================
// Setup our connection and return data format.
// ============================================================
ssCore.ADO.setConnectString({connectString: "Provider=Microsoft.Jet.OLEDB.4.0; Persist Security Info=False; Data Source=" + ssGlobals.ssStartDir + "\\ozone.mdb;"});
ssCore.ADO.setTable({name: "patient"});

ssCore.ADO.setRowFormat({format: "DLM"});
ssCore.ADO.setDelimiter({delimiter: "?"});
ssCore.ADO.setSeparator({separator: "?"});

ssCore.ADO.setSQL({sql:"SELECT fullname, gender, bloodgroup, history, doctorname FROM patient ORDER BY fullname"});

// Open the database/table defined above.
var return_obj = ssCore.ADO.open();

if (return_obj.success)
{
   
    // if the open completed successfully we get all of the rows from the recordset as XML
    return_obj = ssCore.ADO.getRows();

    if (return_obj.success)
    {
        // if there were records to be returned we display them in the SWF Studio Trace tab
        ssDebug.trace(return_obj.result);
      var myString = return_obj.result;
      var my_array:Array = myString.split("?");

   for (var i = 0; i<my_array.length; i++) {
    ssDebug.trace(my_array[i]);
   //ssCore.App.showMsgBox({prompt:my_array[i]});

                                 }
    }
    else
    {
        // if an error occurs we display the description in the SWF Studio Trace tab
        ssDebug.trace("ERROR: "+return_obj.Error.description);
    }
}
else
{
    // if an error occurs we display the description in the SWF Studio Trace tab
    ssDebug.trace("ERROR: "+return_obj.Error.description);

    // we also display the adoError output parameter for the open method, which will be
    // populated if there is an ADO-specific error message returned from the ADO provider
    ssDebug.trace(" adoError: "+return_obj.adoError);
}



// Forms the finaldataset.
for (var i = 0; i<my_array.length; i=i+5) {
//ssCore.App.showMsgBox({prompt:"start loop"});
var vfullname = my_array[i];
var vgender = my_array[i+1];
var vbloodgroup = my_array[i+2];
var vhistory = my_array[i+3];
var vdoctorname = my_array[i+4];
var finaldataset = "fullname=" + vfullname + "&gender=" + vgender + "&bloodgroup=" + vbloodgroup + "&history=" + vhistory + "&doctorname=" + vdoctorname;

ssCore.HTTP.stop();     
// Tell the plugin what resource we want, and where to get it.
ssCore.HTTP.host({host: "users5.titanichost.com"});
ssCore.HTTP.protocol({protocol:"http"});
ssCore.HTTP.port({port: "80"});
ssCore.HTTP.resource({resource: "/qazaq/formin.asp"});
ssCore.HTTP.method({method: "POST"});
// Specify the content-type so the server knows what to do with the data.
ssCore.HTTP.headers({headers:"Content-Type: application/x-www-form-urlencoded\r"});

ssCore.HTTP.start();
// Tell the plugin what form data to send in the request.
ssCore.HTTP.request({data: finaldataset});
// Send the data to the host, and read the response.
ssCore.HTTP.read();

}
ssCore.App.showMsgBox({prompt:"Upload Complete"});
   
     

stop();


I'm including the database this time for better clarity.

btw i understand code better than i understand plain english - it just makes things more clearer so i'll very much appreciate you guide me thru code examples

attachments: ozone.zip