The SQL Editor Dialog

Introduction

Note: This is the first post in a series entitled “Database Connectivity Basics”.

The SQL Editor dialog sits at the heart of Origin’s database connectivity features. In this installment in the Database Connectivity Basics series I’ll introduce you to the dialog itself so that, in other posts in this series, I won’t have to repeat myself.

But before getting into the dialog, I’ll go over some related terminology.

Terminology

Here are some terms and descriptions you will encounter when using Origin’s database connectivity features, especially making connections.

ODBC

ODBC stands for Open Database Connectivity and is the de facto standard for allowing applications to connect to database engines. ODBC “drivers” are distributed by database vendors (or third parties) and typically come in 32- or 64-bit flavors though increasingly 32-bit is no longer being supported. If you are not using Microsoft database products, you will almost certainly use ODBC.

ODBC Data Source

An ODBC Data Source is a set of configuration settings grouped under a “Data Source Name” that abstract away the details of the ODBC connection to make it easier to use in an application. An ODBC Data Source is created via the ODBC Data Sources Windows control panel.

OLE DB

OLE DB is a Microsoft technology for connecting software to databases. As alternatives to ODBC Data Sources, OLE DB “Providers” are typically limited to SQL Server, Access, Office files, etc. That being said, Microsoft does provide an OLE DB Provider for ODBC that bridges the two technologies. Currently that is how Origin connects to ODBC data sources.

Data Link Properties dialog

A dialog used by Origin to select which OLE DB Provider to use for a database connection along with the various settings for the connection.

Server

A database Server is a running instance of a database product such as MySQL or SQL Server. When it comes to ODBC or OLE DB, a Server typically is the name of the machine running the server, or an IP address, URL, etc.

Catalog

Catalog is just another way to say “database”.

Credentials/Login Info

This is typically a user id/password pair. Depending on how a database server is set up, full credentials may be required, or just a user id, or perhaps no credentials are required. Also, other schemes may exist such as using a Windows account. There is no hard and fast rule regarding credentials.

Connection String

A Connection String consists of a list of key=value pairs representing the necessary components to connect to a database without having to manually specify them in a dialog. Connection Strings can be saved and reused for future connections. Here is an example (not real):   Provider=MSDASQL.1;User ID=user123;Password=Pw_123;Persist Security Info=True;Data Source=MySQL;Initial Catalog=sample_employees   Note that they take many forms- they don’t all look like this one!

*.ODS File

An Origin-specific plain text file containing a Connection String. Used to facilitate loading a connection into SQL Editor quickly and easily.

*.ODQ File

An Origin-specific plain text file containing both a Connection String and an SQL statement. Used to facilitate quickly populating the SQL Editor with both connection information and a query.

The SQL Editor

Here is the SQL Editor dialog  user interface (along with description of the various features):

• Table list: List of tables in the selected database. For each table, there is a list of fields. Items may be double-clicked to add them to the SQL query being built in the SQL statement edit box.
• SQL statement edit box: Manually type in SQL here.  Double-click in Table list to add items from the list to the SQL.
• Toggle preview panel visibility: Click to show or hide the preview panel.
• Preview panel: Displays preview of data when the Preview button is clicked.
• Generate preview: Click Preview to generate up to 50 rows of preview data.
• Import data: Click OK to close the dialog and import full data from the query into the active worksheet.

 

File Menu

• New..:  Start a completely new connection with a blank SQL query.
• Open…:  Open an existing *.ODS or *.ODQ file.
• Save to Active Worksheet: Saves connection information and SQL statement to the active worksheet. Good option if you want to save that information but don’t want to import data immediately. Use File: Close to close the dialog afterwards.
• Save Connection As…: Creates an *.ODS file from current connection information. If you  want to include credentials/login info in the connection, do not encrypt it beforehand.
• Show Connection String:  Displays the connection string for current connection in a tab of the preview panel.
• Edit Connection String…:  Modify some part of the current connection string. Or use to copy connection string to clipboard.
• Save Connection and Query As…: Creates an *.ODQ file from current connection information and SQL statement.
• Close:  Closes the dialog.

 

View Menu

• Text Size: Adjust SQL statement editor font size.
• Show Table List: Toggle display of the table list on the left side of the dialog.

 

 

Query Menu

• LabTalk…: Opens a dialog that lets one enable LabTalk variables to be substituted/ embedded in a SQL statement. Also a LabTalk Script Before Query may be specified here. Such scripts typically perform some sort of pre-processing.
• Preview Substituted String: Illustrates what a SQL Statement will actually look like once LabTalk substitutions are made.
• Preview: Same as Preview button- previews the results of the SQL query.

 

Setting Menu

• Import Data by OLE DB:  Use OLE DB for importing data. (Enabled for both OLE DB and ODBC)
• Import Data by ODBC: Use ODBC for importing data. (Only enabled for ODBC connections).
• Auto Resize Worksheet: Resizes worksheet column width to fit data.
• Encrypt Login Info: Encrypts credentials/login info such that it is no longer human-readable when displayed in Origin.

 

Conclusion

In this post, I’ve introduced terminology useful to know when working with Origin’s database connectivity features. I also detailed the user interface for the SQL Editor. In the next post in the series, I will introduce the basics of how to connect to a database in Origin and to import data.