Kirix Support Forums

SQL command output to local project

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

SQL command output to local project

Postby abenitez77 on Mon Feb 07, 2011 8:33 pm

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
Registered User
 
Posts: 141
Joined: Fri Jan 21, 2011 12:42 pm

Re: SQL command output to local project

Postby abenitez77 on Wed Feb 09, 2011 9:55 am

Is this possible?
abenitez77
Registered User
 
Posts: 141
Joined: Fri Jan 21, 2011 12:42 pm

Re: SQL command output to local project

Postby Aaron on Wed Feb 09, 2011 11:27 am

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
Aaron Williams
Kirix Support Team
User avatar
Aaron
Kirix Support Team
 
Posts: 120
Joined: Fri Dec 16, 2005 3:01 pm

Re: SQL command output to local project

Postby abenitez77 on Wed Feb 09, 2011 4:08 pm

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);
abenitez77
Registered User
 
Posts: 141
Joined: Fri Jan 21, 2011 12:42 pm

Re: SQL command output to local project

Postby Aaron on Wed Feb 09, 2011 5:05 pm

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);
    }
}
Aaron Williams
Kirix Support Team
User avatar
Aaron
Kirix Support Team
 
Posts: 120
Joined: Fri Dec 16, 2005 3:01 pm

Re: SQL command output to local project

Postby abenitez77 on Thu Feb 10, 2011 8:22 am

Aaron wrote: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?
abenitez77
Registered User
 
Posts: 141
Joined: Fri Jan 21, 2011 12:42 pm

Re: SQL command output to local project

Postby Aaron on Thu Feb 10, 2011 11:52 am

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);
}
Aaron Williams
Kirix Support Team
User avatar
Aaron
Kirix Support Team
 
Posts: 120
Joined: Fri Dec 16, 2005 3:01 pm

Re: SQL command output to local project

Postby abenitez77 on Thu Feb 10, 2011 12:29 pm

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.
abenitez77
Registered User
 
Posts: 141
Joined: Fri Jan 21, 2011 12:42 pm

Re: SQL command output to local project

Postby Aaron on Thu Feb 10, 2011 1:04 pm

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.
Aaron Williams
Kirix Support Team
User avatar
Aaron
Kirix Support Team
 
Posts: 120
Joined: Fri Dec 16, 2005 3:01 pm

Re: SQL command output to local project

Postby abenitez77 on Thu Feb 10, 2011 9:36 pm

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();
}
}
abenitez77
Registered User
 
Posts: 141
Joined: Fri Jan 21, 2011 12:42 pm

Re: SQL command output to local project

Postby Aaron on Fri Feb 11, 2011 1:34 am

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.
Aaron Williams
Kirix Support Team
User avatar
Aaron
Kirix Support Team
 
Posts: 120
Joined: Fri Dec 16, 2005 3:01 pm

Re: SQL command output to local project

Postby abenitez77 on Fri Feb 11, 2011 7:48 am

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.
abenitez77
Registered User
 
Posts: 141
Joined: Fri Jan 21, 2011 12:42 pm

Re: SQL command output to local project

Postby Ben on Fri Feb 11, 2011 8:48 am

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
Ben Williams
Kirix Support Team
User avatar
Ben
Kirix Support Team
 
Posts: 522
Joined: Mon Dec 19, 2005 6:29 am

Re: SQL command output to local project

Postby abenitez77 on Fri Feb 11, 2011 12:31 pm

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
Registered User
 
Posts: 141
Joined: Fri Jan 21, 2011 12:42 pm

Re: SQL command output to local project

Postby abenitez77 on Mon Feb 14, 2011 7:38 am

is my syntax wrong?
abenitez77
Registered User
 
Posts: 141
Joined: Fri Jan 21, 2011 12:42 pm

Re: SQL command output to local project

Postby Aaron on Mon Feb 14, 2011 11:49 am

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.
Aaron Williams
Kirix Support Team
User avatar
Aaron
Kirix Support Team
 
Posts: 120
Joined: Fri Dec 16, 2005 3:01 pm

Re: SQL command output to local project

Postby abenitez77 on Mon Feb 14, 2011 5:15 pm

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
Registered User
 
Posts: 141
Joined: Fri Jan 21, 2011 12:42 pm

Re: SQL command output to local project

Postby abenitez77 on Mon Feb 14, 2011 5:54 pm

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
Registered User
 
Posts: 141
Joined: Fri Jan 21, 2011 12:42 pm

Re: SQL command output to local project

Postby abenitez77 on Mon Feb 14, 2011 6:10 pm

abenitez77 wrote: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
Registered User
 
Posts: 141
Joined: Fri Jan 21, 2011 12:42 pm

Re: SQL command output to local project

Postby abenitez77 on Mon Feb 14, 2011 10:18 pm

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
Registered User
 
Posts: 141
Joined: Fri Jan 21, 2011 12:42 pm

Re: SQL command output to local project

Postby abenitez77 on Mon Feb 14, 2011 10:32 pm

Is this the only way or the fastest way to import a table from sql to kirix via script?
abenitez77
Registered User
 
Posts: 141
Joined: Fri Jan 21, 2011 12:42 pm

Re: SQL command output to local project

Postby abenitez77 on Thu Feb 17, 2011 3:25 pm

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?
abenitez77
Registered User
 
Posts: 141
Joined: Fri Jan 21, 2011 12:42 pm

Re: SQL command output to local project

Postby Aaron on Thu Feb 17, 2011 3:29 pm

Do you want the progress bar to appear in a panel, or use the main application progress bar?
Aaron Williams
Kirix Support Team
User avatar
Aaron
Kirix Support Team
 
Posts: 120
Joined: Fri Dec 16, 2005 3:01 pm

Re: SQL command output to local project

Postby abenitez77 on Fri Feb 18, 2011 8:38 am

I'd like to learn how to do both but in this case it would be in the main application.
abenitez77
Registered User
 
Posts: 141
Joined: Fri Jan 21, 2011 12:42 pm

Re: SQL command output to local project

Postby abenitez77 on Mon Feb 21, 2011 9:16 am

Progressbar in the main application.
abenitez77
Registered User
 
Posts: 141
Joined: Fri Jan 21, 2011 12:42 pm

Re: SQL command output to local project

Postby abenitez77 on Tue Feb 22, 2011 1:06 pm

Did I loose you?
abenitez77
Registered User
 
Posts: 141
Joined: Fri Jan 21, 2011 12:42 pm

Return to Strata Help & Feedback