Using an SQL Tool With FileMaker

Document created by fmpdude on Jun 3, 2018Last modified by fmpdude on Jun 8, 2018
Version 8Show Document
  • View in full screen mode

Why use an SQL Tool in FileMaker?

 

Document Word Count: 8812

Approximate reading time: 35 minutes

 

Using an SQL tool can make you much more productive giving you a way to run queries interactively to get exactly the data you want — quickly. Plus, using third party tools, like the one used in this quick introduction, gives you all the “user-goals” at your fingertips. For example, after doing an SQL statement, you can export to any nearly any file type of your choice.  Import data using any delimiter? No problem.

 

The SQL tool used here puts data in neat columns where you can edit directly and even do regular expression searches on those results! As you will see in this quick intro, the SQL tool shown also has powerful programming utilities should you need them.

 

This article is a brief introduction to using an SQL tool with FileMaker. This article won’t attempt to teach you SQL but instead shows a couple quick examples. The goal of this article, then, is to demonstrate how much more productive you can be when you have a powerful SQL tool in your toolkit.

 

However, it’s left to the reader to determine if using a SQL tool like the one described here makes sense for their applications or workflow.

 

This tip will use the extremely useful and inexpensive (with free evaluation) tool called “RazorSQL” (razorsql.com). This tool is updated regularly and has excellent developer support.  For example, RazorSQL recently had an FMP-needed enhancement to handle FMP’s non-standard date formats.

 

A Word (or two) Of Caution

 

RAZORSQL, THE SQL TOOL, AND TOOLS LIKE IT, DESCRIBED IN THIS QUICK INTRO, IS EXTREMELY POWERFUL. IF YOU ARE NOT CAREFUL, YOU CAN DO MUCH DAMAGE TO YOUR DATA WITH A SINGLE SQL STATEMENT. THEREFORE, YOU SHOULD CONSIDER USING TEST DATA AND TEST PROJECTS UNTIL YOU ARE COMFORTABLE. AND IN ANY CASE, ALWAYS, ALWAYS, HAVE CURRENT BACKUPS FOR ALL YOUR DATA.

 

ODBC/JDBC Documentation

 

Before starting, it’s an excellent idea to have the most current documentation for JDBC and ODBC. FMI publishes an ODBC/JDBC guide you should download and keep for reference. This guide is extremely useful working with container data, for example, and for knowing exactly what SQL FMI supports—including usage examples.

 

You can download the SQL documentation, here:

https://fmhelp.filemaker.com/docs/16/en/fm16_odbc_jdbc_guide.pdf

 

Downloading the FileMaker JDBC Driver

 

Since external SQL tools often use JDBC, you need to get the free JDBC driver from the FileMaker website.

 

See this URL: http://www.filemaker.com/support/downloads/

 

FileMaker Permissions

 

For the FileMaker database you’re using, you need to allow JDBC connections. You can allow all or specify users with a particular permission set. For this simple example intro, we will allow “All users”.

 

FileMaker Menu: File…Sharing….Enable ODBC/JDBC…

 

 

For local database access, the setup above is all you need to do to allow JDBC from the FileMaker side. If you have a solution on AWS, there are additional security settings to enable. Read the AWS documentation for more information.

 

Installing the FileMaker JDBC Driver

 

Once downloaded, where you put the JDBC driver is completely up to you. There is no formal installer that forces you through a wizard, installs registry entries, or anything complicated; the JDBC driver is just a file you download.

 

The FMP JDBC driver is called, simply, “fmjdbc.jar”.

 

 

Basic Setup

 

After downloading RazorSQL, you will need to set it up for access to each FileMaker (or other vendor’s) database. There is a version of Razor for every operating system (Mac, Windows, and Linux).

 

After starting RazorSQL and clicking “Click to Connect to Database”, you will see a screen like the one below. Note all the database types (nearly all of them) RazorSQL supports.  It’s worth pointing out that not every JDBC-enabled tool on the market supports FileMaker.

 

When you select FileMaker in the left list and then click “Connect” you will get a setup screen like you see below.

 

In the dialog, as shown above, enter a profile name — a descriptive name for this connection. Next, click the “Browse” button to navigate to the FMP JDBC driver’s location you downloaded earlier.

 

The authentication area is where you define however you have the user name and password  defined in your FileMaker database. Here, for our test database, the login is  “Admin” with no password.  Then, fill in the database name and whether you want to restrict the maximum rows to display.

 

The Host or IP address for the local machine can just be “localhost”.

 

Note: Unlike JDBC drivers from most companies, FMP ’s JDBC driver is crippled to only work on the local machine. There are workarounds for that functionality reduction— not discussed in this document.  However, if you did try to connect, using JDBC, to a FMP database on a different computer as  the computer with the JDBC driver, you would get a surprising and productivity-halting message similar to the one below:

 

 

Click Connect.

 

If you get an error screen like the one below, remember that for local FMP connections, your FMP database must be open and you must have JDBC enabled as discussed above:

 

 

Also, if you have your database on FMS, you will need to use the domain name for that database or its IP address (not “localhost”). There will most likely be other security and server configuration necessary as well (security issues not discussed in this quick intro).

Do I need to know SQL?

 

No! At least not initially, As shown below, Razor SQL has a very powerful query editor for all three types of SQL statements (including a fourth capability, search). Just fill out what you need in an FMP-type Query-By-Example (QBE) format.

 

As shown by the three buttons at the lower left, you can then generate the SQL, Run it, or copy that SQL to the Razor editor.

 

