DROP TABLE problem

[SLSJax]

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?

[Aaron]

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

[SLSJax]

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.

[Aaron]

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

[SLSJax]

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.

[Aaron]

Excellent.