Connect to a DB

Verified
Added by iNTERFACEWARE

Code to connect to commonly used databases (connecting to other databases is very similar).

Source Code
   -- connect to a SQLite database
   -- no user/password needed (if they are supplied they will just be *ignored*)
   -- will create the DB file if it does not exist
   -- NOTE: SQLite has no concept of user accounts, and relies on the file system for security
   --       It is possible to recompile MySQL to include user account security, see
   --       http://www.sqlite.org/src/doc/trunk/ext/userauth/user-auth.txt
   conn = db.connect{
      api = db.SQLITE,
      name = 'test',
      live = true
   }
      
   -- connect to a MySQL database
   -- requires user and password (both can be empty string if anonymous logon is enabled)
   -- NOTE: MySQL has the option for an "anonymous" login (analogous to anonymous FTP), 
   --       which uses blank/empty user and password as logon credentials. Be aware that this
   --       option is not always available as it may be disabled by the system administrator.
   conn = db.connect{
      api = db.MY_SQL,
      name = 'test',
      user = 'MyUser',
      password = 'SecretPassword',
      live = true
   }
   
   -- connect to a Microsoft SQL Server database
   -- requires user and password (when both are empty Windows integrated security is used)
   -- NOTE: You can use the "Guest" user account (disabled by default) to grant "anonymous"
   --       access in SQL Server. Alternatively for integrated security you could grant 
   --       access to the "Everyone" group (all users including Guest), or the 
   --       "Authenticated Users" group (all users excluding Guest). Speak to your system
   --       administrator for more information.
   conn = db.connect{
      api = db.SQL_SERVER,
      name = 'Test',
      user = 'MyUser',
      password = 'SecretPassword',
      live = true
   }
   
   -- connect to an Oracle database with OCI
   -- requires user and password (password can be blank)
   -- NOTE: Oracle has a ANONYMOUS user (disabled by default) that can be used for HTTP
   --       access. Alternatively you could create user like "Guest" (or other name)
   --       with a blank password. You can also use external authentication to enable 
   --       Windows integrated security - which means you could use the "Everyone" or 
   --       "Authenticated Users' group (exactly like you can for SQL Server above).
   --       Speak to your system administrator for more information
   conn = db.connect{
      api = db.ORACLE_OCI,
      name = 'Test',
      user = 'MyUser',
      password = 'SecretPassword',
      live = true
   }

   -- connect to an Oracle database with ODBC
   -- requires user and password (password can be blank)
   -- NOTE: Oracle has a ANONYMOUS user (disabled by default) that can be used for HTTP
   --       access. Alternatively you could create user like "Guest" (or other name)
   --       with a blank password. You can also use external authentication to enable 
   --       Windows integrated security - which means you could use the "Everyone" or 
   --       "Authenticated Users' group (exactly like you can for SQL Server above).
   --       Speak to your system administrator for more information
   conn = db.connect{
      api = db.ORACLE_ODBC,
      name = 'Test',
      user = 'MyUser',
      password = 'SecretPassword',
      live = true
   }
Description
Code to connect to commonly used databases (connecting to other databases is very similar).
Usage Details

Code to connect to commonly used databases (connecting to other databases is very similar). Change the api parameter to connect to a different database, and create an ODBC datasource  to pass in as the name parameter if required.

This page is intended to describe the database connection parameters that can be used. We assume that you understand user/password requirements for your own database (if you are unsure please ask your system administrator). That being said there has been some confusion about using blank usernames and passwords, as this unfortunately means different things for different databases, e.g., in Microsoft SQL it is used for Windows integrated security, and for MySQL it used for anonymous logon. As a response to this we have included some notes about in the script about how blank user/password and “anonymous” access works with each type of DB – you can ignore these notes if you wish. If you have further questions please contact us at support@interfaceware.com.

How to use the snippet:

  • Paste the desired connection into your script
  • To connect to a different database just change the api parameter
    Note: Type “db.” in the editor and use auto-complete to choose the DB code
  • If you are using ODBC  pass the ODBC datasource name to the name param