SQL command output to local project

[abenitez77]

Can i run a sql command that runs on the server and then creates the output table in my local project in kirix? The results below are created in the SQL server db. I want to run the sql query in SQL but then create the table locally in kirix.

function onButton3Clicked()
{
//Creates the Tables on the SQL server from the TSQL command in the function
var db3
var x
var i
var selected_items
var selected_items2
// Get the Selected Items(Table Names) into an Array
selected_items = listbox.getSelectedItems();
// Get the Selected Items(DB Names) into an Array
selected_items2 = listbox2.getSelectedItems();
for (i in selected_items2)
{
for (x in selected_items)
{
db3 = new DbConnection("mssql://" + textbox.getText() + ":1434/" + selected_items2[i]);
alert("mssql://" + textbox.getText() + ":1434/" + selected_items2[i]);

// Test for connected.
// if (db3.isConnected()) alert("Connection succeeded");
// else alert("Connection failed");

var results = db3.execute("Select * Into dbo." + textbox3.getText()+ String.toString(x) + " From " + selected_items[x] + " Where Vnd_Nbr = 'CR-007143'");

}
}
// refresh the project
HostApp.refresh();
}

[abenitez77]

Is this possible?

[Aaron]

Sure. First, create a table in the local project that mirrors the output of the query structure in SQL Server. Then, read through the result set on SQL Server and for each record in the result set, insert it into the table on the local project.

The example here shows how to query a database and iterate through a result set:
http://www.kirix.com/docs/DbResult.html

The example here shows how to create a table and insert into it:
http://www.kirix.com/docs/DbBulkInsert.html

[abenitez77]

I'm also adding a checkbox to this form. I have tried to use the line below to check the checkbox in my script but does not work...what is the correct syntax..
checkbox.setValue("true");
and
checkbox.setValue(-1);

[Aaron]

Just use true/false:

Code: Select all

checkbox.setValue(true);
checkbox.setValue(false);


Here's an example showing this in action:

Code: Select all

var f = new SampleForm;

// open the form in a dialog
f.center();
f.show();

Application.run();

class SampleForm extends Form
{
    function SampleForm()
    {

        // call the constructor on the base Form object     
        super("Example", 100, 100, 200, 100);
        setMinSize(100,100);


        var checkbox1 = new CheckBox("CheckBox");
        checkbox1.setValue(false);

        var checkbox2 = new CheckBox("CheckBox");
        checkbox2.setValue(true);

        // create main layout
        var main_layout = new BoxLayout(Layout.Vertical);
        main_layout.addSpacer(15);
        main_layout.add(checkbox1, 0, Layout.Left | Layout.Right, 8);
        main_layout.addSpacer(8);
        main_layout.add(checkbox2, 0, Layout.Left | Layout.Right, 8);       

        // set the layout of this form
        setLayout(main_layout);
    }
}


[abenitez77]

Sure. First, create a table in the local project that mirrors the output of the query structure in SQL Server. Then, read through the result set on SQL Server and for each record in the result set, insert it into the table on the local project.

The example here shows how to query a database and iterate through a result set:
http://www.kirix.com/docs/DbResult.html

The example here shows how to create a table and insert into it:
http://www.kirix.com/docs/DbBulkInsert.html

in the 2nd example..it shows how to create a table by hardcoding the field names...but I don't know ahead of time...is there a function that gives me the field info(name and type) to create the table? Or do i have to get that From sql server and then iterate thru a table with that info?

[Aaron]

Yes. You can find out the structure of a table using DbConnection.describeTable():

http://www.kirix.com/docs/DbConnection.html#DbConnection.describeTable

Here's an example showing how to use it. To see the output, make sure your console is showing (View->Console Panel).

Code: Select all

var db = HostApp.getDatabase();              // use connection to database here
var info = db.describeTable("sample_table"); // use table name to describe here

Console.clear();
for (var i = 0; i < info.length; ++i)
{
    var name = info[i].name;
    var type = info[i].type;
    var width = info[i].width;
    var scale = info[i].scale;
   
    if (type == DbType.Character)
        type = "character";
    if (type == DbType.WideCharacter)
        type = "widecharacter";
    if (type == DbType.Numeric)
        type = "numeric";
    if (type == DbType.Double)
        type = "double";     
    if (type == DbType.Integer)
        type = "integer";
    if (type == DbType.Date)
        type = "date";       
    if (type == DbType.DateTime)
        type = "datetime"; 
    if (type == DbType.Boolean)
        type = "boolean";       

    Console.writeLine(name + "\t" + type + "\t" + width + "\t" + scale);
}


