I get the following error in my code when I try to import a sql table that has fields names with spaces within the name (i.e. "Claim Number").
error msg:
Script Runtime Error: Term 'inserter.insertRow' does not evaluate to a function.
snippet of my code:
SQLTbl = db3.execute("Select * From [" + items[i].getColumnText(2) + "]");
//Get the field types from the table.
info = db3.describeTable(items[i].getColumnText(2));
//info.sort();
CreateFlds = " ";
FldsList = " ";
//Create a variable with all the field names for the dynamic SQL query.
for (var e = 0; e < info.length; ++e)
{
var name = info[e].name;
var type = info[e].type;
var width = info[e].width;
var scale = info[e].scale;
if (type == DbType.Character)
{
type = "character";
CreateFlds = CreateFlds + name + " Varchar(" + width + "), ";
FldsList = FldsList + name + ", ";
}
if (type == DbType.WideCharacter)
{
type = "widecharacter";
CreateFlds = CreateFlds + name + " Varchar(" + width + "), ";
FldsList = FldsList + name + ", ";
}
if (type == DbType.Numeric)
{
type = "numeric";
CreateFlds = CreateFlds + name + " Numeric(" + width + "," + scale + "), ";
FldsList = FldsList + name + ", ";
}
if (type == DbType.Double)
{
type = "double";
CreateFlds = CreateFlds + name + " double(" + width + "," + scale + "), ";
FldsList = FldsList + name + ", ";
}
if (type == DbType.Integer)
{
type = "integer";
CreateFlds = CreateFlds + name + " Integer(" + width + "), ";
FldsList = FldsList + name + ", ";
}
if (type == DbType.Date)
{
type = "date";
CreateFlds = CreateFlds + name + " Date, ";
FldsList = FldsList + name + ", ";
}
if (type == DbType.DateTime)
{
type = "datetime";
CreateFlds = CreateFlds + name + " Date, "; //LM - changed from Datetime to Date so SQL Datetime extracts to Strata Date & we don't see the 00:00:00 in the table.
FldsList = FldsList + name + ", ";
}
if (type == DbType.Boolean)
{
type = "boolean";
CreateFlds = CreateFlds + name + " Boolean, ";
FldsList = FldsList + name + ", ";
}
//Console.writeLine(name + "\t" + type + "\t" + width + "\t" + scale);
}
// Clean up the variable by removing the last comma.
CreateFlds = CreateFlds.substring(1, CreateFlds.lastIndexOf(","));
// Clean up the variable by removing the last comma.
FldsList = FldsList.substring(1, FldsList.lastIndexOf(","));
//Current Database in Kirix Project.
db4 = HostApp.getDatabase();
//check to see if a file name was entered for output, if not remove the "_" from the name in the beggining
var filename
filename = textbox3.getText();
if (filename != '')
{
filename = pname + "_"
}
else
{
filename = pname
}
if (k == 0)
{
// Create the local table in Kirix that will be populated from the SQL Table.
//SQLResults = db4.execute("Drop Table If Exists " + pname);
//SQLResults = db4.execute("Drop Table If Exists " + textbox3.getText());
//Creates an output table name using (the users path + Criteria + table name from where we are extracting).
print("Create Table " + filename + items[i].getColumnText(2) + " (" + CreateFlds + ");");
SQLResults = db4.execute("Create Table " + filename + items[i].getColumnText(2) + " (" + CreateFlds + ");");
// Create the inserter. Create and Prep the Bulk Insert table
inserter = db4.bulkInsert(filename + items[i].getColumnText(2), FldsList);
k = k + 1;
}
else
{
// Create the local table in Kirix that will be populated from the SQL Table.
kirixtblname = filename + items[i].getColumnText(2); // + k.toString();
//SQLResults = db4.execute("Drop Table If Exists " + kirixtblname);
SQLResults = db4.execute("Create Table " + kirixtblname + " (" + CreateFlds + ");");
// Create the inserter. Create and Prep the Bulk Insert table
inserter = db4.bulkInsert(kirixtblname, FldsList);
k = k + 1;
}
print("Importing...");
//HostApp.refresh();
//Loop thru the rows in the SQL table and insert it into our kirix table.
var icount = 0;
var iten = 0;
while (SQLTbl.next())
{
icount = icount + 1;
//Loop thru the field names in the sql table.
for (var e = 0; e < info.length; ++e)
{
fname = info[e].name;
inserter[SQLTbl.getColumnName(e)] = SQLTbl[fname];
}
//insert the row
inserter.insertRow();
// Fill the progressbar
if (icount >= drows)
{
iten = iten + 10;
m_progressbar.setValue(iten);
drows = drows + irows;
}
}
drows = drows * .1;
irows = drows;
m_progressbar.setValue(iten + 10);
//finalize the insert
inserter.finishInsert();
print("Done with: " + pname);
Kirix Support Forums
inserter.insertRow error msg
4 posts
• Page 1 of 1
- abenitez77
- Registered User
- Posts: 143
- Joined: Fri Jan 21, 2011 12:42 pm
Re: inserter.insertRow error msg
I also tried using [ ] for my field names in the code but did not work.
i.e.
CreateFlds = CreateFlds + "[" + name + "] Integer(" + width + "), ";
i.e.
CreateFlds = CreateFlds + "[" + name + "] Integer(" + width + "), ";
- abenitez77
- Registered User
- Posts: 143
- Joined: Fri Jan 21, 2011 12:42 pm
Re: inserter.insertRow error msg
I'd check after the bulkInsert() command if the 'inserter' object you get is null or not. Apparently it is, which indicates that the table you are specifying in the bulkInsert() command isn't correct.
Let me know.
Let me know.
Ben Williams
Kirix Support Team
Kirix Support Team
-
Ben - Kirix Support Team
- Posts: 525
- Joined: Mon Dec 19, 2005 6:29 am
Re: inserter.insertRow error msg
I changed this line:
FldsList = FldsList + name + ", ";
to this:
FldsList = FldsList + "[" + name + "], ";
and then it worked like a charm. I am able to use field names with spaces in the name.
thanks!
FldsList = FldsList + name + ", ";
to this:
FldsList = FldsList + "[" + name + "], ";
and then it worked like a charm. I am able to use field names with spaces in the name.
thanks!
- abenitez77
- Registered User
- Posts: 143
- Joined: Fri Jan 21, 2011 12:42 pm
4 posts
· Page 1 of 1
Return to Strata Help & Feedback