For our example ZipCodes database, we would see this:

 

 

How easy is that?!

 

As you become more experienced, if you don’t know SQL already, learning SQL will be incredibly useful and you wouldn’t use a query builder like the one above often.

 

Here’s one of the many good sites that can help you learn SQL: https://www.w3schools.com/sql/

 

Sample FMP Database - ZipCodes

 

Using a freely available database for zip codes (use your own data if desired), you should see an image similar to the one below after you connect to your database and choose to select all rows (note all the other things you can do right away just by right-clicking the table name):

 

What you should see:

 

 

In production or with large data, you generally wouldn’t display all rows, but this step here is mainly to verify that we can see the data in the open FMP application and to demonstrate the nice columnar display.

 

In the data display above, we can also:

 

    •    Drag the columns to re-order them.

    •    Shrink or grow the columns as needed

    •    Directly edit a Value and then update the FMP table

    •    Change the font size or font family itself

    •    Export or do any other user-goal items directly above the data using the toolbar

    •    Other toolbar options

 

Let's do some queries!

 

It’s important to mention that using the JDBC approach (that is, using the FMP JDBC driver), you can do not only SELECTs (as in FMP), but also INSERTs, UPDATEs, and DELETEs.

 

As a quick example before we do a SELECT example, say you want to update a field in Razor from the data listing we got above? This data listing might also be a query result you got.

 

If you turn on the live edit (note highlighted “ON” at top left below in screenshot), you can then modify results directly in the table shown. After you’re done with a row’s edit, you can click the disk icon and Razor will show you the UPDATE statement it generated automatically for you.

 

 

Clicking Save (see the save button upper left toolbar), Razor displays the UPDATE statement it will execute. You can modify that statement if needed. After you click Execute, you can go back to FileMaker to verify, if you want, that Razor’s UPDATE statement was correct:

 

 

Hurray! FMP got the update!

 

You can of course write your own UPDATE statements, and you often would, but many will appreciate how much Razor helps you do basic editing without needing to write SQL from scratch (or at all).

 

Let’s do a simple SELECT statement:

 

1. Select all the Cities where the zip code is 90210

 

Razor has SQL Assist to help you type in your SQL also. SQL Assist will let you quickly enter table names and other parts of your SQL via pop-ups when you type. You can disable SQL Assist if you don’t want to use it.

 

(just hit ENTER to paste in the typing assisted suggestion)

 

Now that you have the results in the lower part of the display, you can do all kinds of things right away

    ◆    Export

    ◆    Compare query results with another query

    ◆    Filter

    ◆    Sort

    ◆    Create a new table from results

    ◆    Other...

 

Exporting Query Results

 

As you work with people with different databases it’s often convenient to send them a “SQL” file where they can just “run” it to import the your query data.  Using the export button on the toolbar, then, you can create an SQL file to send to someone who also has the ZIPCODES table:

 

    INSERT INTO ZIPCODES(RecordNumber, Zipcode, ZipCodeType, City, State, LocationType, Lat, Long, Xaxis, Yaxis, Zaxis, WorldRegion, Country, LocationText, Location) VALUES ('44656', '90210', 'STANDARD', 'BEVERLY HILLS', 'CA', 'PRIMARY', '34.09', '-118.41', '-0.39', '-0.72', '0.56', 'NA', 'US', 'Beverly Hills, CA', 'NA-US-CA-BEVERLY HILLS');

 

-----

 

If that person doesn’t already have the ZIPCODES table, click the export option, as you see in the dialog below, to additionally create the DDL for you:

 

 

Giving you, …

 

CREATE TABLE ZIPCODES (

    RecordNumber Text(0),

    Zipcode Text(0),

    ZipCodeType Text(0),

    City Text(0),

    State Text(0),

    LocationType Text(0),

    Lat Text(0),

    Long Text(0),

    Xaxis Text(0),

    Yaxis Text(0),

    Zaxis Text(0),

    WorldRegion Text(0),

    Country Text(0),

    LocationText Text(0),

    Location Text(0)

);

INSERT INTO ZIPCODES(RecordNumber, Zipcode, ZipCodeType, City, State, LocationType, Lat, Long, Xaxis, Yaxis, Zaxis, WorldRegion, Country, LocationText, Location) VALUES ('44656', '90210', 'STANDARD', 'BEVERLY HILLS', 'CA', 'PRIMARY', '34.09', '-118.41', '-0.39', '-0.72', '0.56', 'NA', 'US', 'Beverly Hills, CA', 'NA-US-CA-BEVERLY HILLS');

 

 

You can also just copy the row itself by highlighting it and clicking copy.

 

Note: Being able to run an SQL file is a commonly-used feature in most database environments.

 

——

 

Razor will, like most tools, let you format your SQL using a button on the top bar. You can configure how you want the SQL formatted in that dialog.

 

Export the query data itself

 

Here are the data export options for this query:

Then, with the user-goal to export delimited as the default, you get these options when you click Next in the dialog above:

 

CSV file Output:

 

firstname,lastname,dob

Phil,Richardson,2009-01-10

Pete,Smothers,1953-01-01

Phil,Conners,2017-02-02

Phil,Sanders,2005-04-15

 

And, since we selected to “Open File in Editor After Export”, we see the CSV data as well in the top portion of Razor:

 

 

Importing Data

Importing data is just as simple. You can specify any delimiter you need.

 

Note the powerful options you have for data import:

As with CSV for export, importing from CSV is extremely flexible (any delimiter, start row, and other options):

 