[abenitez77]

Will describetable() function work for tables in SQL server? remember, I am getting a sql table into a kirix table. So I need the structure of the sql table to create the table in kirix table and then import the data into that kirix table.

[Aaron]

Yes, it will work on the SQL Server connection. However, it will turn the mapped column types and widths that are appropriate for importing rather than the native SQL Server column types and widths.

If you want the native SQL Server info, you need to create an appropriate SQL Server SQL statement for these, then iterate through the result set yourself. Then when importing, determine what you want to map to.

[abenitez77]

I am getting "Compiler error (line 330): Missing semicolon ';'

I am highlighting line 330 below.

if (checkbox2.getValue())
{
//Get the SQL database connection
db3 = new DbConnection("mssql://" + textbox.getText() + ":1434/" + selected_items2[i]);
//Get the all the rows from sql table to insert into kirix table.
SQLTbl = db3.execute("Select * From " + selected_items[x]);

//Get the field types from the table.
var info = db.describeTable(selected_items[x]);
var CreateFlds = "";

Console.clear();
for (var e = 0; e < info.length; ++i)
{
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 + ") ";
if (type == DbType.WideCharacter)
type = "widecharacter";
CreateFlds = CreateFlds + name + " Varchar(" + width + ") ";
if (type == DbType.Numeric)
type = "numeric";
CreateFlds = CreateFlds + name + " Numeric(" + width + "," + scale + ") ";
if (type == DbType.Double)
type = "double";
CreateFlds = CreateFlds + name + " double(" + width + "," + scale + ") ";
if (type == DbType.Integer)
type = "integer";
CreateFlds = CreateFlds + name + " Integer(" + width + ") ";
if (type == DbType.Date)
type = "date";
CreateFlds = CreateFlds + name + " Date ";
if (type == DbType.DateTime)
type = "datetime";
CreateFlds = CreateFlds + name + " Datetime ";
if (type == DbType.Boolean)
type = "boolean";
CreateFlds = CreateFlds + name + " Boolean ";

Console.writeLine(name + "\t" + type + "\t" + width + "\t" + scale);
}
//Current Database in Kirix Project.
db4 = HostApp.getDatabase();
//var resultstemplate = db3.execute("Select * Into dbo.template" + textbox3.getText()+ String.toString(x) + " From " + selected_items[x] + " Where Vnd_Nbr = 'CR-007143'");
SQLResults = db4.execute("Drop Table If Exists " + selected_items[x] );
SQLResults = db4.execute("Create Table " + selected_items[x] + " (" + CreateFlds + ");");
// Create the inserter
inserter = db4.bulkInsert(selected_items[x], CreateFlds);

//Loop thru the rows in the SQL table.
While (SQLTbl.next())
{
//Loop thru the field names in the sql table.
for (var e = 0; e < info.length; ++i)
{
name = info[e].name;
inserter[name] = SQLTbl.[name];
}
//insert the row
inserter.insertRow();
}
}

[Aaron]

Your "While" is capitalized but should be lowercase. ECMAScript is case-sensitive. I think the problem you encountered is that the compiler reported a missing semi-colon, when really something else is wrong. This happened earlier with the ->.

Next time you get a missing semi-colon error when there shouldn't be a semi-colon, take a look at the rest of the line and see if you are using valid keywords and syntax. A good way to find the problem is to take the problem line and try to create the smallest test script using that line that will still fail; then compare that to known examples that work.

[abenitez77]

ahh yes.... gotta get used to that. Now i'm getting an error msg at the same line...not sure what to make of this...

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

[Ben]

If you get that error message, it means that your variable SQLTbl is null. That means that the query that was run above in your script failed for some reason, perhaps a permission problem.

Best,
Ben

[abenitez77]

ok...i'm getting closer.... Now I get the error msg below:
error msg:
Script Runtime Error: Term 'inserter.insertRow' does not evaluate to a function.

Code:
inserter = db4.bulkInsert(selected_items[x], CreateFlds);
//Loop thru the rows in the SQL table.
while (SQLTbl.next())
{
//Loop thru the field names in the sql table.
for (var e = 0; e < info.length; ++e)
{
name = info[e].name;
inserter[name] = SQLTbl.[name];
}
//insert the row
inserter.insertRow();
}
//finalize the innsert
inserter.finishInsert();

[abenitez77]

is my syntax wrong?

[Aaron]

Check the input parameter values and make sure they are valid. Also, you might want to create a small test script where you are able to successfully insert rows, then adapt that to your script.

[abenitez77]

ok, i think i know what the problem is...what I need to know is how to dynamically reference the field name (in bold below) from a recordset (SQLTbl). I am getting the name of the field from (fname = info[e].name;). I am trying to feed that to SQLTbl so i can use that dynamically.

Code Snipet:
SQLTbl = db3.execute("Select * From [" + selected_items[x] + "]");
info = db3.describeTable(selected_items[x]);

while (SQLTbl.next())
{
//Loop thru the field names in the sql table.
for (var e = 0; e < info.length; ++e)
{
fname = info[e].name;
inserter[fname] = SQLTbl.[fname];
}
//insert the row
inserter.insertRow();
}
//finalize the innsert
inserter.finishInsert();
print("Done.");

[abenitez77]

I found the answer to my question but now I need it for the other side(inserter.fname). fname is actually giving me value instead of the field name for inserter.fname. I think it has to be inserter.fieldname.

fname = info[e].name;
inserter.fname = SQLTbl[fname];

[abenitez77]

I found the answer to my question but now I need it for the other side(inserter.fname). fname is actually giving me value instead of the field name for inserter.fname. I think it has to be inserter.fieldname.

fname = info[e].name;
inserter.fname = SQLTbl[fname];

remember..i don't know the name of the field ahead of time...this is why i am trying to do this dynamically.

in the example below that i found on the website, it uses the field name in quotes :

// fill out the inserter elements corresponding to
// the table fields
inserter["NAME"] = fields[item].name;
inserter["TYPE"] = nametype;
inserter["WIDTH"] = fields[item].width;
inserter["SCALE"] = fields[item].scale;
inserter["EXPRESSION"] = fields[item].expression;

[abenitez77]

Woohoo...success. I'm getting the hang of debugging with ECMAScript. For posterity...here is the working 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 name
var info
var fname

// 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)
{
//alert("No semicolon");
db3 = new DbConnection("mssql://" + textbox.getText() + ":1434/" + selected_items2[i]);
if (checkbox.getValue())
{
//textbox2.clear();
//textbox2.appendText("Select * \n Into dbo." + textbox3.getText()+ String.toString(x) + " \n From " + selected_items[x] + " \n Where Vnd_Nbr = 'CR-007143' \n \n");
results = db3.execute("Select * Into dbo.[" + textbox3.getText()+ String.toString(x) + "] From [" + selected_items[x] + "] Where Vnd_Nbr = 'CR-007143'");
HostApp.refresh();
}

if (checkbox2.getValue())
{
//Get the SQL database connection
//Get the all the rows from sql table to insert into kirix table.
SQLTbl = db3.execute("Select * From [" + selected_items[x] + "]");
//Get the field types from the table.
info = db3.describeTable(selected_items[x]);
Console.clear();
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 + " Datetime, ";
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();
// Create the local table in Kirix that will be populated from the SQL Table.
SQLResults = db4.execute("Drop Table If Exists " + textbox3.getText()+ String.toString(x) + "" );
SQLResults = db4.execute("Create Table " + textbox3.getText()+ String.toString(x) + " (" + CreateFlds + ");");
//alert("Create Table " + textbox3.getText()+ String.toString(x) + " (" + CreateFlds + ");");
// Create the inserter
// Create and Prep the Bulk Insert table
inserter = db4.bulkInsert(textbox3.getText()+ String.toString(x), FldsList);

print("Importing...");
//Loop thru the rows in the SQL table.
while (SQLTbl.next())
{
//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();
}
//finalize the innsert
inserter.finishInsert();
print("Done.");
}
}
}
}
// refresh the project
HostApp.refresh();
}

[abenitez77]

Is this the only way or the fastest way to import a table from sql to kirix via script?

[abenitez77]

I added a progress bar, but don't know how to make it work with my importing the sql table to kirix table function above...can you help me get started?

[Aaron]

Do you want the progress bar to appear in a panel, or use the main application progress bar?

[abenitez77]

I'd like to learn how to do both but in this case it would be in the main application.

[abenitez77]

Progressbar in the main application.

[abenitez77]

Did I loose you?