Hi,
I want to do things like insert/update/select on a customized SQLite database. Is there a HSL library for doing this? @EricSindelar_Hamilton do you have any documentation or guides for this?
I have seen that there are things like VectorDB database libraries, but I am more interested in running my own SQL queries on SQLite.
Thanks so much!
@Zach - I am not 100% sure if VENUS has been specifically used directly with SQLite before, but I believe this can be done using a standard file open command (file type āAll Filesā) in method editor, so long as you properly construct a connection string to establish a connection your specific database, as as well as a command string that structures the query. The final section of this slide deck regarding database integration contains more info, but the other sections may be helpful as well.
More info for SQLite connections strings can be found here:
https://www.connectionstrings.com/sqlite/
Typically people will use VENUS to perform SQL queries using an excel table or a csv as a database, where the command string queries are constructed per typical SQL syntax, but direct database integration is supported as well. In addition to the other deck linked, this deck on Using SQLStatements and Relational Databases in VENUS may also be helpful. The content may be old news for you, but could be helpful for others that follow and see this post in the future. In that spirit, here is an example method highlighting different types of SQL queries from files.
Hope this helps.
-Nick
REST APIs 
@NickHealy_Hamilton does this mean I should be able to run the āFile: Openā command with an āINSERTā statement if I want to insert data into the database?
Hi @Zach,
That is correct. Have you reviewed the presentations in the links Nick provided? They go rather in depth on the implementation and examples. Particularly, the slide deck regarding database integration shows the syntax and application, including the use of INSERT, UPDATE, DELETE, SELECT, etc., but essentially the File:Open Command String field will support any SQL statement you could use in a standard query/non-query in something such as MS SQL Server Management Studio. That command string is executed when the āfileā (database connection) is opened, so doing a File:Open followed by a File:Close will result in the command being executed and subsequently closing the database connection. With this approach, it does mean that you either want to aggregate multiple INSERTs into a single statement or you would need to Open and Close the āfileā over and over again to execute a series of INSERTs, which could result in fairly long run times, especially if you have Method View open in Run Control.
