You can create and edit SQL (.sql) files in PrimalScript. To help with this task, PrimalScript provides a Database Browser that allows you to view database objects. Instead of typing connection information in your editor pane, you can drag it from the Database Browser pane.
To show / hide the Database Browser
•On the ribbon, click the View tab and then check (to show) or uncheck (to hide):
Database Connections
To work with database objects you must create a connection.
To create a connection
1.Right-click in the Database Browser pane and click Create a New Connection.
2.Enter a name for the database connection. "New Connection" is not a valid name.
3.The Data Link Properties dialog will open:
4.Click the Provider tab > select a provider > then click Next >>:
5.On the Connection tab > enter the provider-specific connection settings > then click OK:
The options on the Connection tab will vary depending on the selection you made on the Provider tab.
If you already have an ODBC connection setup you can select ODBC on the Provider tab, then on the Connection tab select the preconfigured ODBC connection from the Specify the source of data option.
To create a new ODBC connection
•Click Use connection string > and then click Build...:
To start the ODBC connection wizard
•After selecting Use connection string (see above), click New...:
Run SQL Queries
There are many ways to run SQL queries in PrimalScript—all include the requirement to select a database connection. After you select the connection, it is used by default for all queries until you change it.
To run a SQL query
1.On the ribbon, click Connect > in the Database section, select a connection from the connections drop-down list:
PrimalScript uses this connection for all queries until you select a different one.
2.Click Run Query.
-OR-
1.On the ribbon, click Connect > in the Database group, select a connection from the drop-down list.
2.In the editing pane, right-click and then select Execute entire file as query.
This option appears in the context menu only after you have selected a connection.
-OR-
1.In the Database Browser, right-click a connection and then click Copy connection string to clipboard.
2.Paste the connection string on the first line of your script. The comment must begin with "Connection:":
A connection string in a script takes precedence over the selection in the Connection box on the Tools tab.
To run the queries in a SQL file
1.Select a connection by using any of the methods described above.
2.Right-click each query and then click Execute select as query.
You can run only one SQL query at a time.
Query Results
Query results are displayed in the SQL Query pane:
ADO Wizard
The ADO Wizard generates VBScript or Windows PowerShell script for your database connection.
To start the ADO Wizard
•In the Database Browser, under the Tables node, right-click a table name and then click Generate VBScript/Powershell code.
Tips for using the ADO Wizard
•After the code is inserted, you can change connection string details in the wizard or in the editing pane.
•The default query uses the table you clicked to start the wizard. You can change the table name, but you cannot refresh the Columns list to reflect the change. To generate columns a different table, close the wizard, click the new table, and open the wizard again.
•You can shift-click to select multiple columns from the Columns list.
Use explicit column names
Although the ADO wizard makes it easier to create SQL connections in your VBScript or Windows Powershell scripts, there's no substitute for solid coding standards. Querying a database properly is very important in any scenario.
The ADO Wizard creates queries with " select * ". Always replace the " * " with an explicit list of the columns that you need. An explicit list insures that the DBAs can index your query properly and your code will be more reliable and perform better. Also, If columns are added to the table, they are not automatically added to your query. This is especially important when extra columns have a large data type like image or varchar(max) that can add a significant delay to your query with no benefit. Avoiding " select * " is considered good database practice in every relational database engine.
For a more feature rich database browser and visual query builder, try PrimalSQL from SAPIEN Technologies.
See also:
