Take the 2-minute tour ×
Drupal Answers is a question and answer site for Drupal developers and administrators. It's 100% free, no registration required.

I have a table that contains a char(36) field for a uuid. It seems like I can insert data into that field with a mysql query like: INSERT INTO mytable (uid, event_guid) VALUES (1, uuid());. I'd like to make a query like that in DBTNG syntax, but I believe I will just insert the string "uuid()" into the column.

db_insert('mytable')
  ->fields(array('uid' => $uid, 'event_guid' => 'uuid()'))
  ->execute();

So...do I just use db_query? Is there some magic in dbtng to handle this?

share|improve this question
1  
I think you're stuck with db_query for that one, db_insert doesn't have an expression() method...unless your table has a primary key that isn't the uuid column, in which case you might be able to ever-so-slightly abuse db_merge() which does have that method –  Clive Dec 4 '12 at 0:30
    
Expressions are used from the MergeQuery class when the table needs to be updated, not to insert new data in the database. See MergeQuery::execute(), in particular the part after if ($this->needsUpdate) {. –  kiamlaluno Dec 4 '12 at 17:47
    
I created an issue for this idea drupal.org/node/1858188 –  greggles Dec 7 '12 at 16:01
add comment

3 Answers

up vote 1 down vote accepted

Here's what I ultimately did:

db_query("INSERT INTO {mytable} (uid, event_guid) VALUES (:uid, uuid())",
array(':uid' => $uid));

And that works to use the mysql uuid() function in an insert.

I've created an issue to add this to Drupal 8 http://drupal.org/node/1858188

share|improve this answer
add comment

You can use function calls in the fields array. The following code should just work:

db_insert('mytable')
  ->fields(array('uid' => $uid, 'event_guid' => uuid()))
  ->execute();

If you wanted to insert multiple entries in a loop something like this would work:

$event_guid = uuid();
foreach($entries as $entry) {
  $entry['event_guid'] = $event_guid;
  db_insert('mytable')
    ->fields($entry)
    ->execute();
}
share|improve this answer
2  
greggles want to use mysql's uuid function with in db_insert() not a php function :( –  Mathankumar Dec 4 '12 at 7:42
    
Knowing greggles, I would not think he is asking how to use the output from a PHP function for a query. He made that clear when he showed the equivalent SQL query he would use without the Drupal database API. –  kiamlaluno Dec 4 '12 at 9:51
    
Yeah, unfortunately this won't work, as kiamlaluno and Mathankumar pointed out. –  greggles Dec 4 '12 at 15:22
add comment
            $uuid = db_query("SELECT uuid() AS uuid ")->fetchField();

            db_insert('mytable')
            ->fields(array('uid' => $uid, 'event_guid' => $uuid))
            ->execute();

A workaround to the problem.

share|improve this answer
    
An interesting idea. This would work OK for a single instance but wouldn't be great for a multi-value insert. It also is probably not super performant since you are doing two queries (one db_query, on db_insert) where my ultimate solution was one db_query("INSERT...UUID()..."); –  greggles Oct 16 '13 at 20:42
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.