SELECT, INSERT, UPDATE, AND DELETE Examples

 

Just so you see how easy it is to manipulate your data, let’s run through all four SQL capabilities you can do using a SQL tool like Razor.

 

Below is our test Customer table.  (We should have included a primary key, but this is just a simple example with four rows.)

 

SELECT

Let’s get all the customers with first name = “Phil”

 

SELECT * from Customers where firstname='Phil'

 

UPDATE  (change existing data)

 

Somebody made a mistake entering a birthday. Let’s change Phil Richardson’s birthday to 2009-01-10

 

We can do this update interactively using the form shown above with no UPDATE statement we need to write at all (Razor will write it for us!). Or, we can write our own UPDATE statement.

 

For this example, let’s just write our own UPDATE statement.

 

    UPDATE Customers

SET

    dob = DATE '2009-01-10'

WHERE

    firstname = 'Phil' AND

    lastname = 'Richardson' AND

    dob = DATE '2009-01-01';

 

Here is what we entered:

 

When we run the UPDATE statement in the top area (clicking the green “—>” button), we get:

 

Notice we can both run the UPDATE using the button shown above and then just rerun the query below to get the updated row!

 

INSERT

Let’s say we need another Phil in the table.

 

Let’s enter Phil Sanders, born 2005-04-15

 

INSERT INTO Customers

     VALUES ('Phil', 'Sanders', DATE '2005-04-15')

 

(Notice we’re using  the “DATE” option per the FMP JDBC reference manual to clarify FMP’s date format to the SQL tool.)

 

Here’s the INSERT statement in Razor:

 

 

And, as above, once we run the query on the top, we can just refresh the list below to see the new data.

 

——

DELETE

Our customer table looks like this now:

 

 

Let’s delete Pete Smith.

 

DELETE

FROM

    Customers

WHERE

    firstname = 'Pete' AND

    lastname = 'Smith'

 

And, in Razor, after executing this line, we get:

 

 

 

You might have noticed that above the query results we got multiple ”Customers” query results. Razor saves your previous queries for this session so you can go back and see that data again and revise if needed.

 

How Do You Get Your Query Into FMP?

 

Once you have your working query in Razor, you just need to go into FileMaker and create a corresponding ExeucteSQL statement.

 

In our example above, we had a query that was “hard-coded” as in:

 

Select city, state from “ZipCodes” where  Zipcode = ‘90210’

 

So, if the ‘90210’, for example, will be a field, you would want to set up your FMP ExecuteSQL using “?” for parameters, like this:

 

 

In the right portion of the ExecuteSQL, where you see the “90210” hard-coded (again here), you might change this parameter to be a search field or some way the user would use this SQL statement in your application.

 

The basic idea, above, however, is that once you created your query and got it working, it’s simple to move it to FMP and finalize it.

 

Other Razor SQL Tool Capabilities

 

A few other interesting Razor Capabilities are below.

 

Example: Find in Query Results

 

In the screenshot below, you can search within query results — even specifying a regular expression:

 

 

You can also search for Data in the entire database or search for database objects in the database.

Example: Powerful Import Options

 

As also shown above, unlike what you may be used to in other environments, RazorSQL gives you flexible import options. One useful feature is that you can specify any delimiter you want for the import file with several commonly-used delimiters already defined for convenience.

From the RazorSQL Website, below are the menu options (and what they do) you will find in RazorSQL:

 

File Menu

New Tab (No Connection): Open a new tab containing a new database browser, SQL programming editor, and query results tabbed pane. The new tab will not be connected to a database until the Connections - Connect option is selected.

New Tab (Share Connection): Open a new tab containing a new database browser, SQL programming editor, and query results pane. The new tab will share the connection of whichever connection was selected from the sub-menu.

Open (No Connection): Open a file from the filesystem, and load that file's contents into the SQL programming editor. The syntax highlighting for the file will be set based on the file extension. The No Connection option means the tab will not be connected until the Connections - Connection option is selected.

Open (Share Connection): Open a file from the filesystem, and load that file's contents into the SQL programming editor. The syntax highlighting for the file will be set based on the file extension. The new tab will share the connection of whichever connection was selected from the sub-menu.

Open URL: Opens the contents of a URL, for example, http://www.razorsql.com/ would return the page source for the RazorSQL site.

Open Choose Encoding: Opens a file with the file encoding selected from the sub-menu. Use this option if you do not want to open the file using the encoding selected in the Preferences, or if no encoding was selected in the preferences, the default operating system file encoding.

Close: Closes the existing tab and closes any resources associated with the tab such as result sets, SQL statements, or database connections.

Close All: Closes all tabs and closes any resources associated with the tabs such as result sets, SQL statements, or database connections.

Close All Except Current: Closes all tabs except the current tab and closes any resources associated with the tabs such as result sets, SQL statements, or database connections.

Save: Saves the contents of the SQL programming editor to the file system. If a file is already associated with the editor, the contents are saved to that file. If there is a no file associated with the editor, a dialog will appear prompting the user to select a file to save the contents to.

Save As: Saves the contents of the SQL programming editor to a file on the file system. A dialog will appear prompting the user to select which file to save the contents to.

Save All: Saves the contents of all of the currently open SQL editor tabs to the file system. If the tabs are associated with a file, the contents will be saved to those files. If not, a dialog will appear prompting the user to select a file to save the contents to.

Save Selection As: Saves the currently highlighted selection in the SQL editor to a file on the file system. A dialog will appear prompting the user to select a file to save the contents to.

