sql

A Drupal 8 XML Sitemap generating PHP script

I’m not going to comment on the following code too much or provide support for it, but (a) if you need to create an XML Sitemap for a Drupal 8 website, and (b) you don’t like the Drupal 8 sitemap modules that are available, then (c) this PHP script can serve as a starting point for you.

Scala: How to connect to a SQL database with the Spring Framework alvin June 20, 2015 - 6:22pm

This is an excerpt from the Scala Cookbook (partially modified for the internet). This is Recipe 16.2, “How to connect to a database with the Spring Framework.”

Problem

You want to connect to a database using the Spring Framework. This gives you a nice way to add connection pooling and other capabilities to your SQL code.

A Play Framework Anorm insert method that returns the database auto-insert ID alvin March 23, 2014 - 10:06pm

As a quick example, if you need to see some code for a Play Framework Anorm method that inserts an object into a relational database table and returns the auto-into (MySQL auto_increment) ID field on success, here you go:

A Scala JDBC connection and SQL SELECT example alvin November 8, 2011 - 12:29pm

Scala JDBC FAQ: How can I use the Java JDBC API in my Scala application?

If you want to use a SQL database with your Scala applications, it's good to know you can still use the traditional Java JDBC programming library to access databases. I just ran a simple JDBC connection and SQL SELECT test, and everything seems to work just as it does in Java.

Handling Drupal SQL exceptions (db_insert, db_update, db_delete)

While developing a Drupal module, I just ran into a situation where it may be common for users to generate SQL exceptions. I have a 'unique' limit on several of my database tables, essentially saying that certain name fields must be unique for the current project. As you can imagine, it's extremely easy to enter a duplicate name, and while doing a SQL INSERT that can easily lead to a SQL exception.

As a result, I dug around and found a way to handle SQL exceptions in Drupal queries. My current problem is with a Drupal 7 db_insert query, and I handled it like this:

A Drupal 7 SQL cheat sheet

I'm currently developing a new Drupal 7 application (technically built as a new Drupal module), and as I'm building the application, I've made notes on database queries I've made.

The approach I'm taking is to use the following Drupal SQL query functions. As you can see from the function names, there are now different Drupal functions for each query type:

A Drupal 7 SQL DELETE example

Drupal 7 SQL FAQ: How do I perform a SQL DELETE query in Drupal 7?

The short answer is that you use the Drupal 7 db_delete function, like this:

db_delete('projects')
  ->condition('id', 10)
  ->condition('user_id', 5)
  ->execute();

This example assumes that your database fields are named 'id' and 'user_id', and the values of 10 and 5 are the values in your database table you want to delete. If you just want to delete by id, your query is simpler, like this:

A Drupal 7 db_insert query (SQL INSERT statement)

Drupal 7 database FAQ: How do I use the Drupal dbquery function to perform a SQL INSERT? (Or, what is the Drupal 7 dbinsert syntax?)

I didn't type db_query in that question by mistake. I just spent 45 minutes trying to use it for a Drupal 7 SQL INSERT, which of course I've now learned doesn't work.

In short, if you're looking for a Drupal 7 db_insert example that shows how to perform a SQL INSERT, and also happens to show some SQL Timestamp fields, here's a 'submit' function I'm currently writing:

Creating a PHP date in the format for a SQL Timestamp insert

PHP date/time FAQ: How do I create a date in the proper format to insert a SQL Timestamp field into a SQL database?

First off, you may not need to create a date in PHP like this. If you're using plain old PHP and a database like MySQL, you can use the SQL 'now()' function to insert data into a SQL timestamp field, like this: