for (key in arguments.Struct) { if ( Len(key) AND isSimpleValue(arguments.Struct[key]) ) { // Trim the field value. -- Don't do it! This causes trouble with encrypted strings //sResult[key] = Trim(sResult[key]); // Replace the special characters that Microsoft uses. sResult[key] = arguments.Struct[key]; sResult[key] = Replace(sResult[key], Chr(8217), Chr(39), "ALL");// apostrophe / single-quote sResult[key] = Replace(sResult[key], Chr(8216), Chr(39), "ALL");// apostrophe / single-quote sResult[key] = Replace(sResult[key], Chr(8220), Chr(34), "ALL");// quotes sResult[key] = Replace(sResult[key], Chr(8221), Chr(34), "ALL");// quotes sResult[key] = Replace(sResult[key], Chr(8211), "-", "ALL");// dashes sResult[key] = Replace(sResult[key], Chr(8212), "-", "ALL");// dashes } } // code taken from Transfer with permission var connection = 0; var db = ""; var type = ""; if ( Len(variables.datasource) ) { connection = getConnection(); db = connection.getMetaData().getDatabaseProductName(); connection.close(); switch(db) { case "Microsoft SQL Server": type = "MSSQL"; break; case "MySQL": type = "MYSQL"; break; case "PostgreSQL": type = "PostGreSQL"; break; case "Oracle": type = "Oracle"; break; case "MS Jet": type = "Access"; break; case "Apache Derby": type = "Derby"; break; default: type = "unknown"; type = db; break; } } else { type = "Sim"; } return type; " ) > ---> 0 THEN 1 ELSE 0 END,0)")> ---> ---> var dbtables = getDatabaseTables(); var MyTables = StructNew(); var varXML = XmlParse(arguments.xmldata,"no"); var arrTables = varXML.XmlRoot.XmlChildren; var arrData = XmlSearch(varXML, "//data"); var i = 0; var j = 0; var k = 0; var mytable = 0; var thisTable = 0; var thisTableName = 0; var thisField = 0; var tmpStruct = 0; var tables = ""; var fields = StructNew(); var fieldlist = ""; //var qTest = 0; var colExists = false; //var arrDbTable = 0; var FailedSQL = ""; var sArgs = 0; // Loop over all root elements in XML for (i=1; i lte ArrayLen(arrTables);i=i+1) { // If element is a table and has a name, add it to the data if ( arrTables[i].XmlName eq "table" AND StructKeyExists(arrTables[i].XmlAttributes,"name") ) { //temp variable to reference this table thisTable = arrTables[i]; //table name thisTableName = thisTable.XmlAttributes["name"]; //Add table to list tables = ListAppend(tables,thisTableName); // Only add to struct if table doesn't exist or if cols should be altered if ( arguments.addcolumns OR NOT ( StructKeyExists(variables.tables,thisTableName) OR ListFindNoCase(dbtables,thisTableName) ) ) { //Add to array of tables to add/alter if ( NOT StructKeyExists(MyTables,thisTableName) ) { MyTables[thisTableName] = ArrayNew(1); } if ( NOT StructKeyExists(fields,thisTableName) ) { fields[thisTableName] = ""; } // Loop through fields in table for (j=1; j lte ArrayLen(thisTable.XmlChildren);j=j+1) { // If this xml tag is a field if ( thisTable.XmlChildren[j].XmlName eq "field" OR thisTable.XmlChildren[j].XmlName eq "column" ) { thisField = thisTable.XmlChildren[j].XmlAttributes; tmpStruct = StructNew(); //If "name" attribute exists, but "ColumnName" att doesn't use name as ColumnName if ( StructKeyExists(thisField,"name") AND NOT StructKeyExists(thisField,"ColumnName") ) { thisField["ColumnName"] = thisField["name"]; } //Set ColumnName tmpStruct["ColumnName"] = thisField["ColumnName"]; //If "cfsqltype" attribute exists, but "CF_DataType" att doesn't use name as CF_DataType if ( StructKeyExists(thisField,"cfsqltype") AND NOT StructKeyExists(thisField,"CF_DataType") ) { thisField["CF_DataType"] = thisField["cfsqltype"]; } //Set CF_DataType if ( StructKeyExists(thisField,"CF_DataType") ) { tmpStruct["CF_DataType"] = thisField["CF_DataType"]; } //Set PrimaryKey (defaults to false) if ( StructKeyExists(thisField,"PrimaryKey") AND isBoolean(thisField["PrimaryKey"]) AND thisField["PrimaryKey"] ) { tmpStruct["PrimaryKey"] = true; } else { tmpStruct["PrimaryKey"] = false; } //Set AllowNulls (defaults to true) if ( StructKeyExists(thisField,"AllowNulls") AND isBoolean(thisField["AllowNulls"]) AND NOT thisField["AllowNulls"] ) { tmpStruct["AllowNulls"] = false; } else { tmpStruct["AllowNulls"] = true; } //Set length (if it exists and isnumeric) if ( StructKeyExists(thisField,"Length") AND isNumeric(thisField["Length"]) AND NOT tmpStruct["CF_DataType"] eq "CF_SQL_LONGVARCHAR" ) { tmpStruct["Length"] = Val(thisField["Length"]); } else { tmpStruct["Length"] = 0; } //Set increment (if exists and true) if ( StructKeyExists(thisField,"Increment") AND isBoolean(thisField["Increment"]) AND thisField["Increment"] ) { tmpStruct["Increment"] = true; } else { tmpStruct["Increment"] = false; } //Set precision (if exists and true) if ( StructKeyExists(thisField,"Precision") AND isNumeric(thisField["Precision"]) ) { tmpStruct["Precision"] = Val(thisField["Precision"]); } else { tmpStruct["Precision"] = ""; } //Set scale (if exists and true) if ( StructKeyExists(thisField,"Scale") AND isNumeric(thisField["Scale"]) ) { tmpStruct["Scale"] = Val(thisField["Scale"]); } else { tmpStruct["Scale"] = ""; } //Set default (if exists) if ( StructKeyExists(thisField,"Default") AND Len(thisField["Default"]) ) { tmpStruct["Default"] = makeDefaultValue(thisField["Default"],tmpStruct["CF_DataType"]); //} else { // tmpStruct["Default"] = ""; } //Set Special (if exists) if ( StructKeyExists(thisField,"Special") ) { tmpStruct["Special"] = Trim(thisField["Special"]); //Sorter or DeletionMark should default to zero/false if ( NOT StructKeyExists(tmpStruct,"Default") ) { if ( tmpStruct["Special"] EQ "Sorter" OR ( tmpStruct["Special"] EQ "DeletionMark" AND tmpStruct["CF_Datatype"] EQ "CF_SQL_BOOLEAN" ) ) { tmpStruct["Default"] = makeDefaultValue(0,tmpStruct["CF_DataType"]); } /* if ( tmpStruct["Special"] EQ "CreationDate" OR tmpStruct["Special"] EQ "LastUpdatedDate" ) { tmpStruct["Default"] = getNowSQL(); } */ } } else { tmpStruct["Special"] = ""; } //Set relation (if exists) if ( ArrayLen(thisTable.XmlChildren[j].XmlChildren) eq 1 AND thisTable.XmlChildren[j].XmlChildren[1].XmlName eq "relation" ) { tmpStruct["Relation"] = expandRelationStruct(thisTable.XmlChildren[j].XmlChildren[1].XmlAttributes); if ( StructKeyExists(thisTable.XmlChildren[j].XmlChildren[1],"filter") ) { tmpStruct["Relation"]["filters"] = ArrayNew(1); for ( k=1; k LTE ArrayLen(thisTable.XmlChildren[j].XmlChildren[1].filter); k=k+1 ) { ArrayAppend(tmpStruct["Relation"]["filters"],thisTable.XmlChildren[j].XmlChildren[1].filter[k].XmlAttributes); } } } //Set alias (if exists) if ( StructKeyHasLen(thisField,"alias") ) { tmpStruct["alias"] = Trim(thisField["alias"]); } //Copy data set in temporary structure to result storage if ( ( NOT ListFindNoCase(fields[thisTableName], tmpStruct["ColumnName"]) ) AND NOT ( StructKeyExists(variables.tableprops,thisTableName) AND StructKeyExists(variables.tableprops[thisTableName],"fieldlist") AND ListFindNoCase(variables.tableprops[thisTableName]["fieldlist"], tmpStruct["ColumnName"]) ) ) { fields[thisTableName] = ListAppend(fields[thisTableName],tmpStruct["ColumnName"]); ArrayAppend(MyTables[thisTableName], StructNew()); MyTables[thisTableName][ArrayLen(MyTables[thisTableName])] = Duplicate(tmpStruct); } }// /If this xml tag is a field }// /Loop through fields in table }// /Only add to struct if table doesn't exist or if cols should be altered }// /If element is a table and has a name, add it to the data }// /Loop over all root elements in XML //Add tables to DataMgr for ( mytable in MyTables ) { addTable(mytable,MyTables[mytable]); } //Create tables if requested to do so. if ( arguments.docreate ) { //Try to create the tables, if that fails we'll load up the failed SQL in a variable so it can be returned in a handy lump. try { CreateTables(tables); } catch (DataMgr exception) { if ( Len(exception.Detail) ) { FailedSQL = ListAppend(FailedSQL,exception.Detail,";"); } else { FailedSQL = ListAppend(FailedSQL,exception.Message,";"); } } }// if //Add columns to tables as needed if requested to do so. if ( arguments.addcolumns ) { //Loop over tables (from XML) for ( mytable in MyTables ) { //Loop over fields (from XML) for ( i=1; i lte ArrayLen(MyTables[mytable]); i=i+1 ) { colExists = false; // get list of fields in table fieldlist = getDBFieldList(mytable); //check for existence of this field if ( ListFindNoCase(fieldlist,MyTables[mytable][i].ColumnName) OR StructKeyExists(MyTables[mytable][i],"Relation") OR NOT StructKeyExists(MyTables[mytable][i],"CF_DataType") ) { colExists = true; } //If no match, add column if ( NOT colExists ) { try { sArgs = StructNew(); sArgs["tablename"] = mytable; StructAppend(sArgs,MyTables[mytable][i],"no"); addColumn(argumentCollection=sArgs); /* sArgs["tablename"] = mytable; if ( StructKeyExists(MyTables[mytable][i],"Default") AND Len(MyTables[mytable][i]["Default"]) ) { addColumn(mytable,MyTables[mytable][i].ColumnName,MyTables[mytable][i].CF_DataType,MyTables[mytable][i].Length,MyTables[mytable][i]["Default"]); } else { addColumn(mytable,MyTables[mytable][i].ColumnName,MyTables[mytable][i].CF_DataType,MyTables[mytable][i].Length); } */ } catch (DataMgr exception) { FailedSQL = ListAppend(FailedSQL,exception.Detail,";"); } } } } } if ( arguments.docreate ) { seedData(varXML,tables); } #Trim(DMPreserveSingleQuotes(arguments.sql))# #Trim(DMPreserveSingleQuotes(arguments.sql))# #Trim(DMPreserveSingleQuotes(temp))##getBooleanSqlValue(aSQL[i].value)##CreateODBCDateTime(aSQL[i].value)# #Trim(DMPreserveSingleQuotes(temp))##getBooleanSqlValue(aSQL[i].value)##CreateODBCDateTime(aSQL[i].value)# #Trim(DMPreserveSingleQuotes(temp))##getBooleanSqlValue(aSQL[i].value)##CreateODBCDateTime(aSQL[i].value)# #Trim(DMPreserveSingleQuotes(temp))##getBooleanSqlValue(aSQL[i].value)##CreateODBCDateTime(aSQL[i].value)# //Set default (if exists) if ( StructKeyExists(arguments,"Default") AND Len(arguments["Default"]) ) { arguments["Default"] = makeDefaultValue(arguments["Default"],arguments["CF_DataType"]); } //Set Special (if exists) if ( StructKeyExists(arguments,"Special") ) { arguments["Special"] = Trim(arguments["Special"]); //Sorter or DeletionMark should default to zero/false if ( NOT StructKeyExists(arguments,"Default") ) { if ( arguments["Special"] EQ "Sorter" OR arguments["Special"] EQ "DeletionMark" ) { arguments["Default"] = makeDefaultValue(0,arguments["CF_DataType"]); } if ( arguments["Special"] EQ "CreationDate" OR arguments["Special"] EQ "LastUpdatedDate" ) { arguments["Default"] = getNowSQL(); } } } else { arguments["Special"] = ""; } ALTER TABLE #escape(arguments.tablename)# ADD #sqlCreateColumn(arguments)# UPDATE #escape(arguments.tablename)# SET #escape(arguments.columnname)# = #arguments.Default# WHERE #escape(arguments.columnname)# IS NULL var sTables = getTableData(); var aColumns = sTables[arguments.tablename]; var i = 0; for ( i=1; i lte ArrayLen(aColumns); i=i+1 ) { if ( StructKeyExists(arguments.data,aColumns[i].ColumnName) ) { if ( StructKeyExists(aColumns[i],"Length") AND aColumns[i].Length AND aColumns[i].CF_DataType neq "CF_SQL_LONGVARCHAR" ) { arguments.data[aColumns[i].ColumnName] = Left(arguments.data[aColumns[i].ColumnName],aColumns[i].Length); } } }