Save Choose Encoding: Saves the contents of the currently selected SQL editor to the file system using the character encoding chosen from the sub-menu.

Templates: The templates menu item contains a sub-menu that contains templates for the programming languages supported by RazorSQL such as PL/SQL, SQL, Transact SQL, SQL PL, Java, XML, HTML, CSS, JavaScript, PHP, etc. Users can add their own templates to the Templates menu by selecting the Add Template option from the submenu.

Recent Files: This menu contains a submenu listing all of the recent files that were opened by RazorSQL. Clicking on one of the files listed opens a new tab with that files contents in the SQL programming editor.

Recent Directories: This menu contains a submenu listing all of the recent directories that were the parents of files opened by RazorSQL. Clicking on one of the directories brings up a dialog with the contents of that directory, and allows the user to select one of the files in the directory for opening.

Current Directory: This menu contains the files contained in the parent directory of the file that is associated with the currently active tab in RazorSQL. If there is no file associated with the currently active tab, there are no items displayed.

Favorites: The favorites item allows users to bookmark files or directories so that they can be quickly opened at a later time. To add a file or directory to the favorites, simply open the file, and select either Add Current File to Favorites or Add Current Directory to Favorites.

Tools: The File - Tools menu contains the following file tools:

Size: Displays the size of the selected file in megabytes, kilobytes, and bytes.

Get Info: Displays information about the selected file such as its modification date, type, and size.

Search: Performs a normal or regular expression search on the selected file and displays the results as the contents of a new SQL editor tab.

Head: Performs a head on the selected file by retrieving the first so many lines of the file as specified by the user. The results are displayed as the contents of a new SQL editor tab.

Get Section: Gets the contents of the selected file from the begin and end lines specified by the user. The results are displayed as the contents of a new SQL editor tab.

Tail: Performs a tail on the selected file by retrieving the last so many lines of the file as specified by the user. The results are displayed as the contents of a new SQL editor tab.

Rename: Renames the selected file to the new name specified by the user.

Move: Moves the selected file to a new location as specified by the user.

Delete: Deletes the selected file.

Conversions: The File - Conversions menu contains options for converting the file's line breaks either to Windows line breaks (carriage return line feed), Unix line breaks (line feed) or Mac Classic line breaks (carriage return).

Show in Web Browser: Shows the currently active contents of the SQL programming editor as an HTML page in the default web browser for the user's system.

Backup Editor File: Makes a backup of the file in the currently active tab.

Compare Files: Allows the user to select two files to compare and then displays the differences of the compared files. This is similar to the UNIX diff command.

Reload: Reloads the contents of the currently active SQL editor if there is a file associated with that editor.

Reload All: Reloads the contents of the SQL editors for all tabs if there are files associated with those editors.

Reload Choose Encoding: If the currently active SQL editor has a file associated with it, reloads the editor using the character encoding chosen by the user.

Print: Allows the user to either print the SQL editor contents or the results of the currently active query results tab.

Edit Menu

Undo: Undoes the last operation performed in the editor. Undo can be re-selected hundreds of times to undo operations in the sequence that they were performed.

Redo: Redoes the last operation performed in the editor. Redo can be re-selected hundreds of times to redo operations in the sequence that they were performed.

Cut: Cuts the highlighted text from the SQL programming editor and places it on the system clipboard.

Copy: Copies the highlighted text from either the SQL programming editor or query results and places it on the system clipboard.

Paste: Pastes the contents of the system clipboard to the SQL programming editor.

Select All: Selects all the text in either the SQL programming editor or in the query results.

Sticky Cut: Cuts the highlighted text from the SQL programming editor and stores it indefinitely in a special clipboard provided by RazorSQL.

Sticky Copy: Copies the highlighted text from either the SQL programming editor or query results and places it indefinitely in a special clipboard provided by RazorSQL.

Sticky Paste: Pastes the contents of the RazorSQL sticky clipboard to the SQL editor.

Delete Menu: Contains many operations for deleting text such as the following: Delete Word, Backspace Word, Delete Line, Delete to Start of Line, Delete to End of Line.

Select Menu: Contains many operations for selecting text such as selecting lines, paragraphs, pages, characters, and words.

Text Menu: Contains text operations such as upper casing or lower casing text, appending and prepending text to lines, removing trailing and beginning white space, removing blank lines, removing duplicate lines, sorting lines, word counts, formatting to fit screen, wrapping text at a column number, and unwrapping text.

Shift Selection Left: Shifts the highlighted selection in the SQL editor to the left by one tab.

Shift Selection Right: Shifts the highlighted selection in the SQL editor to the right by one tab.

Previous Word: Moves the cursor in the SQL programming editor to the beginning of the previous word.

Next Word: Moves the cursor in the SQL programming editor to the beginning of the next word.

Previous Paragraph: Moves the cursor in the SQL programming editor to the beginning of the paragraph.

Next Paragraph: Moves the cursor in the SQL programming editor to the beginning of the next paragraph.

Document Begin: Moves the cursor in the SQL editor to the beginning of the document.

Document End: Moves the cursor in the SQL editor to the end of the document.

Date Time: Print the current date and time in the currently active SQL editor.

Preferences: Displays the Preferences window where users can edit preferences.

Search Menu

Database Data Search: The database data search tool is a tool that allows users to search single or multiple tables. The users chooses which columns to use in the search criteria, and what kind of data to look for in those columns. Once the criteria is entered, the search generates database queries behind the scenes and displays the results.

