Help run sql scrip from text file

Databases
1

Hi
How i can run this on Xojo to Create a Table

CREATE TABLE scanitems
(
itemcode character(20) NOT NULL,
scanqty integer,
sysqty integer,
CONSTRAINT scankeys PRIMARY KEY (itemcode)
)
WITH (
OIDS=FALSE
);
ALTER TABLE scanitems
OWNER TO postgres;

2

You don’t really need to use an external script. Set up a simple connector to your database and an appropriate sql string to pass to SQLSelect(). Look at the examples where it is done to in-memory databases. Works the same on existing databases as well. You can do this through the action event of a button, or use a textarea for the sql statements and process them using a button event.

3

Thanks Ben for the Idea

4

or you can put all the different SQL statement in a table and use code to loop through this table to do execute the sql statement.

5

The idea to put the SQL strings into a text file is not that bad (especially for complex ones), as Xojo doesn’t support multiline strings without concatenation. Just drag the text file with the SQL string into the project. The file name (without extension) will become the project item name which is also a globally available string.

If a file with the name Test.sql contains SELECT * FROM tableXYZ, you’ll have a global string variable in your project with the name Test. To test you can do:

MsgBox(Test)   // shows "SELECT * FROM tableXYZ"

Note that the file will be accessible for everyone in the resources folder of the compiled application. This might be good or bad, depending on your application.

6

Hi Eli, does it work with multiple sql statement??

7

That depends on the database.

8

unless there is a separator between each statement and when the code read in the text file, separate each SQL statement and execute one by one.

9

Each SQL statement has to be terminated by a ; anyway.

10

that is true…