[plt-scheme] bzlib/dbi error trying to insert a null value

From: Scott Hickey (sshickey at qwest.net)
Date: Thu Oct 1 16:46:44 EDT 2009

Using the latest dbi + jazmysql, I get an error trying to insert a null value into column defined as nullable in MySQL.

Also, I added code that is MySQL specific to return the row id for the row inserted. Is there a database agnostic way to get that id using the dbi module?

(define (get-handle) (connect 'jazmysql "localhost" 3306 "golfscore" "golfscore" '#:schema "golfscore"))

(define-struct hole_played (hole_number par score putts fairway_hit round_played_id [id #:auto] ))

(define (insert-hole_played hp)
  (let ((a-handle (get-handle)))
    (exec a-handle "insert into hole_played (hole_number, par, score, putts, fairway_hit, round_played_id) 
VALUES (?hole_number, ?par, ?score, ?putts, ?fairway_hit, ?round_played_id)" 
          `( (hole_number . ,(hole_played-hole_number hp)) 
             (par . ,(hole_played-par hp))
             (score . ,(hole_played-score hp))
             (putts . ,(hole_played-putts hp))
             (fairway_hit . ,(hole_played-fairway_hit hp))
             (round_played_id . ,(hole_played-round_played_id hp))))
    (caadr (query a-handle "select LAST_INSERT_ID()" '()))))

(insert-hole_played (make-hole_played 1 4 4 2 null 1))

uncaught exception: #<exn:mysql errno=1210 sqlstate="HY000" message="Incorrect arguments to mysql_stmt_execute">

Scott Hickey
Senior Consultant
Object Partners, Inc.

Posted on the users mailing list.