Kirix Support Forums

DROP TABLE problem

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

DROP TABLE problem

Postby SLSJax on Fri Mar 18, 2011 10:55 am

I'm runnign a script & trying to drop a kirix table, but it is failing.

I'm querying in SQL to join two files to a new third file. Right after, I try to drop one of the original files, and it fails. Here's the code...

SQLResults = db4.execute("
SELECT a.fld1, a.fld2, a.fld3, b.fld4, a.fld5
INTO " + textbox.getText()+ "_IN
FROM " + textbox.getText()+ "_INV as a
INNER JOIN ModCD as b ON a.fld3 = b.fld4
ORDER BY fld1, fld2, fld3");
print (SQLResults);
HostApp.refresh();
SQLResults = db4.execute("Drop Table " + textbox.getText() + "_INV");
print (SQLResults);
HostApp.refresh();
Application.exit();

The first execute runs properly. The second returns Null. I tried adding "If Exists", and it still returns null.

I'm wondering if Kirix is leaving the original files open somehow making this fail. Do I need to have a way to close or flush the files?
SLSJax
Registered User
 
Posts: 11
Joined: Fri Mar 18, 2011 10:40 am

Re: DROP TABLE problem

Postby Aaron on Fri Mar 18, 2011 12:49 pm

Hi,

First, to help with debugging, I recommend that you build up your SQL statement as a string, then feed it into the execute statement. That way, you can print out your SQL to the console using Console.writeLine() to more easily review what's happening, and it will also provide you more information than checking the return result. Here's an example:

Code: Select all
var table_name = textbox.getText();

var sql = "";
sql += "SELECT fld1, fld2, fld3 FROM ";
sql += table_name;
sql += " ORDER BY fld1";

// debug:
Console.writeLine(sql);

// execute:
db.execute(sql);



If the console isn't showing, you can show it by selecting View->Console Panel in the main toolbar. Once you've added some debugging code, take a look at the SQL in the console. Is it correct?

Best,
Aaron
Aaron Williams
Kirix Support Team
User avatar
Aaron
Kirix Support Team
 
Posts: 120
Joined: Fri Dec 16, 2005 3:01 pm

Re: DROP TABLE problem

Postby SLSJax on Mon Mar 21, 2011 8:00 am

The first sql execute works fine (I do have the console running) - that's not where the problem is. it creates the file properly.

The problem is in the second part - the drop table. SQLResults = db4.execute("Drop Table " + textbox.getText() + "_INV"); evaluates to null - it isn't letting it drop one of the old tables for whatever reason.
SLSJax
Registered User
 
Posts: 11
Joined: Fri Mar 18, 2011 10:40 am

Re: DROP TABLE problem

Postby Aaron on Mon Mar 21, 2011 11:07 am

Is the table open in the user interface? Is the table related to another table in the relationship manager?
Aaron Williams
Kirix Support Team
User avatar
Aaron
Kirix Support Team
 
Posts: 120
Joined: Fri Dec 16, 2005 3:01 pm

Re: DROP TABLE problem

Postby SLSJax on Mon Mar 21, 2011 11:52 am

Aaron wrote:Is the table open in the user interface? Is the table related to another table in the relationship manager?


Nope, neither. But I did figure it out. The file was still open because I had left out a .finishInsert when I built the file.

Thanks for the attempted assistance. Sorry I hadn't provided enough info to solve it. :)
SLSJax
Registered User
 
Posts: 11
Joined: Fri Mar 18, 2011 10:40 am

Re: DROP TABLE problem

Postby Aaron on Mon Mar 21, 2011 1:32 pm

Excellent.
Aaron Williams
Kirix Support Team
User avatar
Aaron
Kirix Support Team
 
Posts: 120
Joined: Fri Dec 16, 2005 3:01 pm

Return to Strata Help & Feedback