Database Object Search: The database object search tool allows users to find database objects such as tables, views, procedures, triggers, indexes, etc. Type types of objects available for searching are dependent on the type of database that is connected to. The results of the object search are displayed in tree view. Right-clicking on the nodes in the tree provides a menu with options.

Find / Replace: Displays the Find / Replace dialog. This dialog allows users to find text in the currently active SQL programming editor with options such as forward and backward direction, all lines or selected lines only, case sensitivity, whole word only search, wrap search, show found lines, and count all.

Users can also replace found text using the Replace, Replace / Find, and Replace All buttons.

Find Next: Finds the next search text in the SQL editor. If there is text selected, Find Next looks for text matching the selection. If not, Find Next tries to get the last Find done for the current session and looks for that text.

Find Previous: Finds the previous search text in the SQL editor. If there is text selected, Find Previous looks for text matching the selection. If not, Find Previous tries to get the last Find done for the current session and looks for that text.

Find Selection: Searches for text in the SQL editor using the current selection as the search parameter.

Replace Range: Replaces a range of text in the SQL editor, from beginning column number to ending column number, with text specified by the user. Replace Range can work on all lines or only the selected lines.

Goto Line: Places the SQL editor cursor at the beginning of the line number as specified by the user.

Match Bracket: Highlights text in the SQL editor from the first bracket after the cursor to the ending bracket for the beginning bracket. Brackets can include parentheses, curly brackets, and braces.

Match Tag: Highlights text in the SQL editor from the first tag after the cursor to the ending tabg for the beginning tag.

Find / Replace In Files: Searches for text in files on the file system. If the find button is pressed, this text is displayed in a window with clickable icons to take the user to the exact line of text in the file where the text was found. Users can specify the file type to search in, and the directory to start the search. There is a match case option, and the ability to search sub directories.

If the Replace All button is pressed, all found text is replaced with the text in the Replace With box.

Find in Open Editors: Search for text in the RazorSQL SQL editor tabs. Found text is displayed in a window with clickable links to take the user to the exact line of the found text in the appropriate tab.

DB Tools Menu

Create Menu: The Create menu includes options for creating database objects such as tables, views, indexes, sequences, packages, package bodies, procedures, functions, triggers, databases, and users. Not all options are available for all database types.

The Create tools prompt users for information on the database object such as the object name, attributes, etc., and then generate and execute the SQL to create the database objects. All SQL can be previewed before being executed by RazorSQL.

Alter Menu: The Alter menu includes options for altering database objects such as tables and views. The Alter Table option includes options such as adding primary keys, foreign keys, and check constraints, dropping columns and constraints, and renaming tables and columns. Not all Alter options are available for all database types.

The Alter tools generate and then execute the SQL to alter the database object. All SQL can be previewed before being executed by RazorSQL.

Drop Menu: The Drop menu includes options for dropping database objects such as tables, views, indexes, sequences, packages, package bodies, procedures, functions, triggers, databases, and users. Not all options are available for all database types.

The Drop tools generate and then execute the SQL to drop the database object. All SQL can be previewed before being executed by RazorSQL.

Copy / Backup: The copy / backup option can be used to copy the structure and data of a table. This option generates the create table and, if applicable, alter table SQL to generate a new table based on the structure of the existing table. It then generates the SQL insert statements to take data from the old table to the new table.

Edit Menu: The Edit menu includes options for editing tables, packages, package bodies, procedures, functions, and triggers.

The Edit Table option brings up the contents of the table in a spreadsheet like format and allows the user to modify the values in the cells. The Edit Table tool can then generate and/or execute the SQL for any changes made by the user. The Edit Table Tool also has options such as a find tool to find data in the table, and the ability to enter text in a separate window outside of the cell that is useful when editing large blocks of text. The Edit Table Tool also allows users to insert and delete rows.

The other edit options for packages, package bodies, procedures, functions, and triggers, populate the currently active SQL programming editor with the contents of the database object to allow users to edit them.

View Menu: The View menu includes options for viewing table contents, and also viewing the contents of packages, package bodies, procedures, functions, and triggers.

Describe: The describe option is used to describe a table or view and includes such information as column name, column type, column size, primary key status, foreign key status, null-ability, and scale.

Generate DDL: This option allows users to generate the database definition language (DDL) for various database objects such as tables, views, indexes, procedures, etc.

Auto Complete: The auto complete option will automatically complete started text in the SQL editor. The auto complete option displays a list of tables and views that start with the text entered into the editor.

Column Lookup: The column lookup option prompts the user for a table or view and then displays the columns of the table or view in a popup list in the currently active SQL editor. This allows the user to quickly select a column from a table or view without having to type the column name. The SQL editor also has auto column lookup that will automatically display the list of columns for a table.

Table Lookup: The table lookup option prompts the user for a database and/or schema and then displays the tables and views located in that database and/or schema. This allows the user to quickly select a table or view without having to type the name. The SQL editor also has auto table lookup that will automatically display the list of tables for a database or schema.

Execute Procedure / Function: The execute procedure option prompts users for values to each of the input parameters of a procedure or function, and then calls that procedure or function and returns the output.

Export: The Export tool allows users to export data from tables, views, or queries in several formats including Excel, delimited format, HTML, XML, Text, and as SQL insert statements. Multiple or single tables can be exported at a time.

Import: The Import tool allows users to import data into tables from Excel files, delimited files, or fixed width files.

