Autocomplete delay
Posted by Kyle Hankinson June 30th, 2020
The autocomplete delay option is a perference which allows users to determine the time it takes for the autocomplete window to be displayed when typing in the query editor. It can be found on the General page of SQLPro Preferences.
The default autocomplete delay is 1.25 seconds. With the delay, autocomplete would appear as follows.
Setting the autocomplete delay to 0 would display autocomplete after most-every keystroke.
Exceptions
When entering a database, schema or table, followed by a period, such as:
SELECT * FROM myDatabase.
Autocomplete will instantly appear showing all children of the parent object.
Autocomplete will also not appear automatically after the following characters: ;\"),
It has generally been found that users tend to want whitespace (such as a newline, space, tab, etc) after those characters. Autocomplete can still be triggered by hitting escape.
Tags: Feature
MySQL - SHOW TABLES
Posted by Kyle Hankinson June 24th, 2020
Listing tables
Have you ever needed to see the tables in a MySQL database? MySQL provides a simple and easy query for listing them.
SHOW TABLES
The SHOW TABLES command is the quickest and easiest way to get the list of tables available. You can run this from the MySQL CLI or from any MySQL GUI.
Example
In the example northwind database, running SHOW TABLES will give you results similar to the following:
+--------------------------------+ | Tables_in_northwind | +--------------------------------+ | Alphabetical list of products | | Categories | | Category Sales for 1997 | | Current Product List | | Customer and Suppliers by City | | CustomerCustomerDemo | | CustomerDemographics | | Customers | | EmployeeTerritories | | Employees | | Invoices | | Order Details | | Order Details Extended | | Order Subtotals | | Orders | | Orders Qry | | Product Sales for 1997 | | Products | | Products Above Average Price | | Products by Category | | Quarterly Orders | | Region | | Sales Totals by Amount | | Sales by Category | | Shippers | | Summary of Sales by Quarter | | Summary of Sales by Year | | Suppliers | | Territories | +--------------------------------+
Tables Only?
By default when running SHOW TABLES you will receive a list of both Tables and Views. If you want to narrow down to just showing objects which are a table, you will want the following:
SHOW FULL TABLES WHERE Table_Type = 'BASE TABLE'
Similar, if you want just views then you will want:
SHOW FULL TABLES WHERE Table_Type = 'VIEW'
Additional options
There are a few other options available with the SHOW TABLES command. You can find additional details at the official MySQL Documentation.
Tags: MySQL
MySQL with Swift and Objective-C
Posted by Kyle Hankinson June 24th, 2020
Prefix
In general its a better idea to use a webservice and to keep direct database connections out of your mobile or desktop apps. If you are still looking to connect to a MySQL (or MariaDB) database, MariaDBKit is an iOS and macOS compatible framework for making a connection.
Getting started
MariaDBKit supports Carthage. The easiest way to get started is by adding the following to a Cartfile in your projects root directory. You will want to add the following to your Cartfile.
github "hankinsoft/MariaDB"
Then run 'carthage update' from terminal:
Once completed, you now have a framework available for use in both iOS and macOS apps, which you can link to any Objective-C or Swift project. Switch to the 'Build Phases' tab of your target and either drag the framework in, or add it by using the 'Plus' button, followed by navigating to the Carthage build output directory.
Code
Now comes the fun part. The following sample code allows a connection to the MySQL database, along with running a query and enumerating the results.
import MariaDBKit
let client = MariaDBClient()
do {
try client.connect("sqlprostudio-mysql.csyg8tkobue6.us-west-2.rds.amazonaws.com", username: "sqlprostudio-ro", password: "password123", database: "northwind")
var error : NSError?
let results = client.executeQuery("SHOW TABLES;", error: &error)
if let theError = error {
print(theError)
return
}
while(results.next(&error)) {
print(results.object(forColumnIndex: 0))
}
}
catch {
print(error)
}
@import MariaDBKit;
MariaDBClient * client = [[MariaDBClient alloc] init];
NSError * error = nil;
BOOL success = [client connect: @"sqlprostudio-mysql.csyg8tkobue6.us-west-2.rds.amazonaws.com"
username: @"sqlprostudio-ro"
password: @"password123"
database: @"northwind"
error: &error];
if(!success || error)
{
NSLog(@"Failed to connect to MySQL Server.");
return;
} // End of we had an error
MariaDBResultSet * results = [client executeQuery: @"SHOW TABLES;"
error: &error];
if(error)
{
NSLog(@"Error: %@", error.localizedDescription);
return;
}
while([results next: &error])
{
NSLog(@"%@", [results objectForColumnIndex: 0]);
} // End of results loop
The above code runs the query SHOW TABLES, which gives us a single column result containing the table names. While in this cases we can assume that we are receive a single column, you can also get a list of column names from a result. You might want to do this if you were running a wildcard query against a table (such as SELECT * FROM myTable). The following code example will run a query against the Employees table and give us a list of column names.
import MariaDBKit
let client = MariaDBClient()
do {
try client.connect("sqlprostudio-mysql.csyg8tkobue6.us-west-2.rds.amazonaws.com", username: "sqlprostudio-ro", password: "password123", database: "northwind")
var error : NSError?
let results = client.executeQuery("SELECT * FROM Employees;", error: &error)
if let theError = error {
print(theError)
return
}
// Output the number of columns along with the list of columns themselves.
print(String(format: "Table has %ld columns.\r\n%@.", results.columnNames.count,
results.columnNames.joined(separator: ",")))
}
catch {
print(error)
}
@import MariaDBKit;
MariaDBClient * client = [[MariaDBClient alloc] init];
NSError * error = nil;
BOOL success = [client connect: @"sqlprostudio-mysql.csyg8tkobue6.us-west-2.rds.amazonaws.com"
username: @"sqlprostudio-ro"
password: @"password123"
database: @"northwind"
error: &error];
if(!success || error)
{
NSLog(@"Failed to connect to MySQL Server.");
return;
} // End of we had an error
MariaDBResultSet * results = [client executeQuery: @"SHOW TABLES;"
error: &error];
if(error)
{
NSLog(@"Error: %@", error.localizedDescription);
return;
}
NSLog(@"Table has %ld columns.\r\n%@.", results.columnNames.count, [results.columnNames componentsJoinedByString: @","]);
Up Next
For the next blog post, I plan on creating a small SwiftUI wrapper around this. It will allow a user to enter a host, username and password to connect. Then once connected, a text entry where a user can enter a query and finally a results grid which will display the results of the query.