Tuesday, April 11, 2006

Execute Immediate and DDL

Today I created a script which creates DDL statements on the fly for doing data conversion between Loyalty phase 1 and 2. I discovered that when doing:


begin
execute immediate('create table MYTABLE(......)');
insert into MYTABLE(select * from ORIGINAL_TABLE);
end;

The above code doesn't compile. I get ORA-00942: table or view does not exist.

However when I do this:

begin
execute immediate('create table MYTABLE(......)');
execute immediate('insert into MYTABLE(select * from ORIGINAL_TABLE)');
commit; -- don't forget the commit, execute immediate doesn't do this for you automatically
end;


Then it works. Which kind of makes sense because the compiler doesn't recognize that table until it gets half way through the script execution. So if you dynamically need to create a table and then insert into it, you have do dynamically insert it using execute immediate.

Monday, April 10, 2006

SJAX - Synchronous Javascript and XML

Lian answered my question regarding how to implement a synchronous model of AJAX (although once we follow a synchronous model it really isn't AJAX anymore, but SJAX). I updated my EE post, although they may delete the question as it wasn't officially answered within EE: Here is the link to the question.

The trick was to NOT use the onreadystatechange. Here is a copy of the full function that uses the synchronous request. (Sorry about the bad white space formating, eblogger screws it up):


function makeSyncRequest(data, field){
originalField = field;
var fullURL = document.URL;
var loyaltyLocation = fullURL.indexOf("/loyalty/") + 9;
var url = fullURL.substr(0,loyaltyLocation) + "ajax?";
http_request = false;

if (window.XMLHttpRequest) { // Mozilla, Safari,...
http_request = new XMLHttpRequest();
if (http_request.overrideMimeType) {
http_request.overrideMimeType('text/xml');
}
} else if (window.ActiveXObject) { // IE
try {
http_request = new ActiveXObject("Msxml2.XMLHTTP");
} catch (e) {
try {
http_request = new ActiveXObject("Microsoft.XMLHTTP");
} catch (e) {}
}
}
if (!http_request) {
alert('Giving up :( Cannot create an XMLHTTP instance');
return false;
}
http_request.open('GET', url + data, false);
http_request.send(null);

if(http_request.status == 200) {
var xmldoc = http_request.responseXML;
var ajaxValue = xmldoc.getElementsByTagName("Root")[0].childNodes[0].nodeValue;
if (ajaxValue == "0") {
alert("DCL " + validatingDCL + " is invalid.");
setTimeout("originalField.select()", 5);
}
}

}

Thanks Lian!

I decided to pull the validating of DCL out of the onkeyup trigger and put in the onblur action to avoid all sorts of crap, the only thing is that if the user ever goes to a different window, the on-blur fires, and if the DCL is invalid, an error message comes up, which re-fires the on-blur so I get an endless loop of error messages. So I put a flag in the validate so that if I get the error it sets a flag which prevents it from revalidation, until the error message is done. This was only possible in Synchonized mode, and not Asynchronous, so this really comes in handy to get around the on-blur firing repeatedly.