sql

Flutter, sqflite, and escaping quotes with SQL INSERT and UPDATE statements

When you want to do a SQL INSERT or UPDATE using the Flutter sqflite package, I’ve found that it’s best to use their insert and update methods (as shown below) so you don’t have to worry about escaping single- and double-quotes. This is similar to the reason why Java developers switched from Statement to PreparedStatement way back in the late 1900s.

As an example, given this SQLite database table:

SQL joins as Venn diagrams alvin March 20, 2019 - 1:06am

I saw this “SQL joins as Venn diagrams” image on this Twitter page. To give attribution to the original author, it appears to have been created by C.L. Moffatt and documented in this Visual Representation of SQL Joins article. However, this article makes the case that Venn diagrams are not an accurate description of SQL joins.

A ScalikeJdbc SQL SELECT example that maps results to a list of case class instances

Here’s another ScalikeJdbc example. The main benefits of this example are:

  • It shows how to perform a SQL SELECT with ScalikeJdbc
  • How to map the ResultSet values to create a series of objects from the ResultSet
  • How to connect to a database with ScalikeJdbc using a JDBC style of connecting (as opposed to putting the JDBC parameters in a configuration file)

Here’s the ScalikeJdbc example code:

A ScalikeJdbc SQL UPDATE syntax example

Here’s an example of how to write a SQL UPDATE query with ScalikeJdbc:

def updateUrlClickGeoInfo(urlClickId: Long, gi: GeoInfo): Int = NamedDB('kbhr) localTx { implicit session =>
    sql"""
    update url_clicks
    set city = ${gi.city}, state = ${gi.state}, country = ${gi.country}
    where id = ${urlClickId}
    """.update.apply()
}

(As a note to self, this code comes from my PopulateKbhrGeo1 app.)

A ScalikeJdbc SQL SELECT example with WrappedResultSet

Here’s another Scala ScalikeJdbc SQL SELECT example. A few unique things about this example is that it shows the JDBC-style parameters; it shows how to use a case class and the “service” style approach; and it shows how to use the WrappedResultSet as shown. Here’s the code:

A simple ScalikeJdbc SQL SELECT example

I don’t have time to write about ScalikeJdbc right now, so I’ll just say that it’s been working well for me. Here’s one of the first test examples I created, a simple SQL SELECT query that prints some output from a database table:

A SQL select group by, order by, and count query

As a note to self, this is how I wrote a “group by” and “order by” SQL query that gives me the number of times each country code occurs in a database table named url_clicks:

select country, count(1) as the_count
from url_clicks
where url_id=6
and country != ''
group by country
order by the_count desc

Results of this query look like this:

US, 50
CA, 30
GB, 15

That tells me that 50 people in the US clicked on the link with the id=6, 30 people from CA clicked on the same link, and 15 people from GB clicked on that link as well.