Some Source Code

JSGrid

2009-03-29T14:18:00.000-07:00

Since I've developed web applications, I was looking for a usefull grid for JavaScript, but I never found anything, there are some nice Applets and Activex files, but you have to pay for them.

So, I decided to write my own JavaScript grid and I called it JSGrid.

IMPORTANT Note: If you decide to use this script. All comments may be removed to optimize the performance, but must keep the notes about the author and credit of the authors of the modified scripts.

Remember to comment here if you use this script in a web page, so we all can see it in action.

Features:

-Sort in ascendant or decendent order by a column:
Inspired in the script of Eric Pascarello (http://www.pascarello.com/sortTable/)
-Cells editions supports textbox, checkbox and combobox:
Inspired in the script of kaka vijes, i can do it, Magnus Gudmundsson, Martin Honnen, Sachin S, william baney and felix norambuena (www.faqts.com)
-Data cell edition optional by each column
-Resizable columns optional by each column
-Use of more than one grid per page
-Manage rows and columns by a key
-Get a specified cell object method:
its properties can be modified by the td tag styles
-Get number of columns or row methods
-Show a specified column method
-Show a specified row method
-Set rows selection method
-Get selected rows method
-Delete a row method
-Add a row method
-Use any size on your grids
-Easy design change with css
-Easy implementation for new methods
-Easy iteraction with dynamic web page programing languages (PHP, ASP, JSP, CGI)
-It was tested in Firefox, IE and should works in Safari

To use it you only have to call the JSGrid.js file from your html file as in the example:

jsgrid.js

//JSGrid: Script created by Luis Eduardo Cañedo Ugalde:
// http://www.computrabajo.com.mx/cvs/eduardocanedo
// http://somesourcecode.blogspot.com/
// luis.eduardo.canedo@gmail.com

//JSGrid GETS THE GRID NAME "tblNom" (IT MUST BE THE SAME TO THE OBJECT NAME,
//SO IT CAN FIND IT'S VARIABLES) THE DATA FOR THE COLUMNS "aryCol", THE DATA
//FOR THE ROWS "aryDat", THE HEIGTH SIZE "hgtSze", THE SELECT MODE "rowSel"
//AND THE NAME OF THE VALIDATOR FUNCTION FOR VALUES CHANGES "funVal"
JSGrid = function(tblNom, aryCol, aryDat, hgtSze, rowSel, funVal)
{
//MAKE THE ARGUMENTS GLOBAL
this.arguments = JSGrid.arguments;
//CONTAINER FOR KIND OF COLUMNS
this.aryTyp = new Array();
//WICH COLUMNS CAN BE EDITED
this.aryEdt = new Array();
//CURRENT RESIZED COLUMN
this.colSze = null;

//RETURN SELECTED ROWS INTO AN ARRAY
this.getSelRows = function()
{
//SEARCH SELECTED ROWS
var arrSel = new Array();
var arrRows = document.getElementById(tblNom).getElementsByTagName('tbody')[0].getElementsByTagName('tr');
for(i=1;i<arrRows.length;i++)
if (arrRows[i].className == 'clikRow')
arrSel.push(arrRows[i].id.substring(arrRows[i].id.indexOf('_row_')+5));
return arrSel;
}

//SELECT ROWS FROM AN ARRAY
this.setSelRows = function(arrSel)
{
//IF IT'S MULTISELECT, SELECT ALL ROWS
//IN arrSel ELSE, SELECT ONLY THE FIRST
var numRows = (rowSel)?arrSel.length:1;
for(i=0;i<numRows;i++)
document.getElementById(tblNom+'_row_'+arrSel[i]).className = 'clikRow';
}

//RETURN NUMBER OF ROWS
this.getNumRows = function()
{
var arrRows = document.getElementById(tblNom).getElementsByTagName('tbody')[0].getElementsByTagName('tr');
return arrRows.length-1;
}

//RETURN NUMBER OF COLS
this.getNumCols = function()
{
var arrCols = document.getElementById(tblNom+'_titles').getElementsByTagName('thead')[0].getElementsByTagName('th');
return arrCols.length-1;
}

//RETURN A SPECIFIED CELL
this.getCelObj = function(row, col)
{
var numCol = document.getElementById(tblNom+'_col_'+col).cellIndex/2;
var selCel = document.getElementById(tblNom).getElementsByTagName('tbody')[0].getElementsByTagName('tr')[tblNom+'_row_'+row].getElementsByTagName('td')[numCol];
return selCel;
}

//ADD A ROW
this.addNewRow = function()
{
//IF THE ROW EXISTS, RETURN FALSE
if (document.getElementById(tblNom+'_row_'+this.addNewRow.arguments[0])) return false;
//CREATE THE NEW ROW
var row = document.createElement('TR');
//SET THE ROW'S ID: GRID ID, "row" STRING AND ROW KEY
row.setAttribute('id',tblNom+'_row_'+this.addNewRow.arguments[0]);
//SET THE NEW ROW SELECTED
row.className = 'clikRow';
//ADD A COLUMN FOR EACH PARAMETER AFTER THE ROW ID
for (var i=1;i<this.addNewRow.arguments.length;i++)
{
var col = document.createElement('TD');
col.setAttribute('unselectable','on');
//IF THE COLUMN IS A CHECKBOX
if (this.aryTyp[i-1] == 'chk')
{
//CENTER THE CHECK
col.setAttribute('align','center');
//USE THE GRID ID, "chk" STRING, COLUMN INDEX AND ROW KEY AS CHECKBOX ID
col.innerHTML = '<input id="'+tblNom+'_chk_'+(i-1)+'_'+this.addNewRow.arguments[0]+'" type="checkbox" '+this.addNewRow.arguments[i]+'>';
}
else //IF IT IS NOT A CHECKBOX
col.appendChild(document.createTextNode(this.addNewRow.arguments[i]));
//ADD THIS COL
row.appendChild(col);
}
//ADD THE NEW ROW
document.getElementById(tblNom).getElementsByTagName('tbody')[0].appendChild(row);
//SHOW THE NEW ROW
document.getElementById(tblNom+'_body').scrollTop = document.getElementById(tblNom+'_row_'+this.addNewRow.arguments[0]).offsetTop;
//ADJUST THE LAST TITLE CELL IF IT IS NECESARY
this.adjustTit();
return true;
}

//DELETE A ROW
this.removeRow = function(row)
{
//IF THE ROW DO NOT EXIST, RETURN FALSE
if (!document.getElementById(tblNom+'_row_'+row)) return false;
//ELSE, DELETE IT
var numRow = document.getElementById(tblNom+'_row_'+row).rowIndex;
document.getElementById(tblNom).deleteRow(numRow);
return true;
}

//SET A ROW VISIBLE
this.showRow = function(row)
{
//IF NOT MULTISELECT CLEAR THE LAST SELECTED ROW
if (!rowSel && eval('sel'+tblNom))
eval('sel'+tblNom+'.className = \'nselRow\';');
//SAVE THE ROW IN TO A VARIABLE
eval('sel'+tblNom+' = document.getElementById(\''+tblNom+'_row_'+row+'\');');

document.getElementById(tblNom+'_row_'+row).className = 'clikRow';
document.getElementById(tblNom+'_body').scrollTop = document.getElementById(tblNom+'_row_'+row).offsetTop;
}

//SET A COL VISIBLE
this.showCol = function(col)
{
document.getElementById(tblNom+'_body').scrollLeft = document.getElementById(tblNom+'_col_'+col).offsetLeft-5;
}

//RESIZE THE LAST TITLE CELL
//HELPS TO KEEP THE ROWS AND TITLES ALIGNED
this.adjustTit = function()
{
dtaTbl = document.getElementById(tblNom+'_body');
//SET THE LAST TITLE CELL WIDTH AS THE SCROLLBAR WIDTH IF VERTICAL AND HORIZONTAL SCROLLBARS EXIST
if ((dtaTbl.clientWidth < dtaTbl.scrollWidth) && (dtaTbl.clientHeight < dtaTbl.scrollHeight))
document.getElementById(tblNom+'_endtit').width = dtaTbl.offsetWidth-dtaTbl.clientWidth;
}

//JSGrid MAKER
JSGrid.prototype.show = function(elmPad)
{
//COMBO FIELDS COUNTER, SIX IS THE
//FIRST NOT DELCARED PARRAMETER
var numCmb = 6;
//TYPE COUNTER
var cntTyp = 0;
//THE HEADER CONTAINER
strHTML = '<div id="'+tblNom+'_head" name="'+tblNom+'_head" style="width:100%;overflow:hidden;"><table id="'+tblNom+'_titles" name="'+tblNom+'_titles" class="titles" border="0" cellpadding="0" cellspacing="0"><thead><tr>\n';
//THE TITLES INFO
for (var i in aryCol)
{
//EVERY ELEMENT IN aryCol HAS THE DATA TYPE, THE COLUMN WIDTH, THE NAME OF THE COLUMN, THE RESIZEABLE CONFIGURATION
//AND THE CONFIGURATION FOR EDITION, ALL THEM SEPARATED BY PIPE "|", THE LAS TWO PARAMETERS ARE NOT MANDATORY, ITS DEFAULT VALUE IS TRUE
//THE IMAGE IN THE ROW FORCE THE WIDTH SIZE
aryTmp = aryCol[i].split('|');
//USE GRID ID, "col" STRING AND COLUMN NAME AS TITLE ID
strHTML += '<th id="'+tblNom+'_col_'+i+'" unselectable="on" width="'+aryTmp[1]+'">    '+aryTmp[2]+'    <img border="0" height="0" width="'+aryTmp[1]+'"></th>\n';
//USE A LITTLE TD TO USE AS RESIZER
strHTML += '<td';
//IF ITS RESIZEABLE USE THE "rze_" PREFIX
//ELSE ITS ONLY A COLUMNS SEPARATORNUMBER
if (!aryTmp[3] || eval(aryTmp[3]))
strHTML += ' id="rze_'+tblNom+'_col_'+i;
else
strHTML += ' id="sep_'+tblNom+'_col_'+i;
strHTML += '" unselectable="on" width="2" style="border:none;';
//USE THE RESIZE CURSOR FOR RESIZEABLE COLUMNS
if (!aryTmp[3] || eval(aryTmp[3]))
strHTML += 'cursor:w-resize;';
strHTML += '"><img border="0" height="0" width="2"></td>\n';
//IF THE COLUMN DATA TYPE IS A COMBOBOX, SET THE APPROPIATE ARRAY FOR GET IT'S VALUES
if (aryTmp[0] == 'cmb')
{
//WE SHOULD RECIVE AN ARRAY PARAMETER FOR EACH COMBOBOX FIELD IN THE SAME ORDER
//SO WE MAKE A POINTER TO THE ARRAY WITH THE GRID ID, "col" STRING AND THE FIELD ID
eval('cmb'+tblNom+'_col_'+i+' = this.arguments['+numCmb+'];');
numCmb++;
}
//GET THE COLUMN KIND AND ITS EDITABLE CONFIG
if (aryTmp[4]) this.aryEdt[cntTyp] = eval(aryTmp[4]);
else this.aryEdt[cntTyp] = true;
this.aryTyp[cntTyp] = aryTmp[0];
cntTyp++;
}

strHTML += '<th unselectable="on" width="100%"><img id="'+tblNom+'_endtit" border="0" height="0" width="0"></th>\n';
strHTML += '</tr></thead></table></div>\n';
//THE ROWS BODY
strHTML += '<div id="'+tblNom+'_body" name="'+tblNom+'_body" unselectable="on" style="height:'+hgtSze+'px;width:100%;overflow:auto;" onscroll="document.getElementById(\''+tblNom+'_head\').scrollLeft=this.scrollLeft;">\n';
//THE ROWS CONTAINER
strHTML += '<table align="left" id="'+tblNom+'" name="'+tblNom+'" class="grid" border="0" cellpadding="0" cellspacing="0">\n';

strHTML += '<tbody><tr style="height: 0px;">\n';
//TRICK FOR KEEP THE WIDTH
for (var i in aryCol)
strHTML += '<td unselectable="on" width="1"></td>\n';
strHTML += '</tr>';

for (var i in aryDat)
{
//GET FROM aryDat THE ROWS INFO SEPARATED BY PIPE "|"
aryTmp = aryDat[i].split('|');
//USE GRID ID, "row" STRING AND ROW KEY AS ROW ID
strHTML += '<tr id="'+tblNom+'_row_'+i+'" class="nselRow">\n';
for(e=0;e<aryTmp.length;e++)
{
//IF THE FIELD IS A CHECKBOX
//USE THE GRID ID, "chk" STRING, COLUMN INDEX AND ROW KEY AS CHECKBOX ID
if (this.aryTyp[e] == 'chk')
strHTML += '<td unselectable="on" align="center"><input id="'+tblNom+'_chk_'+e+'_'+i+'" type="checkbox" '+aryTmp[e]+'></td>\n';
else
strHTML += '<td unselectable="on" >'+aryTmp[e]+'</td>\n';
}
strHTML += '</tr>\n';
}
//ENDS THE GRID
strHTML += '</tbody></table></div>\n';
//WAIT BACKGROUND, YOU CAN CONFIGURE IT STYLE HERE AND YOU CAN USE A LABEL LIKE "please wait" OR USE AN ANIMATE GIF IMAGE TO SHOW IT WHILE IT'S SORTING
strHTML += '<div id="'+tblNom+'_back" name="'+tblNom+'_back" style="width:0;height:0;top:0;left:0;position:absolute;border:0px;color:#000000;background:#2B8D2B;filter:alpha(opacity=75);-moz-opacity:.75;opacity:.75;visibility:hidden;"></div>\n';
//WRITE THE GRID INTO THE ELEMENT PARENT
elmPad.innerHTML = strHTML;
//START THE CURRENT SELECTED ROW VARIABLE
eval('sel'+tblNom+' = null;');
//START THE CURRENT EDITED CELL VARIABLE
eval('edt'+tblNom+' = null;');

//RESIZE ALL THE DATA COLUMNS AS THE TITLE WIDTH
//THIS IS THE VALUE THAT WE SUM TO THE td TO KEEP THE COLUMNS ALIGNED WITH THEIR HEADS
//IN THIS CASE THESE ARE THE VALUES FOR IE OR OTHERS, BUT IF THE STYLE CHANGE THEY MAY CHANGE TOO
var brdWth = (document.all)?1:3;
var arrHead = document.getElementById(tblNom+'_titles').getElementsByTagName('thead')[0].getElementsByTagName('th');
var arrCols = document.getElementById(tblNom).getElementsByTagName('tbody')[0].getElementsByTagName('tr')[0].getElementsByTagName('td');
for(var i=0;i<arrCols.length;i++)
arrCols[i].style.width = arrHead[i].offsetWidth + brdWth + ((document.all)?(i/2):0);
//ADJUST THE LAST TITLE CELL IF IT IS NECESARY
this.adjustTit();

//RESIZE & POSITIONING THE WAITING BACKGROUND
var top = 0;
var left = 0;
var bak = document.getElementById(tblNom).parentNode;
while(bak != null )
{
//GET THE REAL TOP AND LEFT
top += bak.offsetTop;
left += bak.offsetLeft;
bak = bak.offsetParent;
}
document.getElementById(tblNom+'_back').style.top = top;
document.getElementById(tblNom+'_back').style.left = left;
document.getElementById(tblNom+'_back').style.height = document.getElementById(tblNom).parentNode.style.height;
document.getElementById(tblNom+'_back').style.width = document.getElementById(tblNom+'_body').parentNode.offsetWidth;

//ON IE THE MOUSEOVER AND MOUSEOUT BECOMES TOO SLOW
//WHEN THE TABLE HAS MANY CELLS (ARROUND 1000), AND
//MAYBE WE WANT TO DISABLE THE NEXT TWO EVENTS,
//ENCLOSING THEM INTO THE NEXT CONDITION, USING
//THE LIMITED NUMBER OF ROWS THAT WE WILL NEED:

/*
if (!document.all || aryDat.length < 200)
{
*/

//SET MOUSEOVER FUNCTION FROM ROWS
document.getElementById(tblNom).onmousemove = function(evt)
{
evt = evt || event;
tar = evt.target || event.srcElement;
par = tar.parentNode;
if (par.rowIndex && par.className != 'clikRow')
par.className = 'yselRow'; //HIGHLIGHT ROW ON mouseover & NOT SELECTED ROW
}

//SET MOUSEOUT FUNCTION FROM ROWS
document.getElementById(tblNom).onmouseout = function(evt)
{
evt = evt || event;
tar = evt.target || event.srcElement;
par = tar.parentNode;
if (par.rowIndex && par.className != 'clikRow')
par.className = 'nselRow'; //CLEAR SELECTED ROW ON mouseout & NOT SELECTED ROW
}

/*
}//ENDS: if (!document.all || aryDat.length < 200)
*/

//SET CLICK FUNCTION FROM ROWS
//CHANGE THE ROW STYLE ON CLICK EVENT
document.getElementById(tblNom).onclick = function(evt)
{
evt = evt || event;
tar = evt.target || event.srcElement;
par = tar.parentNode;

//VALIDATE CHANGE VALUE FOR CHECKBOXES
if (tar.type == 'checkbox')
{
//IF ITS NOT EDITABLE RESTORE ITS VALUE
if (eval('!'+tblNom+'.aryEdt['+par.cellIndex+']'))
tar.checked = !tar.checked;
else
{
//GET THE FIELD TITLES ARRAY
var arrHead = document.getElementById(tblNom+'_titles').getElementsByTagName('thead')[0].getElementsByTagName('th');
//SET THE PARAMETER STRING FOR CHANGE VALUES: ROW KEY, COLUMN NAME, CURRENT VALUE AND CHANGED VALUE
var strPar = '\''+par.parentNode.id.substring(par.parentNode.id.indexOf('_row_')+5)+'\',\''+arrHead[par.cellIndex].id.substring(arrHead[par.cellIndex].id.indexOf('_col_')+5)+'\','+!tar.checked+','+tar.checked;
if (!eval(funVal+'('+strPar+');')) tar.checked = !tar.checked;
}
}

//FOR NON IE BROWSERS, CANCEL THE EVENT IF CLICKED ON A CONTROL
if (tar.tagName == 'INPUT' || tar.tagName == 'SELECT') return;

//FORCE BLUR ON EDITED CELL FOR IE
if (document.all && eval('edt'+tblNom+' != null')) window.focus();

if (!rowSel && eval('sel'+tblNom)) //IF NOT MULTISELECT CLEAR THE LAST SELECTED ROW
eval('sel'+tblNom+'.className = \'nselRow\';');
else if (evt.shiftKey) //ON shift+click EVENT, SELECT THE GROUP OF ROWS
clrRow(true);
else if (!evt.ctrlKey) //ON NON ctrl+click EVENT, CLEAR SELECTED ROWS
clrRow(false);

//SET clikRow STYLE TO THE CLICKED ROW
par.className = 'clikRow';
//SAVE THE CLICKED ROW IN TO A VARIABLE
eval('sel'+tblNom+' = par;');

//FOR shift+click & NON ctrl+click EVENTS
function clrRow(shf)
{
//CLEAR SELECTED ROWS
var arrRows = document.getElementById(tblNom).getElementsByTagName('tr');
for(var i=0;i<arrRows.length;i++) arrRows[i].className = 'nselRow';
if (shf)
{ //IF SHIFT KEY PRESSED
//GET THE LAST AND THE CURRENT ROWS CLICKED
var endRow = par.rowIndex;
var iniRow = eval('sel'+tblNom+'.rowIndex');
if (iniRow > endRow){endRow = iniRow;iniRow = par.rowIndex;}
//SET clikRow STYLE TO THE GROUP OF ROWS SELECTED
for(i=iniRow;i<=endRow;i++) arrRows[i].className = 'clikRow';
}
}
}

//SET DOUBLE CLICK FUNCTION FROM ROWS
//EDIT SELECTED CELL, MIDIFIED FROM THE ORIGINAL SCRIPT BY
//kaka vijes, i can do it, Magnus Gudmundsson, Martin Honnen,
//Sachin S, william baney, felix norambuena (www.faqts.com)
document.getElementById(tblNom).ondblclick = function(evt)
{
var strFnc;
var strPnt = "";
evt = evt || event;
cel = evt.target || event.srcElement;

//IF THE CELL IS NOT EDITABLE RETURN
if (eval('!'+tblNom+'.aryEdt['+cel.cellIndex+']')) return;

//IF THERE IS NOT ANOTHER CELL EDITED
if (eval('edt'+tblNom+' != cel') && eval('edt'+tblNom+' == null'))
{
var strTpo = eval(tblNom+'.aryTyp['+cel.cellIndex+']');
//IF THE CELL IS NOT A CHECKBOX
if (strTpo && strTpo != 'chk')
{
//SET THE VARIABLE WITH THE CURRENT EDITED CELL
eval('edt'+tblNom+' = cel;');
//FOR IE USE innerText INSTEAD OF textContent
strFnc = (document.all)?'innerText':'textContent';
//IF THE FIELD IS A COMBO LIST, USE A select TAG, ELSE USE A input TAG
strTpo = (strTpo=='cmb')?'select':'input';
//GET THE FIELD TITLES ARRAY
var arrHead = document.getElementById(tblNom+'_titles').getElementsByTagName('thead')[0].getElementsByTagName('th');

//GET THE CURRENT EDITED VALUE
var curVal = cel.innerHTML;
if (strTpo == 'select')
{
for (var i in eval('cmb'+arrHead[cel.cellIndex].id))
{
if (eval('cmb'+arrHead[cel.cellIndex].id+'[\''+i+'\']==\''+curVal+'\''))
{
curVal = i;
break;
}
}
}

//SET THE PARAMETER STRING FOR CHANGE VALUES: ROW KEY, COLUMN NAME,
//COLUMN CURRENT VALUE, WE'LL ADD THE CHANGED VALUE ON RUN TIME
var strPar = '\''+cel.parentNode.id.substring(cel.parentNode.id.indexOf('_row_')+5)+'\',\''+arrHead[cel.cellIndex].id.substring(arrHead[cel.cellIndex].id.indexOf('_col_')+5)+'\',\''+curVal+'\'';

//SET THE FIELD FOR EDITION, AND SET THE EVENTS escape key FOR CANCEL
//AND blur & enter key FOR SAVE IF funVal RETURNS TRUE. BEFORE CHANGE
//IT CHECKS IF THE EDITION IS NOT CANCELED BY THE OTHER EVENT AND IF
//THE CURRENT VALUE IS NOT THE SAME TO THE MODIFIED VALUE
var strEdt = '<'+strTpo+' id="celedt" size="1" value="' + curVal + '"' +
' onblur="if (this.parentNode){edt'+tblNom+'=null;' +
' if (\''+curVal+'\' != this.value){' +
' if ('+funVal+'('+strPar+',this.value)){' +
' this.parentNode.'+strFnc+'=';
if (strTpo == 'select')
strEdt+=' cmb'+arrHead[cel.cellIndex].id+'[this.value];';
else
strEdt+=' this.value;';
strEdt+=' return;}}' +
' this.parentNode.'+strFnc+'=\'' + cel.innerHTML + '\';}"' +
' onkeyup="if (this.parentNode){' +
' if(event.keyCode==27){edt'+tblNom+'=null;' +
' this.parentNode.'+strFnc+'=\'' + cel.innerHTML + '\';}' +
' else if(event.keyCode==13){this.blur();}}">';
if (strTpo == 'select')
{
//USE THE TITLE ID FOR GET THE APPROPIATE COMBOBOX
for (var i in eval('cmb'+arrHead[cel.cellIndex].id))
{
var strVal = eval('cmb'+arrHead[cel.cellIndex].id+'[\''+i+'\']');
strEdt += '<option value="'+i+'">'+strVal+'</option>';
}
strEdt += '</select>';
}
cel.innerHTML = strEdt;
//RESIZE THE CONTROL TO THE WIDTH OF THE EDITED CELL
document.getElementById('celedt').style.width = cel.offsetWidth-4;
//IF THE FIELD IS A COMBOBOX, SET THE CURRENT VALUE IN THE LIST
if (strTpo == 'select')
document.getElementById('celedt').value = curVal;
else
document.getElementById('celedt').select();

document.getElementById('celedt').focus();
}
}
}

//SET MOUSE DOWN FUNCTION FROM TITLES
//SET THE CURRENT COLUMN FOR REZISE
document.getElementById(tblNom+'_titles').onmousedown = function(evt)
{
evt = evt || event;
tar = evt.target || event.srcElement;
//IF THE CLICKED CELL'S ID STARTS WITH reze_
if (tar.id.indexOf('rze_') == 0)
eval(tblNom+'.colSze=\''+tar.id.substring(tar.id.indexOf('_')+1)+'\';');
}

//SET MOUSE UP FUNCTION FROM TITLES
//REZISE THE ASIGNED CELL ON MOUSE DOWN
document.getElementById(tblNom+'_titles').onmouseup = function(evt)
{
//DO IT ONLY IF THE CURRENT CELL REGISTERED FOR REZISE IS REAL
if (document.getElementById(eval(tblNom+'.colSze')))
{
evt = evt || event;
tar = evt.target || event.srcElement;
//GET THE NEW SIZE OF THE CELL
var newSze = (evt.pageX || evt.clientX + document.body.scrollLeft - document.body.clientLeft);
newSze -= document.getElementById(tblNom+'_head').parentNode.offsetLeft;
newSze -= document.getElementById(eval(tblNom+'.colSze')).offsetLeft;
newSze += document.getElementById(tblNom+'_head').scrollLeft;
if (newSze > 0)
{
//SET THE NEW SIZE TO THE TITLE CELL AND ITS DATA COLUMNS
var intIdx = document.getElementById(eval(tblNom+'.colSze')).cellIndex/2;
document.getElementById(eval(tblNom+'.colSze')).getElementsByTagName('img')[0].style.width = newSze;
newSze = document.getElementById(eval(tblNom+'.colSze')).offsetWidth;
document.getElementById(tblNom).getElementsByTagName('tr')[0].getElementsByTagName('td')[intIdx].style.width = newSze + brdWth + ((document.all)?(intIdx/2):0);
//ADJUST THE LAST TITLE CELL IF IT IS NECESARY
eval(tblNom+'.adjustTit();');

}
}
//SET NULL THE CURRENT RESIZING CELL
eval(tblNom+'.colSze = null');
}

//SET CLICK FUNCTION FROM TITLES
document.getElementById(tblNom+'_titles').onclick = function(evt)
{
evt = evt || event;
tar = evt.target || event.srcElement;
//IF A CELL WAS CLICKED AND IT IS PAIR
if (tar.cellIndex > -1 && tar.cellIndex%2 == 0)
{
//CHANGE TO NULL A POSIBLE COLUMN RESIZING
eval(tblNom+'.colSze = null');
//MAKE THE WAITING BACKGROUN VISIBLE
document.getElementById(tblNom+'_back').style.visibility = 'visible';
//CALL THE SORT FUNCTION
setTimeout(tblNom+'.sort('+tar.cellIndex/2+')', 100);
}
}

//SORT TABLES BY SELECTED FIELD, MIDIFIED FROM THE ORIGINAL SCRIPT:
//"Sort html table's cols" BY: Eric Pascarello
this.sort = function(intCol)
{
var strMethod;
var intDir = 0;
var arrChk = new Array();
var strTpo = eval(tblNom+'.aryTyp['+intCol+']');

//IF THE DATA TYPE IS NOT DEFINED (MEYBE IF THE LAST COLUMN IS CLICKED)
if (strTpo != undefined)
{
//GET THE HEADER AND ROWS INFO OF THE TABLE (tblNom)
var arrHead = document.getElementById(tblNom+'_titles').getElementsByTagName('thead')[0].getElementsByTagName('th');
var arrRows = document.getElementById(tblNom).getElementsByTagName('tbody')[0].getElementsByTagName('tr');
//GET THE CHECKS IN THE TABLE (tblNom)
var arrChks = document.getElementById(tblNom).getElementsByTagName('INPUT');

//MODIFY THE STYLE OF THE SELECTED TITLE
intDir = (arrHead[intCol].className == "asc")?-1:1;
arrHead[intCol].className = (arrHead[intCol].className == "asc")?"des":"asc";

//RESET THE STYLE FOR THE OTHER TITLES
for(var i=0;i<arrHead.length;i++)
if(i!=intCol) arrHead[i].className = "";

//CLONE THE ROWS FOR SORT
var arrRowsSort = new Array();
for(i=1;i<arrRows.length;i++)
arrRowsSort[i-1]=arrRows[i].cloneNode(true);

//FOR IE, SAVE THE STATUS OF THE CHECKS INTO AN ARRAY
if (document.all)
for(i=0;i<arrChks.length;i++)
if (arrChks[i].checked)
arrChk.push(arrChks[i].id);

//SORT ROWS
arrRowsSort.sort(function(a,b)
{
//ORDER BY CELL CONTENT
if (document.all)
{
var aCell = a.getElementsByTagName("td")[intCol].innerText;
var bCell = b.getElementsByTagName("td")[intCol].innerText;
}
else
{
var aCell = a.getElementsByTagName("td")[intCol].textContent;
var bCell = b.getElementsByTagName("td")[intCol].textContent;
}
//CONVERT DATA TYPE int, float, date OR ANY OTHER
switch (strTpo)
{
case "int":
aCell = parseInt(aCell);
bCell = parseInt(bCell);
break;
case "float":
aCell = parseFloat(aCell);
bCell = parseFloat(bCell);
break;
case "date":
aCell = new Date(aCell);
bCell = new Date(bCell);
break;
}
//RETURNS 0 IF DATA ONE (aCell) IS EQUAL AS DATA TWO (bCell)
//RETURNS 1 IF DATA TWO IS LESSER THAN DATA ONE
//RETURNS -1 IF DATA ONE IS LESSER THAN DATA TWO
//FOR SORT DESC, THE SIGN OF 1 OR -1 CHANGES
return (aCell>bCell)?intDir:(aCell<bCell)?-intDir:0;
});

for(i=1;i<arrRows.length;i++)
arrRows[i].parentNode.replaceChild(arrRowsSort[i-1],arrRows[i]);

//FOR IE
if (document.all)
{
//UNSELECT ALL THE CHECKS IN THE TABLE
for(i=0;i<arrChks.length;i++)
arrChks[i].checked = false;
//RESTORE THE SELECTED CHECKS FROM THE ARRRAY
for(i=0;i<arrChk.length;i++)
document.getElementById(arrChk[i]).checked = true;
}
}
//MAKE THE WAITING BACKGROUND HIDDEN
document.getElementById(tblNom+'_back').style.visibility = 'hidden';
//MAKE VISIBLE THE FIRST GRID ROW
document.getElementById(tblNom+'_body').scrollTop = 0;
}
}
return this;
}

test.html

<html>
<head>
<title>JSGRID</title>
<style type="text/css">
/*STYLE FOR NON SELECTED ROWS*/
.nselRow{color: #000000;background-color: #4C76A3;}
/*STYLE FOR HIGHLIGHTED ROWS*/
.yselRow{color: #FFFFFF;background-color: #6D9DD0;}
/*STYLE FOR SELECTED ROWS*/
.clikRow{color: #FFFFFF;background-color: #627B4F;}
/*ICON TO MARK THE ASCENDENT ORDER*/
th.asc{background-image: url(imgs/asc.gif);}
/*ICON TO MARK THE DECENDENT ORDER*/
th.des{background-image: url(imgs/des.gif);}
/*STYLE FOR TITLES CONTAINER*/
.titles{color: #000000;border-width: 1px;border-spacing: 1px;border-style: solid;}
/*STYLE FOR TITLES COLUMNS, SHOULD NOT REMOVE OR CHANGE: background-repeat AND background-position*/
/*IF CHANGE STYLES THAT AFFECT THE SIZES, READ THE NOTE FOR brdWth VARIABLE IN jsgrid.JS*/
.titles th{font-family: 'Arial';font-size: 12px;height: 20px;cursor: default;border-width: 1px;text-align: center;
border-style: outset;border-color: #53842E;background-color: #53842E;-khtml-user-select: none;
-moz-user-select: none;user-select: none;background-repeat:no-repeat;background-position: 2% left;}
/*STYLE FOR DATA GRID CONTAINER, SHOULD NOT REMOVE OR CHANGE: table-layout AND width*/
.grid{color: #000000;border-style: none;border-collapse:collapse;table-layout: fixed;width: 1px;}
/*STYLE FOR DATA GRID CELLS, SHOULD NOT REMOVE OR CHANGE: white-space AND overflow*/
/*IF CHANGE STYLES THAT AFFECT THE SIZES, READ THE NOTE FOR brdWth VARIABLE IN jsgrid.JS*/
.grid td{font-family: 'Arial';font-size: 12px;border-right-width: 1px;border-right-style: solid;border-right-color: #C8C8C8;
-khtml-user-select: none;-moz-user-select: none;user-select: none;white-space: nowrap;overflow: hidden;}
</style>

<script type="text/javascript" src="jsgrid.js"></script>
</head>
<body bgcolor="#FFFFFF">

<center>
<div id="tabla0" style="width:450px;border:1px solid #627B4F;background-color:#627B4F;"></div>
<input type="button" value="showRow()" onclick="x.showRow('18');">
 
<input type="button" value="showCol()" onclick="x.showCol('3');">
 
<input type="button" value="getSelRows()" onclick="var arrSel=x.getSelRows(); for(var i=0;i<arrSel.length;i++){alert(arrSel[i]);}">
 
<input type="button" value="setSelRows()" onclick="var arrSel=new Array('1','3','5'); x.setSelRows(arrSel);">
</center>
<br>

<div id="tabla1" style="width:90%;border:1px solid #627B4F;background-color:#627B4F;"></div>
<input type="button" value="getCelObj()" onclick="alert(y.getCelObj('row4','usuario').innerHTML);">
 
<input type="button" value="addNewRow()" onclick="alert(y.addNewRow('row20','checked','Banco Tres','file 10','38','Lorenzo','registro agregado'));">
 
<input type="button" value="removeRow()" onclick="alert(y.removeRow('row3'));">
<br>

<center>
<div id="tabla2" style="width:550px;border:1px solid #627B4F;background-color:#627B4F;"></div>
<input type="button" value="getNumRows()" onclick="alert(z.getNumRows());">
 
<input type="button" value="getNumCols()" onclick="alert(z.getNumCols());">
</center>
<br>

<script language="javascript">
//COLUMNS FOR THE FIRST SAMPLE GRID
var aryCol0 = new Array();
aryCol0[0]='str|150|Banco';
aryCol0[1]='str|100|Archivo';
aryCol0[2]='int|75|Tamaño';
aryCol0[3]='str|200|Usuario';
aryCol0[4]='str|250|Observaciones';

//DATA FOR THE FIRST SAMPLE GRID
var aryDat0 = new Array();
aryDat0[0]='Banco Uno|file 01|35|Pedro|registro de prueba con texto muy muy muy muy muy muy muy muy muy muy largo';
aryDat0[1]='Banco Uno|file 02|135|Pablo|obsercaciones de prueba';
aryDat0[2]='Banco Dos|file 03|15|Carlos|';
aryDat0[3]='Banco Dos|file 04|5|Toño|esta es una prueba de observaciones';
aryDat0[4]='Banco Tres|file 05|23|Juan|';
aryDat0[5]='Banco Cuatro|file 06|325|Luis|campo de observaciones';
aryDat0[6]='Banco Cinco|file 07|14|Raul|mas observaciones para las pruebas';
aryDat0[7]='Banco Cinco|file 08|350|Miguel|';
aryDat0[8]='Banco Uno|file 09|57|Martin|';
aryDat0[9]='Banco Dos|file 10|38|Lorenzo|ultimo registro de prueba';
aryDat0[10]='Banco Uno|file 01|35|Pedro|registro de prueba con texto';
aryDat0[11]='Banco Uno|file 02|135|Pablo|obsercaciones de prueba';
aryDat0[12]='Banco Dos|file 03|15|Carlos|';
aryDat0[13]='Banco Dos|file 04|5|Toño|esta es una prueba de observaciones';
aryDat0[14]='Banco Tres|file 05|23|Juan|';
aryDat0[15]='Banco Cuatro|file 06|325|Luis|campo de observaciones';
aryDat0[16]='Banco Cinco|file 07|14|Raul|mas observaciones para las pruebas';
aryDat0[17]='Banco Cinco|file 08|350|Miguel|';
aryDat0[18]='Banco Dos|file 09|57|Martin|';
aryDat0[19]='Banco Uno|file 10|38|Lorenzo|ultimo registro de prueba';

//CREATE THE FIRST DATA GRID OBJECT CALLED x, SO, WE SEND 'x' AS THE FIRST PARAMETER
//THIS ALL PARAMETERS ARE MANDATORY: NAME OF THE OBJECT, COLUMNS ARRAY, DATA ARRAY, GRID HEIGTH, SINGLE SELECT? AND VALIDATOR FUNCTION
x = new JSGrid('x', aryCol0, aryDat0, 150, true, function(){return true;});
//SHOW THE FIRST DATA GRID INTO THE "tabla0" CONTAINER
x.show(document.getElementById('tabla0'));
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//COLUMNS FOR THE SECOND SAMPLE GRID
//IN THIS CASE WE USE A CHECKBOX FOR THE COLUMN "nuevo" AND COMBOS FOR
//THE COLUMNS "banco" AND "usuario" AND USE STRINGS AS COLUMNS KEYS ALSO
//SEE THAT "nuevo" CAN'T RESIZE, BECAUSE ITS FOURTH PARAMETER IS false
//AND "archivo" CAN'T BE EDITED, BECAUSE ITS FIFTH PARAMETER IS false
var aryCol1 = new Array();
aryCol1['nuevo']='chk|25|Nvo|false|true';
aryCol1['banco']='cmb|150|Banco';
aryCol1['archivo']='str|100|Archivo|true|false';
aryCol1['tamanio']='int|75|Tamaño';
aryCol1['usuario']='cmb|200|Usuario';
aryCol1['observaciones']='str|250|Observaciones';

//DATA FOR THE SECOND SAMPLE GRID
//USING STRINGS AS ROWS KEYS. USE THE STRING "checked"
//FOR THE MARKED CHECKBOXES OR EMPTY STRING FOR UNMARKED
var aryDat1= new Array();
aryDat1['row0']='checked|Banco Uno|file 01|35|Pedro|registro de prueba con texto muy muy muy muy muy muy muy muy muy muy largo';
aryDat1['row1']='checked|Banco Uno|file 02|135|Pablo|obsercaciones de prueba';
aryDat1['row2']='|Banco Dos|file 03|15|Carlos|';
aryDat1['row3']='|Banco Dos|file 04|5|Toño|esta es una prueba de observaciones';
aryDat1['row4']='|Banco Tres|file 05|23|Juan|';
aryDat1['row5']='|Banco Cuatro|file 06|325|Luis|campo de observaciones';
aryDat1['row6']='|Banco Cinco|file 07|14|Raul|mas observaciones para las pruebas';
aryDat1['row7']='|Banco Cinco|file 08|350|Miguel|';
aryDat1['row8']='checked|Banco Uno|file 09|57|Martin|';
aryDat1['row9']='|Banco Dos|file 10|38|Lorenzo|ultimo registro de prueba';
aryDat1['row10']='checked|Banco Uno|file 01|35|Pedro|registro de prueba con texto';
aryDat1['row11']='checked|Banco Uno|file 02|135|Pablo|obsercaciones de prueba';
aryDat1['row12']='|Banco Dos|file 03|15|Carlos|';
aryDat1['row13']='|Banco Dos|file 04|5|Toño|esta es una prueba de observaciones';
aryDat1['row14']='|Banco Tres|file 05|23|Juan|';
aryDat1['row15']='|Banco Cuatro|file 06|325|Luis|campo de observaciones';
aryDat1['row16']='|Banco Cinco|file 07|14|Raul|mas observaciones para las pruebas';
aryDat1['row17']='|Banco Cinco|file 08|350|Miguel|';
aryDat1['row18']='|Banco Dos|file 09|57|Martin|';
aryDat1['row19']='checked|Banco Uno|file 10|38|Lorenzo|ultimo registro de prueba';

//WE NEED AN ARRAY FOR EACH COMBOBOX COLUMN
var aryBank = new Array();
aryBank['BU']='Banco Uno';
aryBank['BD']='Banco Dos';
aryBank['BT']='Banco Tres';
aryBank['BC']='Banco Cuatro';
aryBank['BS']='Banco Cinco';

var aryUser = new Array();
aryUser[0]='Pedro';
aryUser[1]='Pablo';
aryUser[2]='Carlos';
aryUser[3]='Toño';
aryUser[4]='Juan';
aryUser[5]='Luis';
aryUser[6]='Raul';
aryUser[7]='Miguel';
aryUser[8]='Martin';
aryUser[9]='Lorenzo';

//SAMPLE FUNCTION TO VALIDATE DATA CHANGES IN THE SECOND SAMPLE GRID
//RECIVE THE ROW KEY AND COL KEY OF THE CHANGED CELL, AND THE
//CURRENT VALUE AND THE NEW VALUE, HERE WE CAN VALIDATE THIS INFO
//AND RETURN true TO MAKE THE CHANGE OR false TO CANCEL THE CHANGE
function yValid(row, col, cur, val)
{
if (col == 'tamanio')
{
//VALIDATE THAT "tamanio" IS A NUMBER
if (isNaN(val))
{
alert ('The value is not a number');
return false;
}
}
else if (col == 'usuario')
{
//VALIDATE THAT "usuario" IS DIFERENT TO 2
if (val == '2')
{
alert ('Carlos does not work here any more');
return false;
}
}
return true;
}

//CREATE THE SECOND DATA GRID OBJECT CALLED y, SO, WE SEND 'y' AS THE FIRST PARAMETER
//IN THIS CASE WE HAVE TO SEND TWO EXTRA PARAMETERS, THE ARRAYS FOR EACH COMBOBOX COLUMN
y = new JSGrid('y', aryCol1, aryDat1, 200, false, 'yValid', aryBank, aryUser);
//SHOW THE SECOND DATA GRID INTO THE "tabla1" CONTAINER
y.show(document.getElementById('tabla1'));
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//COLUMNS FOR THE THIRD SAMPLE GRID
var aryCol2 = new Array();
aryCol2['num']='int|25|Num';
aryCol2['nom']='str|150|Nombre';

//DATA FOR THE THIRD SAMPLE GRID
var aryDat2= new Array();
for (i=0;i<1000;i++)
aryDat2['row'+i]=i+'|nombre '+i;

//FUNCTION TO VALIDATE DATA CHANGES IN THE THIRD SAMPLE GRID
//IN THIS CASE WE RETURN false ALLWAYS, SO THE CHANGES WILL BE CANCELD
function zValid(row, col, cur, val)
{
alert('ROW: '+row+', COL: '+col+', VALUE: '+cur+', CHANGE: '+val);
return false;
}

//CREATE THE THIRD DATA GRID OBJECT CALLED z, SO, WE SEND 'z' AS THE FIRST PARAMETER
z = new JSGrid('z', aryCol2, aryDat2, 200, false, 'zValid');
//SHOW THE THIRD DATA GRID INTO THE "tabla2" CONTAINER
z.show(document.getElementById('tabla2'));
</script>
</body>
</html>

Oracle with wxWidgets

2009-03-28T11:10:00.000-07:00

The second on the list

The source is too a C++ library. I use it for Oracle connections.

If you read the source code and compare it with "Postgres with wxWidgets", now you'll see that are similar and can be adapted to other databases. If you work with MySql and you know the headers and libraries files, you can replace some parts of code to adapt the library to your database.

To use this library in run time, a Oracle instant client is enought, but it should work with any other oracle client.

This source was tested in Windows XP SP2, RedHat 4 and Fedora 8, 9 y 10

You need the Oracle headers:
occiCommon.h, oci1.h, ocidem.h, ocixmldb.h, orl.h, occiControl.h, oci8dp.h, ocidfn.h, odci.h, oro.h, nzerror.h, occiData.h, ociap.h, ociextp.h, oratypes.h, ort.h, nzt.h, occi.h, ociapr.h, oci.h, orid.h, occiAQ.h, occiObjects.h, ocidef.h, ocikpr.h, ori.h, xa.h

And the libraries:
oci.lib, ociw32.lib, oraocci10.lib for Windows

And: libclntsh.so, libocci.so for Linux.

Get the headers and libraries:

You can get the headers and libraries files from www.oracle.com, looking for Oracle C++ Call Interface (OCCI)

WINDOWS:

VC:
-Download the package instantclient-sdk for Windows from www.oracle.com
The package contains the directories include/ and lib/
Copy the headers and libraries to the VC include/ and lib/

Mingw:
-You need the Mingw32 utilities reimp.exe (in package mingw-utils-x.x.x)
And dlltool.exe in package (binutils-x.x.x-mingw32-bin)
If you are working with Mingw, maybe you already have these files
If you don have it, you can download from:
http://www.mingw.org/ or http://sourceforge.net/projects/mingw/

-Create the .def files from oci.lib, ociw32.lib and oraocci10.lib
reimp -d oci.lib
reimp -d ociw32.lib
reimp -d oraocci10.lib

-Create the gcc .a libraries from the .def files
dlltool -k --input-def oci.def --dllname oci.dll --output-lib liboci.a
dlltool -k --input-def ociw32.def --dllname ociw32.dll --output-lib libociw32.a
dlltool -k --input-def oraocci10.def --dllname oraocci10.dll --output-lib liboraocci10.a

-Copy the instantclient-sdk headers to your gcc include directory and the .a files to your gcc lib directory

-Compile with the linker option -loci

LINUX:

-Download and install the rpms instantclient-basic and instantclient-sdk from www.oracle.com

-The directory instantclient_x_x/sdk/include/ contains the .h headers files
-Copy this files to /usr/include/

-Edit the file demo.mk in the directory instantclient_x_x/sdk/demo/
-Find the "section buildoci" and coment the lines:
# $(REMOVE) $(ICLIBHOME)libclntsh$(SO_EXT)
# $(REMOVE) $(ICLIBHOME)libocci$(SO_EXT)
-Make the .so files
make -f demo.mk buildoci EXE=cdemo81 OBJS=cdemo81.o

-Copy the libraries libclntsh.so and libocci.so to /usr/lib/

-You can remove the file instantclient-sdk and the file instantclient-basic
if you won't use this last one

-Compile with the linker option -locci -lclntsh

The Oracle Instant Client version used for this samples is 10.2.0.4

If you don't have a Oracle Client installed, you can use the Oracle Instant Client

Configuring my Oracle Instant Client environment:

WINDOWS:

-Add this lines to the autoexec.bat:

PATH=%PATH%;C:\OracleIC
SET TNS_ADMIN=C:\OracleIC
SET LD_LIBRARY_PATH=C:\OracleIC
SET NLS_LANG=SPANISH_MEXICO.UTF8

-"C:\OracleIC" is the path where you copy the Oracle Instant Client

LINUX:

-Add this lines to your profile (ejm: .bashrc, .bash_profile)

PATH=/usr/lib/oracle/x.x.x/client:$PATH
export TNS_ADMIN=/etc/oracle
export LD_LIBRARY_PATH=/usr/lib/oracle/x.x.x/client:$LD_LIBRARY_PATH
export NLS_LANG=MEXICAN SPANISH_MEXICO.WE8MSWIN1252

-"/usr/lib/oracle/x.x.x/client" is the directory of the Oracle Instant Client libraries

-Copy your tnsnames.ora to the TNS_ADMIN directory

-Change the appropiate NLS_LANG for your Oracle DB

-At runtime, only need the instantclient-basic package

NOTE: Only the library libwxOra requires the oracle include and link options, the application no longer needs them.

I have all the files for VC and Eclipse projects with a sample of use ready for download at SourceForge

Load the library using wx/dynload.h and include the wxOraLib.h file in your app
and include the file wxOraDef.h to your app to let it know the methods of wxOraLib.

The source code:

//---------------------------------------------------------------------------

//

// Name: wxOra.cpp

// Author: eduardo canedo

// Created: 28/05/2007 10:30:00 a.m.

// Description: Exported functions from wxOraLib

//

//---------------------------------------------------------------------------

#include <wx/dynlib.h>

#include "wxOraLib.h"

WXDLL_ENTRY_FUNCTION();

extern "C" WXEXPORT wxOra* oraIni(wxString strTit, wxString strSid, wxString strUsr, wxString strPas)

{

return new wxOra(strTit, strSid, strUsr, strPas);

}

extern "C" WXEXPORT void oraFin(wxOra* oraObj)

{

oraObj->~wxOra();

}

extern "C" WXEXPORT bool oraExe(wxOra* oraObj, wxString strQry)

{

return oraObj->ExecQry(strQry);

}

extern "C" WXEXPORT bool oraSel(wxOra* oraObj, wxString strQry, std::vector< std::vector<wxString> > *aryDat=NULL, wxGridTableBase *grdDat=NULL)

{

return oraObj->SelcQry(strQry, aryDat, grdDat);

}

extern "C" WXEXPORT bool oraArcW(wxOra* oraObj, wxString strArc, wxString strLob, wxString strTbl, wxString strCmp, int numCnd)

{

return oraObj->EscArc(strArc, strLob, strTbl, strCmp, numCnd);

}

extern "C" WXEXPORT bool oraObjW(wxOra* oraObj, wxChar *strArc, size_t size, wxString strLob, wxString strTbl, wxString strCmp, int numCnd)

{

return oraObj->EscLob(strArc, size, strLob, strTbl, strCmp, numCnd);

}

extern "C" WXEXPORT bool oraArcR(wxOra* oraObj, wxString strArc, wxString strLob, wxString strTbl, wxString strCmp, int numCnd)

{

return oraObj->LeeArc(strArc, strLob, strTbl, strCmp, numCnd);

}

extern "C" WXEXPORT int oraObjR(wxOra* oraObj, wxChar *strArc, wxString strLob, wxString strTbl, wxString strCmp, int numCnd)

{

return oraObj->LeeLob(strArc, strLob, strTbl, strCmp, numCnd);

}

//---------------------------------------------------------------------------

//

// Name: wxOraDef.h

// Author: eduardo canedo

// Created: 28/05/2007 10:30:00 a.m.

// Description: Function headers for wxOraLib

//

//---------------------------------------------------------------------------

typedef wxOra* (*oraIni)(wxString, wxString, wxString, wxString);

typedef void* (*oraFin)(wxOra*);

typedef bool* (*oraExe)(wxOra*, wxString);

typedef bool* (*oraSel)(wxOra*, wxString, std::vector< std::vector<wxString> >*, wxGridTableBase*);

typedef int (*oraArcW)(wxOra*, wxString, wxString, wxString, wxString, int);

typedef bool* (*oraObjW)(wxOra*, wxChar*, size_t, wxString, wxString, wxString, int);

typedef int (*oraArcR)(wxOra*, wxString, wxString, wxString, wxString, int);

typedef int (*oraObjR)(wxOra*, wxChar*, wxString, wxString, wxString, int);

//---------------------------------------------------------------------------

//

// Name: wxOraLib.h

// Author: eduardo canedo

// Created: 28/05/2007 10:30:00 a.m.

// Description: wxOra class declaration

//

//---------------------------------------------------------------------------

#ifdef __BORLANDC__

#pragma hdrstop

#endif

#ifndef WX_PRECOMP

#include <wx/wx.h>

#include <wx/dialog.h>

#else

#include <wx/wxprec.h>

#endif

#include <wx/grid.h>

#include <wx/file.h>

#include <vector>

#include <oci.h>

#define MAXLOBLEN 512000 //MAX SIZE FOR LOB'S

class wxOra

{

private:

void ClsLobHnd();

bool ChkErr(OCIError *errhp, sword status);

bool SelLob(wxString strLob, wxString strTbl, wxString strCmp, int numCnd);

public:

wxOra(wxString strTit, wxString strSid, wxString strUsr, wxString strPas);

~wxOra();

bool bolCon;

bool ExecQry(wxString strQry);

bool SelcQry(wxString strQry, std::vector< std::vector<wxString> > *aryDat=NULL, wxGridTableBase *grdDat=NULL);

bool LeeArc(wxString strArc, wxString strLob, wxString strTbl, wxString strCmp, int numCnd);

int LeeLob(wxChar *strArc, wxString strLob, wxString strTbl, wxString strCmp, int numCnd);

bool EscArc(wxString strArc, wxString strLob, wxString strTbl, wxString strCmp, int numCnd);

bool EscLob(wxChar *strArc, size_t size, wxString strLob, wxString strTbl, wxString strCmp, int numCnd);

};

//---------------------------------------------------------------------------

//

// Name: wxOraLib.cpp

// Author: eduardo canedo

// Created: 28/05/2007 10:30:00 a.m.

// Description: wxOra class implementation

//

//---------------------------------------------------------------------------

#include "wxOraLib.h"

static OCISession *authp = (OCISession *) 0;

static OCIDefine *deflob = (OCIDefine *) 0;

static OCILobLocator *blob;

static OCIServer *srvhp;

static OCISvcCtx *svchp;

static OCIError *errhp;

static OCIStmt *stmQry;

static wxString msgTit;

static OCIEnv *envhp;

static sword status;

static text *qry;

wxOra::wxOra(wxString strTit, wxString strSid, wxString strUsr, wxString strPas)

{

bolCon = true;

msgTit = strTit;

text *sid = (text*) strSid.c_str();

text *usr = (text*) strUsr.c_str();

text *pas = (text*) strPas.c_str();

//ENVIRONMENT CREATOR

sword errcode = 0;

wxString strErr;

errcode = OCIEnvCreate((OCIEnv **) &envhp, (ub4) OCI_DEFAULT, (dvoid *) 0, (dvoid * (*)(dvoid *,size_t)) 0, (dvoid * (*)(dvoid *, dvoid *, size_t)) 0, (void (*)(dvoid *, dvoid *)) 0, (size_t) 0, (dvoid **) 0);

if (errcode != 0)

{

strErr.Printf("ERROR en creación de ambiente ORACLE: %d", errcode);

wxMessageBox(strErr, msgTit, wxOK | wxICON_ERROR);

bolCon = false;

}

//START DATABASE CONECTION

(void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &errhp, OCI_HTYPE_ERROR, (size_t) 0, (dvoid **) 0);

(void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &srvhp, OCI_HTYPE_SERVER, (size_t) 0, (dvoid **) 0);

(void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &svchp, OCI_HTYPE_SVCCTX, (size_t) 0, (dvoid **) 0);

if (!ChkErr(errhp, OCIServerAttach( srvhp, errhp, sid, strlen((char*)sid), 0)))

bolCon = false;

if (!ChkErr(errhp, OCIAttrSet( (dvoid *) svchp, OCI_HTYPE_SVCCTX, (dvoid *)srvhp, (ub4) 0, OCI_ATTR_SERVER, (OCIError *) errhp)))

bolCon = false;

if (!ChkErr(errhp, OCIHandleAlloc((dvoid *) envhp, (dvoid **)&authp, (ub4) OCI_HTYPE_SESSION, (size_t) 0, (dvoid **) 0)))

bolCon = false;

if (!ChkErr(errhp, OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION, (dvoid *) usr, (ub4) strlen((char *)usr), (ub4) OCI_ATTR_USERNAME, errhp)))

bolCon = false;

if (!ChkErr(errhp, OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION, (dvoid *) pas, (ub4) strlen((char *)pas), (ub4) OCI_ATTR_PASSWORD, errhp)))

bolCon = false;

if (!ChkErr(errhp, OCISessionBegin ( svchp, errhp, authp, OCI_CRED_RDBMS, (ub4) OCI_DEFAULT)))

bolCon = false;

(void) OCIAttrSet((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX, (dvoid *) authp, (ub4) 0, (ub4) OCI_ATTR_SESSION, errhp);

}

wxOra::~wxOra()

{

ChkErr(errhp, OCISessionEnd(svchp, errhp, authp, OCI_DEFAULT));

ChkErr(errhp, OCIServerDetach(srvhp, errhp, OCI_DEFAULT));

if (authp) (void) OCIHandleFree(authp, OCI_HTYPE_SESSION);

if (srvhp) (void) OCIHandleFree(srvhp, OCI_HTYPE_SERVER);

if (svchp) (void) OCIHandleFree(svchp, OCI_HTYPE_SVCCTX);

if (errhp) (void) OCIHandleFree(errhp, OCI_HTYPE_ERROR);

if (envhp) (void) OCIHandleFree(envhp, OCI_HTYPE_ENV);

}

//EXECUTE QUERY

bool wxOra::ExecQry(wxString strQry)

{

qry = (text*) strQry.c_str();

if (!ChkErr(errhp, OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &stmQry, OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0)))

return false;

if (!ChkErr(errhp, OCIStmtPrepare(stmQry, errhp, qry, (ub4) strlen((char *) qry), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)))

return false;

status = OCIStmtExecute(svchp, stmQry, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT);

if (!ChkErr(errhp, status))

return false;

status = OCITransCommit(svchp, errhp, 0);

if (!ChkErr(errhp, status))

return false;

if (stmQry) (void) OCIHandleFree(stmQry, OCI_HTYPE_STMT);

return true;

}

//SELECT QUERY INTO AN ARRAY OR INTO AN WXGRID

bool wxOra::SelcQry(wxString strQry, std::vector< std::vector<wxString> > *aryDat, wxGridTableBase *grdDat)

{

if (aryDat==NULL && grdDat==NULL)

{

wxMessageBox(wxT("ERROR A data container is required"), msgTit, wxOK | wxICON_ERROR);

return false;

}

else if (aryDat!=NULL && grdDat!=NULL)

{

wxMessageBox(wxT("ERROR Only one data container is required"), msgTit, wxOK | wxICON_ERROR);

return false;

}

ub4 rows = 1;

ub2 type = 0;

ub2 dcml = 0;

ub4 numcols = 0;

wxString strTmp;

qry = (text*) strQry.c_str();

OCIParam *colhd = (OCIParam *) 0;

if (!ChkErr(errhp, OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &stmQry, OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0)))

return false;

if (!ChkErr(errhp, OCIStmtPrepare(stmQry, errhp, (OraText *)qry, (ub4)strlen((char *)qry), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)))

return false;

if (!ChkErr(errhp, OCIStmtExecute(svchp, stmQry, errhp, 0, 0, (OCISnapshot *) 0, (OCISnapshot *) 0, OCI_DESCRIBE_ONLY)))

return false;

if (!ChkErr(errhp, OCIAttrGet((dvoid *)stmQry, OCI_HTYPE_STMT, (dvoid *)&numcols, (ub4 *)0, OCI_ATTR_PARAM_COUNT, errhp)))

return false;

for (int i=1;i<=(int)numcols;i++)

{

type = 0;

dcml = 0;

if (!ChkErr(errhp, OCIParamGet((dvoid *)stmQry, OCI_HTYPE_STMT, errhp, (dvoid **)&colhd, i)))

return false;

if (!ChkErr(errhp, OCIAttrGet((dvoid *)colhd, OCI_DTYPE_PARAM, (dvoid *)&type, (ub4 *)0, OCI_ATTR_DATA_TYPE, errhp)))

return false;

if (!ChkErr(errhp, OCIAttrGet((dvoid *)colhd, OCI_DTYPE_PARAM, (dvoid *)&dcml, (ub4 *)0, OCI_ATTR_SCALE, errhp)))

return false;

switch(type)

{

case SQLT_NUM: /* NUMBER */

break;

case SQLT_INT: /* INTEGER */

break;

case SQLT_UIN: /* UINT */

break;

case SQLT_FLT: /* FLOAT */

break;

case SQLT_CHR: /* VARCHAR2 */

break;

case SQLT_VCS: /* VARCHAR */

break;

case SQLT_AFC: /* CHAR */

break;

case SQLT_AVC: /* CHARZ */

break;

case SQLT_LNG: /* LONG */

break;

case SQLT_LVC: /* LONG */

break;

case SQLT_STR: /* STRING */

break;

case SQLT_DAT: /* DATE */

break;

default:

if (stmQry) (void) OCIHandleFree(stmQry, OCI_HTYPE_STMT);

wxMessageBox(wxT("ERROR Data type not supported"), msgTit, wxOK | wxICON_ERROR);

return false;

}

}

ub4 bufLen = 1182; //SUPPORTS FIELDS UP TO 1100 CHARS

//char* bufFld[numcols-1]; //VC BUG, FIXED

char** bufFld=(char**)malloc((numcols-1) * (sizeof(char) * bufLen));

OCIDefine* define = 0;

if (grdDat!=NULL)

{

grdDat->DeleteCols(0, grdDat->GetNumberCols());

grdDat->DeleteRows(0, grdDat->GetNumberRows());

grdDat->AppendCols(numcols);

}

else

{

aryDat->clear();

}

for (int i=1;i<=(int)numcols;i++)

{

bufFld[i-1] = (char*)malloc(sizeof(char) * bufLen);

if (!ChkErr(errhp, OCIDefineByPos(stmQry, &define, errhp, (ub4)i, bufFld[i-1], bufLen + 1, (ub2)SQLT_STR, 0, 0, 0, OCI_DEFAULT)))

return false;

}

status = OCIStmtExecute(svchp, stmQry, errhp, rows, 0, (OCISnapshot *) 0, (OCISnapshot *) 0, OCI_DEFAULT);

if(status == OCI_NO_DATA)

{

if (stmQry) (void) OCIHandleFree(stmQry, OCI_HTYPE_STMT);

wxMessageBox(wxT("NO se encontraron registros"), msgTit, wxOK | wxICON_INFORMATION);

return false;

}

if (grdDat!=NULL)

{

grdDat->AppendRows(1);

for (int i=0;i<(int)numcols;i++)

{

strTmp.Printf("%s", bufFld[i]);

grdDat->SetValue(0, i, strTmp);

}

}

else

{

std::vector<wxString> aryTmp;

for (int i=0;i<(int)numcols;i++)

{

strTmp.Printf("%s", bufFld[i]);

aryTmp.push_back(strTmp);

}

aryDat->push_back(aryTmp);

}

while(true)

{

status = OCIStmtFetch(stmQry, errhp, rows, OCI_FETCH_NEXT, OCI_DEFAULT);

if(status == OCI_NO_DATA) break;

if (grdDat!=NULL)

{

grdDat->AppendRows(1);

for (int i=0;i<(int)numcols;i++)

{

strTmp.Printf("%s", bufFld[i]);

grdDat->SetValue(grdDat->GetNumberRows()-1, i, strTmp);

}

}

else

{

std::vector<wxString> aryTmp;

for (int i=0;i<(int)numcols;i++)

{

strTmp.Printf("%s", bufFld[i]);

aryTmp.push_back(strTmp);

}

aryDat->push_back(aryTmp);

}

}

for (int i=1;i<=(int)numcols;i++)

memset((void *)bufFld[i-1], '\0', (size_t)bufLen);

if (stmQry) (void) OCIHandleFree(stmQry, OCI_HTYPE_STMT);

return true;

}

//BLOBS FUNTION SUPPORT
bool wxOra::SelLob(wxString strLob, wxString strTbl, wxString strCmp, int numCnd)

{

wxString strQry = wxT("SELECT "+strLob+" FROM "+strTbl+" WHERE "+strCmp+"=:1 FOR UPDATE");

qry = (text*) strQry.c_str();

if (!ChkErr(errhp, OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &stmQry, OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0)))

return false;

if (!ChkErr(errhp, OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &blob, (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0)))

return false;

if (!ChkErr(errhp, OCIStmtPrepare(stmQry, errhp, qry, (ub4) strlen((char *) qry), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)))

return false;

OCIBind *bndhp = 0;

if (!ChkErr(errhp, OCIBindByPos(stmQry, &bndhp, errhp, (ub4) 1, (dvoid *) &numCnd, (sb4) sizeof(numCnd), SQLT_INT, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)))

return false;

if (OCIDefineByPos(stmQry, &deflob, errhp, (ub4) 1, (dvoid *) &blob, sizeof(OCILobLocator*), (ub2) SQLT_BLOB, (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT))

return false;

status = OCIStmtExecute(svchp, stmQry, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT);

if (!ChkErr(errhp, status))

return false;

return true;

}

//WRITE FILE INTO THE DB

bool wxOra::EscArc(wxString strArc, wxString strLob, wxString strTbl, wxString strCmp, int numCnd)

{

wxFile file(strArc);

if (file.IsOpened())

{

long iSize = (long)file.Length();

//wxChar strBuf[iSize];//VC BUG, FIXED

wxChar* strBuf=(wxChar*) malloc(sizeof(wxChar) * iSize);

if (iSize < 1)

{

wxMessageBox(wxT("ERROR In file size"), msgTit, wxOK | wxICON_ERROR);

return false;

}

else if (iSize > MAXLOBLEN)

{

wxMessageBox(wxT("ERROR The file size is biger than expected"), msgTit, wxOK | wxICON_ERROR);

return false;

}

else

{

memset(strBuf, 0, (size_t)iSize);

size_t iRead = file.Read(strBuf, (size_t)iSize);

if (iRead < (size_t)iSize)

{

wxMessageBox(wxT("ERROR Betwen content and file size"), msgTit, wxOK | wxICON_ERROR);

memset((void *)strBuf, '\0', (size_t)iSize);

return false;

}

else if (!EscLob(strBuf, iRead, strLob, strTbl, strCmp, numCnd))

{

memset((void *)strBuf, '\0', (size_t)iSize);

return false;

}

}

file.Close();

}

else

{

wxMessageBox(wxT("ERROR Can't Open file"), msgTit, wxOK | wxICON_ERROR);

return false;

}

return true;

}

//WRITE BLOB INTO THE DB

bool wxOra::EscLob(wxChar *strArc, size_t size, wxString strLob, wxString strTbl, wxString strCmp, int numCnd)

{

wxString strQry;

strQry.Printf("UPDATE %s SET %s=EMPTY_BLOB() WHERE %s=%d", strTbl.c_str(), strLob.c_str(), strCmp.c_str(), numCnd);

if (!ExecQry(strQry))

return false;

if (!SelLob(strLob, strTbl, strCmp, numCnd))

return false;

ub4 arcLen = (ub4)size;

if (!ChkErr(errhp, OCILobWrite(svchp, errhp, blob, &arcLen, (ub4) 1, (dvoid *)strArc, arcLen, OCI_ONE_PIECE, (dvoid *)0, (OCICallbackLobWrite) 0, (ub2) 0, (ub1) SQLCS_IMPLICIT)))

{

ClsLobHnd();

wxMessageBox(wxT("ERROR Can't load the file"), msgTit, wxOK | wxICON_ERROR);

return false;

}

status = OCITransCommit(svchp, errhp, 0);

if (!ChkErr(errhp, status))

{

ClsLobHnd();

return false;

}

ClsLobHnd();

return true;

}

//READ FILE FROM DB

bool wxOra::LeeArc(wxString strArc, wxString strLob, wxString strTbl, wxString strCmp, int numCnd)

{

int numLen;

wxChar strBuf[MAXLOBLEN];

numLen = LeeLob(strBuf, strLob, strTbl, strCmp, numCnd);

if (numLen > 0)

{

wxFile file;

if (!file.Open(strArc, wxFile::write))

{

wxMessageBox(wxT("ERROR Can't Open file"), msgTit, wxOK | wxICON_ERROR);

memset((void *)strBuf, '\0', (size_t) numLen);

return false;

}

file.Write(strBuf, (size_t) numLen);

file.Close();

}

else

{

wxMessageBox(wxT("ERROR Can't read file"), msgTit, wxOK | wxICON_ERROR);

memset((void *)strBuf, '\0', (size_t) numLen);

return false;

}

return true;

}

//READ BLOB FROM DB

int wxOra::LeeLob(wxChar *strArc, wxString strLob, wxString strTbl, wxString strCmp, int numCnd)

{

if (!SelLob(strLob, strTbl, strCmp, numCnd))

return 0;

ub4 lobSze = 0;

if (!ChkErr(errhp, OCILobGetLength(svchp, errhp, blob, &lobSze)))

{

ClsLobHnd();

wxMessageBox(wxT("ERROR In file size"), msgTit, wxOK | wxICON_ERROR);

return 0;

}

if (!ChkErr(errhp, OCILobRead(svchp, errhp, blob, &lobSze, 1, (dvoid *) strArc,

(ub4) MAXLOBLEN, (dvoid *)0,

(OCICallbackLobRead) 0,

(ub2) 0, (ub1) SQLCS_IMPLICIT)))

{

ClsLobHnd();

wxMessageBox(wxT("ERROR Can't Read file"), msgTit, wxOK | wxICON_ERROR);

return 0;

}

ClsLobHnd();

return (int) lobSze;

}

void wxOra::ClsLobHnd()

{

if (stmQry) (void) OCIHandleFree(stmQry, OCI_HTYPE_STMT);

if (blob) (void) OCIHandleFree(blob, OCI_DTYPE_LOB);

}

//OPTIMIZED ERROR MANAGER FOR wxOraLib

bool wxOra::ChkErr(OCIError *errhp, sword status)

{

text errbuf[512];

sb4 errcode = 0;

wxString strErr;

bool bolRes = true;

switch (status)

{

case OCI_SUCCESS:

bolRes = true;

break;

case OCI_SUCCESS_WITH_INFO:

wxMessageBox(wxT("OCI_SUCCESS_WITH_INFO"), msgTit, wxOK | wxICON_INFORMATION);

bolRes = false;

break;

case OCI_NEED_DATA:

wxMessageBox(wxT("OCI_NEED_DATA"), msgTit, wxOK | wxICON_INFORMATION);

bolRes = false;

break;

case OCI_CONTINUE:

wxMessageBox(wxT("OCI_CONTINUE"), msgTit, wxOK | wxICON_INFORMATION);

bolRes = false;

break;

case OCI_NO_DATA:

wxMessageBox(wxT("ERROR OCI_NODATA"), msgTit, wxOK | wxICON_ERROR);

bolRes = false;

break;

case OCI_ERROR:

(void) OCIErrorGet((dvoid *)errhp, (ub4) 1, (text *) NULL, &errcode,

errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);

strErr.Printf("ERROR %.*s", 512, errbuf);

wxMessageBox(strErr, msgTit, wxOK | wxICON_ERROR);

bolRes = false;

break;

case OCI_INVALID_HANDLE:

wxMessageBox(wxT("ERROR OCI_INVALID_HANDLE"), msgTit, wxOK | wxICON_ERROR);

bolRes = false;

break;

case OCI_STILL_EXECUTING:

wxMessageBox(wxT("ERROR OCI_STILL_EXECUTE"), msgTit, wxOK | wxICON_ERROR);

bolRes = false;

break;

default:

wxMessageBox(wxT("UNEXPECTED ERROR"), msgTit, wxOK | wxICON_ERROR);

bolRes = false;

break;

}

return bolRes;

}