Query Builder: he Query Builder Tool allows users to visually build select, insert, update, or delete SQL statements against tables.
The Query Builder Tool allows the user to select the columns to be used in the SQL statement as well as the SQL operations to be included in the query. It also allows for the selection of aggregate functions and group by clauses and allows for selection of multiple tables for join selects. After selecting the appropriate options and entering any custom values, the Query Builder Tool can generate and/or execute the SQL corresponding to the selections.

Compare Tool: The compare tool allows users to compare either tables and views or query results. The compare process then generates and displays a report of the differences between the objects that were compared.

Query Scheduler: The Query Scheduler tool allows users to schedule SQL queries based on a time interval and number of executions and have the results of the query written to an output file.

Connections Menu

New Tab (No Connection): Creates a new tab that is not connected to a database.

New Tab (Share Connection): Creates a new tab that shares its connection with the connection selected by the user.

Connect: Brings up a list of currently configured connection profiles. The user can then select a profile and connect to a database. If no profiles have been configured, the Connect option brings up the Connection Wizard that allows users to create connection profiles.

Add Connection Profile: Brings up the connection wizard tool. This is used to configure and create a database connection. The information used to connect is then saved in a connection profile that can be accessed for future use via the Connect option.

Disconnect: Disconnects and closes all resources associated to the connection that is associated with the currently active tab.

Disconnect All: Disconnect All closes all currently open connections and closes all resources associated with those connections.

Connect to Built in Database (HSLQDB): Connects to the database that ships with RazorSQL, the HSQLDB database.

View SQL History: Displays the contents of the SQL History. SQL History contains the last 20,000 lines of SQL executed in RazorSQL. The number of lines of history stored can be configured in the Preferences tool.

View Query Log: The query log holds all queries executed on the current RazorSQL tab.

View Meta Data: Displays a window containing meta data information about the database such as version information, driver information, keywords, built-in functions, types, etc.

View DBMS Output: For Oracle database connections, if users set the View DBMS Output option to on via the toolbar dropdown, the View DBMS Output menu option becomes active. RazorSQL then checks for DBMS output for any statement executed. If there is DBMS output available, clicking on the View DBSM output option will display the output.

View Print Output: For SQL Server and Sybase databases, if an SQL statement contains print output, the View Print Output option will display the Print output of the last executed statement.

Configure Navigator: The configure navigator option gives users the ability to configure the database navigator. For some databases, the System Navigator option is available. This option generally generates the most information about database objects and is enabled by default where applicable. The Driver option gets all of the database information from the driver. Since the driver only returns information on tables, views, and procedures, this option does not contain as much information as the System Navigator. The Custom Navigator option enables users to enter their own queries for querying the database and displaying that information in the navigator.

Filter Navigator: If users do not want certain objects to display in the database navigator, they can choose the filter navigator option to filter what gets displayed.

Reload Navigator: Reloads and refreshes the database navigator with any changes that may have occurred since the last refresh.

SQL Menu

Execute SQL (One Statement): The execute option determines which statement from the query editor to execute based on certain conditions. If an SQL statement is highlighted in the query editor, that statement will be executed. If there are no statements highlighted, and the language type is set to SQL, PL/SQL, TSQL, or SQL PL RazorSQL will then check for statement separators (by default the semi-colon). If there are statements separated by statement separators, RazorSQL will execute the closest statement based on the cursor position. If there are no statement separators, or if the language type is something other than one of the SQL variants such as HTML, RazorSQL will attempt to execute the entire contents of the query editor. For PL/SQL and SQL PL, RazorSQL will not parse queries based on statement separators if the statement separator is the semi-colon if it determines there are blocks of code, such as begin, end blocks in the editor.

The execute process executes the SQL statement. If the statement performs an insert, update, or delete, RazorSQL will attempt to display the number of rows affected in the SQL status panel.

If the query is a select, RazorSQL will display the results of the query in the query results section. RazorSQL determines how many rows to fetch of the query by looking at the fetch size preferences first, and if nothing is specified, by using the specific database driver settings. Most drivers by default have a fetch size of around 100 rows. If the user attempts to scroll to see more than the number of rows fetched, RazorSQL will then try to fetch the next set of rows from the database. The SQL status panel will be updated each time RazorSQL calls back to the database driver to fetch more rows.

Certain database tools only perform actions on the fetched rows. For example, the sort and filter query results option only sort or filter the rows displayed in the query results. If the user wishes to sort or filter the entire contents of the query, the user should use the "execute fetch all rows" option. However, this option should not be used if there are a large number of rows expected to be returned from a query, as very large numbers of rows can cause performance problems.

Execute SQL - Fetch All Rows: The Execute SQL Fetch All Rows option works exactly like the Execute SQL option for all queries except those that return results, such as select queries. For queries that return results, the fetch all rows option makes sure to fetch all rows from the database and display all of those rows in the query editor, as opposed to only fetching a set number of rows and not doing another fetch until the user scrolls down to see unfectched rows.

Care should be used when using the fetch all rows options for queries that are expected to return very large result sets. Very large result sets could consume enough memory and generate enough network traffic to cause performance problems.

Execute SQL (All Statements): The Execute All option executes all SQL statements in the query editor.

The Execute All option requires the SQL statements to be separated by statement separators, by default, semi-colons (;).

The execute all option executes each statement one by one. By default, if there is an error, execution will stop. This behavior can be changed in the preferences.

Explain Plan: The Explain Plan option generates an explain plan for a query. To generate an explain plan, either highlight a query, or if nothing is highlighted and the language type is SQL mode, separate the queries in the editor by semi-colons and position the cursor within the query to explain, or if there are no semi-colons or highlighted queries, RazorSQL will send the entire contents of the editor to be explained.

