Kirix Support Forums

test for having records

Please post any help questions, requests or other feedback here.

test for having records

Postby abenitez77 on Wed Mar 21, 2012 6:28 pm

I have a script that gives me an error msg when i run it See part of my script where it errors below. It is inside a for loop and on the first try it works, but second time around i get this error msg.

error msg:
Script Runtime Error: Term 'RecCount.next' does not evaluate to a function.

script:
if (MyLen > 0)
{
var RecCount = db3.execute("Select Count(*) as Cnt From [" + selected_items[x] + "] " + strWhere);
}
else
{
var RecCount = db3.execute("Select Count(*) as Cnt From [" + selected_items[x] + "]");
}

RecCount.next();

var rows = RecCount.Cnt;
drows = rows * .1;
irows = drows;

/////////////////////////////////////////////////
This is the entire function:

function onButton3Clicked()
{
//Creates the Tables on the SQL server from the TSQL command in the function
var db3
var db4
var x
var i
var selected_items
var selected_items2
var SQLResults
var SQLTbl
var inserter
var results
var CreateFlds
var FldsList
var info
var fname
var drows
var irows
var RecCount
var MyLen
var WhereFld
var ParamVal
var kirixtblname
var UN
var pname

UN = Environment.getEnvironmentVariable("USERNAME");
pname = "/USERS/" + Environment.getEnvironmentVariable("USERNAME") +"/"+textbox3.getText();

// when the start button is pressed, advance the
// progress bar to its maximum value
var max = m_progressbar.getMaximum();

// Get the Selected Items(DB Names) into an Array
selected_items2 = listbox2.getSelectedItems();
// Get the Selected Items(Table Names) into an Array
selected_items = listbox.getSelectedItems();
var s = 0;
var k = 0;
var strWhere;
Console.clear();
m_progressbar.setValue(0);
HostApp.refresh();



for (i in selected_items2)
{
for (x in selected_items)
{
if (selected_items[x].indexOf(":") < 1)
{
db3 = new DbConnection("mssql://" + textbox.getText() + ":1434/" + selected_items2[i]);

WhereFld = choicebox.getValue();
ParamVal = sqlcriteria.getText();
//###RecCount = " ";
MyLen = " ";
var MyLen = sqlcriteria.getText().length;

// use for filtering TSQL code sent to SQL.
switch (choiceboxTSQL.getValue())
{
case "=":
strWhere = " Where " + WhereFld + " = " + combine_ids([ParamVal]);
break;
case "IN":
strWhere = " Where " + WhereFld + " IN(" + combine_ids([ParamVal]) + ")";
break;
case "LIKE":
strWhere = " Where " + WhereFld + " LIKE '%" + ParamVal + "%'";
break;
}
//alert(strWhere);

// Check length of SQL statemnt in textbox2
//###RecCount = " ";
if (MyLen > 0)
{
//RecCount = db3.execute("Select Count(*) as Cnt From [" + selected_items[x] + "] Where " + WhereFld + " = '" + ParamVal + "'");
RecCount = db3.execute("Select Count(*) as Cnt From [" + selected_items[x] + "] " + strWhere);
alert("Select Count(*) as Cnt From [" + selected_items[x] + "] " + strWhere);
}
else
{
RecCount = db3.execute("Select Count(*) as Cnt From [" + selected_items[x] + "]");
alert("Select Count(*) as Cnt From [" + selected_items[x] + "]");
}

alert("RecCount: " + RecCount.Cnt);

if (RecCount.hasNext())
{

}
else
{
break;
}


var rows = RecCount.Cnt;
drows = rows * .1;
irows = drows;
print("Database: " + selected_items2[i] + " - Table: " + selected_items[x] + " RecCount - " + rows);
print("Select Count(*) as Cnt From [" + selected_items[x] + "]" + strWhere);
// Copy out to SQL from SQL.
if (checkbox.getValue())
{
if (s == 0 )
{
if (MyLen > 0)
{
results = db3.execute("Select * Into dbo.[" + textbox3.getText() + "] From [" + selected_items[x] + "]" + strWhere);
}
else
{
results = db3.execute("Select * Into dbo.[" + textbox3.getText() + "] From [" + selected_items[x] + "]");
}
s = s + 1;
}
else
{
if (MyLen > 0)
{
results = db3.execute("Select * Into dbo.[" + textbox3.getText()+ s.toString() + "] From [" + selected_items[x] + "]" + strWhere);
}
else
{
results = db3.execute("Select * Into dbo.[" + textbox3.getText()+ s.toString() + "] From [" + selected_items[x] + "]");
}
s = s + 1;
}
HostApp.refresh();
}
// Copy out to Kirix from SQL.
if (checkbox2.getValue())
{
if (MyLen > 0)
{
SQLTbl = db3.execute("Select * From [" + selected_items[x] + "]" + strWhere);
}
else
{
SQLTbl = db3.execute("Select * From [" + selected_items[x] + "]");
}

//Get the field types from the table.
info = db3.describeTable(selected_items[x]);
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).
SQLResults = db4.execute("Create Table " + filename + selected_items[x] + " (" + CreateFlds + ");");

// Create the inserter. Create and Prep the Bulk Insert table
inserter = db4.bulkInsert(filename + selected_items[x], FldsList);
k = k + 1;
}
else
{
// Create the local table in Kirix that will be populated from the SQL Table.

kirixtblname = filename + selected_items[x]; // + 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.");
}
}
}
}
alert("Extraction Completed");
// refresh the project
HostApp.refresh();
}

//function to connect to server and retrieve DB names
function onButton4Clicked()
{
var db3
var x
var i
var selected_items
var selected_items2

// Get the Selected Items(DB Names) into an Array
selected_items2 = listbox2.getSelectedItems();
// Get the Selected Items(Table Names) into an Array
selected_items = listbox.getSelectedItems();
for (i in selected_items2)
{
for (x in selected_items)
{
//alert("colon - " + selected_items[x].indexOf(":"));
if (selected_items[x].indexOf(":") < 1)
{
//Connection
db3 = new DbConnection("mssql://" + textbox.getText() + ":1434/" + selected_items2[i]);

}
}
// Populate the listbox with all the DB names on the server
}
HostApp.refresh();
}
abenitez77
Registered User
 
Posts: 143
Joined: Fri Jan 21, 2011 12:42 pm

Re: test for having records

Postby abenitez77 on Wed Mar 21, 2012 6:37 pm

I even tried :
if (RecCount.hasNext())
{

}
else
{
break;
}


in place of
RecCount.next()
abenitez77
Registered User
 
Posts: 143
Joined: Fri Jan 21, 2011 12:42 pm

Return to Strata Help & Feedback