justin.k.montgomery

NAS: External database manipulation

Discussion created by justin.k.montgomery on Jul 14, 2009
Latest reply on Dec 20, 2012 by Jarrod



 

 

To Open a database connection to a local SQL Server:

 

database.open ("Provider=SQLOLEDB;Server=127.0.0.1;Database=dbname;Trusted_Connection=yes;")

 

 

 

The connection string will vary depending on the server type, location, and security settings but I have found all my connection strings at the following URL: http://www.connectionstrings.com/

 

 

 

To run a query against the open database:

 

TS = database.query("SELECT * FROM (Insert Database Here)") 

 

 

 

The results of the query are put into the variable (TS) and are in a HASH element structure.  I love this idea since we can simply call the first element in the hash and get the specific information we want from it.

 

 

 

Ex: TS.anything – This would return the column result ‘anything’ of the first element in the HASH.  Using this method requires knowledge of the Database Schema that your are referencing.  Also, you can test to see if any results were returned by checking if #TS is larger than 0. 

 

 

 

To close the database connection:

 

database.close()

 

 

 

This is an absolute must, if you miss this you will have a memory leak!

 

 

 

 

 

Things to note(pitfalls):

 

1.                 You can call the database.open and the database.close only one time per script within the NAS lua scripting environment.  Multiple calls have created a memory leak for me.

 

2.                 If you need to make more than one query to a database its best to call the database.open, run all your query’s and then call the database.close at the end of your script.  This is how I have solved my issues with memory leaks.

 

3.                 Issuing the command from an outside module appears to have the same memory leak affect as calling the open and close multiple times within a script.

 

a.           Ex. Require database_subs

 

 

 

 

 

 

 

 

 

 

 

 

 

All of the above information has been reverse engineered from NAS v3.19 – v3.23

Outcomes