The Explain Plan option is currently available for the following databases:

DB2
HSQLDB
MySQL
Oracle
PostgreSQL
SQLite
SQL Server
Sybase

Commit: When a connection is setup with the auto-commit mode set to off, the SQL Commit and Rollback options become available.

Use the commit option to commit a transaction or statement of work to the database.

Please note that some database do not support transactions, such as older versions of MySQL. In these cases, the commit and rollback options become meaningless. Also, some databases handle transactions differently than others. For example, PostgreSQL handles transactions differently than the other major databases such as Oracle or SQL Server. RazorSQL attempts to handle this by issuing SavePoints when using PostgreSQL in auto-commit off mode so that transactions get into an invalid state.

Rollback: When a connection is setup with the auto-commit mode set to off, the SQL Commit and Rollback options become available.

Use the rollback option to rollback any work since the last commit.

Please note that some database do not support transactions, such as older versions of MySQL. In these cases, the commit and rollback options become meaningless. Also, some databases handle transactions differently than others. For example, PostgreSQL handles transactions differently than the other major databases such as Oracle or SQL Server. RazorSQL attempts to handle this by issuing SavePoints when using PostgreSQL in auto-commit off mode so that transactions get into an invalid state.

Format SQL: RazorSQL includes an SQL Formatter tool that can be used to insert line breaks in user-customized areas such as before and/or after commas, SQL keywords, brackets, AND/OR, etc. It also allows users to specify text formatting such as text wrapping, upper casing, and indenting.

Favorites: RazorSQL includes an SQL Favorites menu option that allows users to add and remove SQL queries or statements to a favorites list. Selecting a query from the favorites list will insert the query into the beginning of the editor. Users can select favorites either by using the SQL Favorites menu or by using the Favorites Selector tool. The selector tool displays a window listing all of the favorites, and allows users to select a query to insert into the editor.

Results Menu

Filter Query Results: Allows the user to filter the displayed query results by specifying criteria for what data should be displayed.

Sort Query Results: Allows the user to sort the query results by whatever column they choose.

Generate SQL Select for Selected Cells: This option uses the cells selected in the query results, and generates SQL select statements using the columns and values for these cells.

Generate SQL Update for Selected Cells: This option uses the cells selected in the query results, and generates SQL update statements using the columns and values for these cells.

Generate SQL Insert for Selected Rows: This option generates SQL insert statements using any rows in the query results that have selected cells.

Generate SQL Delete for Selected Rows: This options generates SQL delete statements using any rows in the query results that have selected cells. If the table that the delete is being generated for has primary keys defined, those keys are used in the where clause of the delete statement. If not, all columns are used to generate the delete where clause.

Tools Menu

Plugins Menu: This menu contains any plugins defined in RazorSQL. See the Plugin Guide for information on how to write plugins.

Macros Menu: The macros menu has options for users to record keyboard macros, and once the macro is recorded, select the macro for execution. Once there have been macros recorded, users can use the Macro Selector shortcut to display a list of available macros for execution.

Key Ahead: The programming editor provides key ahead functionality that can be invoked by using the control/command K keyboard combination or the Tools - Key Ahead menu option.

Key Ahead displays a selectable list of items based on the current language type of the editor. The editor has pre-configured many key ahead items. The user is also able to add items to the key ahead list for languages by selecting the Key Ahead tab of the preferences window.

Zip Utility: The Zip utility allows users to view and extract the contents of Zip or Jar files.

Execute Shell Script / Batch: The execute script option allows users to execute shell or batch scripts located on their machine. The output of the script is then displayed to the user.

Execute OS Command (Non-Windows): The execute OS command allows users to execute operating system commands via EditRocket. This is similar to opening a terminal appication or command prompt.

ASCII Table: The ASCII table option displays an ASCII table with information in decimal, octal, hex, and binary format.

Color Chooser: The Color Chooser allows users to choose colors in HTML Hex or RGB format from a color wheel or by color name.

Set Local Web Server Properties: Set the local web server properties for various programming languages. RazorSQL will then use this information when launching a web browser via the "Show in Web Browser" option.

Programming Language Sub-menus: These are menus containing tools for various programming languages such as PHP, Java, HTML, XML, CSS, etc.

Code Menu

Sidekicks Menu: This contains the sidekicks for the various programming languages supported by RazorSQL such as PL/SQL, Java, PHP, XML, etc.

Inserts Menu: This menu contains code inserts for the various programming languages supported by RazorSQL.

Utils Menu: The utils menu contains language utils for languages such as C, HTML, Java, and XML, and includes such options as encoding and decoding URLs, converting selections to hyperlinks, etc.

Builders Menu: EditRocket provides Code Builders for many programming languages such as C, Java, JavaScript, Perl, PHP, PL/SQL, Python, Ruby, Shell Script, and Transact-SQL. Code Builders allow users to easily create code by selecting options from the code builder window. For example, the PHP Code Builder has information such as function signatures, function descriptions, and version information on all functions available in PHP. The Code Builder lets users easily select one of these functions and insert it into the editor. Function Lookup: The editor provides function lookup for the various languages it supports. Function lookup can be used via the function lookup icon, the control/command L keyboard combination, or the Tools - Function Lookup menu option.

The function lookup works by determining a list of functions based on the current language type in the editor and then displaying them in a selectable list. For example, SQL function lookup would display the SQL aggregate functions. PL/SQL function lookup would display all of the PL/SQL functions like UPPER, DBMS_OUTPUT, etc. The Java function lookup would display methods pertaining to the class type of the variable on which the user is doing the lookup.

Function / Method Navigator: The function / method navigator tool displays a list of functions contained in the currently active editor. The functions are determined based on language type. For example, if the language type is JavaScript, JavaScript functions will be displayed. The functions displayed have a clickable icon so that users can be taken directly to the position in the editor where the function is located.

Query Navigator: The query navigator tool displays a list of SQL queries contained in the currently active editor. The queries displayed have a clickable icon so that users can be taken directly to the position in the editor where the query is located.

Tag Navigator: The tag navigator tool displays a list of tags contained in the currently active editor. The tags displayed have a clickable icon so that users can be taken directly to the position in the editor where the tag is located. The tag list is useful when the language type for the editor is HTML or XML.

Function / Method Selector: This option allows users to quickly jump to functions or method by using the keyboard shortcut for the selector, or by selecting the menu option.

Range Comment: This option inserts a range comment into the editor, or if there is selected text, wraps the selected text in a range comment.

Line Comment: This option inserts a line comment into the editor, or if there is selected text, wraps each line in a line comment.

View Menu

Customize Toolbar: Allows users to customize which icons appear on the toolbar.

Font: Allows users to customize the font for the SQL editor, database navigator, and query results.

Increase Editor Font: Increases the editor font by one size.

Decrease Editor Font: Decreases the editor font by one size.

Programming Language: Allows the user to select the programming language to use for the syntax highlighting and tools such as function lookup for the current editor. Options includes SQL, PL/SQL, TSQL, SQL PL, Batch, C, C++, C#, COBOL, CSS, HTML, Java, JavaScript, JSP, PHP, Perl, Python, Ruby, Shell Script, and XML.

Show Default View: Shows the default RazorSQL view, which includes the database navigator, SQL editor, query results, and status pane.

Show Editor Only: Hides all panes except for the editor.

Query / Programming Editor: Show / Hide the SQL editor.

Query Results: Show / Hide the query results.

Database Navigator: Show / Hide the database navigator.

Status Panel: Show / Hide the status panel.

File System Browser: Show / Hide the file system browser.

Window Menu

Close All Tabs: Close all connections, resources, and hide all tabs.

Close All Except Current: Close all connections and resources, and all tabs except the current tab.

Close All Query Results: Close all SQL statement and result set resources and close all query results for the current active tab.

Close All Results Except Current: Close all SQL statement and result set resources and close all query results except for the current query results for the current active tab.

Next Tab: Make the next RazorSQL tab active.

Previous Tab: Make the previous RazorSQL tab active.

------

 

Check out all the features at https://www.razorsql.com/

 

What about other SQL Tools?

 

FileMaker has plug-ins that also support SQL via JDBC. Thus, SELECT, INSERT, UPDATE, and DELETE are all available for you with FMP with those plugins (assuming the plug-in vendor supports them. MBS, for example, has excellent SQL support).

 

Modern IDEs such as a Java IDE have built-in database panels — totally integrated in the tool.

 

So, using Intellij IDEA as an example, and our same FMP database ZipCodes (with the customer table added), we can do much of the same things we did in RazorSQL.

 

Let’s get all customers with first name = ‘Phil’

Using the database console, you have most of the same export options, and even some better ones depending on your needs. Overall, RazorSQL has more features, but it would be up to you to determine if you need another tool over tools you may already be using.

 

====

 

JDBC Programming

 

Once you master SQL, you can write some Java/JDBC programs to control your data. The same JDBC driver that RazorSQL, and other tools use (like Intellij above), allows you to programmatically select/update/delete/insert data with your live FMP database!

 

(Note that unfortunately, FMI has crippled the JDBC driver to only work on local FMP data, even when programming. You can overcome that limitation by using other techniques, not discussed here.)

 

Programming JDBC in Java is not really that different that what we did in RazorSQL, up to a point. That is, we need to tell the program the name of the database, the password, where the JDBC driver is and such.

We then programmatically execute some SQL command.

Assuming we programmatically issued a SELECT statement, we will get data back in a JDBC “ResultSet” (like a FoundSet).  We then just need to process that result however our application needs it.

 

You can find lots of examples online about writing JDBC programs in Java. Using RazorSQL or a tool like it also will make your database programming more productive.

 

(Note: Razor also supports writing JDBC logic right in the tool!)

 

Conclusion

 

Using an SQL tool like Razor means you don’t have to rely on FMP relationships or work with the FMP interface to check out data. One caveat, however, is that the tables you query must exist in the Relationship Graph. Queries such as getting the time or other non-data type queries that, say, MySQL supports, won’t work with FMP’s SQL.

 

Additionally, having a real SQL tool means the things you want to do with your data like live edit, export, and other “user goals” have been thought about and are available to you.

 

And, once you create a query, you can save it for future re-use.

 

Razor works with just about any JDBC-enabled database (nearly all of them) so your learning investment will work with other database environments too.

 

SQL is used (almost) EVERYWHERE.

 

Finally, for advanced uses, Razor has a command line interface so you can use it in other ways. You can read more about this capability, below:

 

https://razorsql.com/docs/command_line.html

 

 

This article didn’t attempt to explain every feature in Razor or JDBC in general. That would take a book (or more)! The goal was to introduce the developer to an actual SQL tool with expected user goals.

 

----------------

 

Please post comments, and any problems you encountered with this intro and I will revise this document.

8 people found this helpful

Attachments

    Outcomes