sqldb
Syntax: %sqldb(name, database, protocol, server, port, username, password)
Related: #SQLDB, %sql
Create and return a new named SQL Database connection, but do not automatically open it.
NOTE: CMUDPro is required to use any database protocol other than SQLite.
The database connection has several properties and methods that you can call to further manipulate it:
Properties
- Filename : Specifies the name of a local database file to connect to.
- Database : sets the name of the database to connect to when using a remote database protocol.
- Server : Specifies the IP Hostname or IP Address of the remote server
- Port : Specifies the port to be used for the database connection if different than the default port for the specific database driver type.
- Username : specifies the username used to connect to the database. You can prompt for the username and password using the LoginPrompt property.
- Password : specifies the password used to connect to the database. You can prompt for the username and password using the LoginPrompt property.
- LoginPrompt : Set this property to true (1) to prompt for the username and password to be used to connect to the database. This property is False (0) by default.
- ConnectionString : specifies the full Windows Connection String for an ADO database, or a database name for a non-ADO database.
- ReadOnly : Set this to true (1) if the connection should be opened in read-only mode.
- Connected : Returns true (1) if the database is open and connected. Can set this property to true (1) to open the database connection, or set it to false (0) to close the database connection.
- InTransaction : returns true (1) if a transaction is currently in process.
Methods
- Open : opens the database connection.
- Close : closes a database connection
- StartTransaction : Begins a transaction against a database that supports transactions.
- Commit : ends a transaction started with the StartTransaction method and commits all changes to the database.
- Rollback : ends a transaction started with StartTransaction by discarding the changes and rolling the database back to its state when StartTransaction was called.
- Execute(sql) : execute the given SQL statement. Should be used for SQL statements that do not return a result. Use the %sql function for queries that return results.
Example
db = %sqldb("MyDB", "dbname", "mysql", "server.com", 3305)
db.LoginPrompt = 1
#CALL @db.Open
row = %sql(MyDB, "SELECT * FROM table")
Creates a remote database connection but sets the LoginPrompt properties before opening the database so that you are prompted to enter the username and password of the connection. |
%abs