3

Is there a way of using the given FDML interface to insert multiple records at once?

The given insert-record statement can only handle one value tuple at once and the idea of calling this function uncountable times, instead of once is bugging me quite a bit, and I guess (without actually having done any profiling) that this would not be the fastest approach either.

Sim
  • 4,199
  • 4
  • 39
  • 77
  • It was long time ago that I used `clsql`, but I believe that you can just construct an arbitrary query, if the one of the predefined variety doesn't work for you... Another possibility to reasearch is to insert the contents of a temporary table, this can be sometimes preferred if, say, you could generate some of the data during generation of the said table. –  Aug 07 '13 at 19:46
  • well, currently I just construct a string query which works fine, but I thought that if there is such a nice interface why not try to work with it. Your suggestion seems to be a bit too complicated for such a seemingly trivial problem. But thx for the suggestion. – Sim Aug 07 '13 at 20:20

1 Answers1

2

How about something like this?

; SLIME 2013-04-02
CL-USER> (ql:quickload "clsql")
To load "clsql":
  Load 1 ASDF system:
    uffi
  Install 1 Quicklisp release:
    clsql
; Fetching #<URL "http://beta.quicklisp.org/archive/clsql/2013-04-20/clsql-20130420-git.tgz">
; 900.99KB
==================================================
922,610 bytes in 1.92 seconds (468.78KB/sec)
; Loading "clsql"
[package uffi]....................................
[package cmucl-compat]............................
[package clsql-sys]...............................
[package clsql]...................................
[package clsql-user]..............................
..................................................
[package ansi-loop]..............................
("clsql")
CL-USER> (ql:quickload "clsql-sqlite3")
To load "clsql-sqlite3":
  Load 1 ASDF system:
    clsql-sqlite3
; Loading "clsql-sqlite3"
[package clsql-uffi]..............................
[package clsql-sqlite3]...........................
[package sqlite3]........
("clsql-sqlite3")
CL-USER> (clsql:connect '("./test.db") :database-type :sqlite3)
#<CLSQL-SQLITE3:SQLITE3-DATABASE ./test.db OPEN {10080C08E3}>
CL-USER> (clsql:enable-sql-reader-syntax)
; No value
CL-USER> (clsql:create-table [test_insert]
                             '(([id] integer)
                               ([first_name] text)
                               ([last_name] text)))
; No value
CL-USER> (clsql:insert-records :into [test_insert]
                               :attributes '(id first_name last_name)
                               :values '(0 "John" "Neumann"))
; No value
CL-USER> (clsql:select [id] [first_name] [last_name] :from [test_insert])
((0 "John" "Neumann"))
("ID" "FIRST_NAME" "LAST_NAME")
CL-USER> (clsql:insert-records
           :into [test_insert]
           :attributes '(id first_name last_name)
           :query (clsql:sql-expression :string
           "select 1 as id, 'Albert' as first_name, 'Einstein' as last_name
            union select 2, 'Alan', 'Turing'"))
; No value
CL-USER> (clsql:select [id] [first_name] [last_name] :from [test_insert])
((0 "John" "Neumann") (1 "Albert" "Einstein") (2 "Alan" "Turing"))
("ID" "FIRST_NAME" "LAST_NAME")

Maybe you could construct the insertion query in some other way (other databases may provide different syntax). clsql has (or, more properly has not) a syntax for column renaming... so you'd have to manipulate strings / extend it to have it use symbols instead.

  • in order to scale this you'd still have to write a string-generation function for writing the corresponding string query, therefore you could just use (clsql:query ...). But I guess that this might be the only (currently) available solution fitting my actual request/question – Sim Aug 10 '13 at 14:16
  • @Sim `clsql:query` argues about missing `:from` in `select`... maybe you could do something like `select from dual` or whatever fake table is available in your DBMS, but that just sounds wrong... –  Aug 10 '13 at 16:56
  • I meant you could just write a string-generation method witch generates a corresponding SQL "INSERT INTO ... (...) VALUES (...)" statement then to be used with `clsql:query`. If you meant something else, please specify as I then did not understand it. – Sim Aug 10 '13 at 19:51
  • @Sim yes, I meant you could have used `(clsql:select ...)` instead of `(clsql:sql-expression ...)`, but the library wants you to specify the table in the `from` clause of the query, which maybe required by the standard, but many databases allow you to omit it, if you are selecting something other then actual table, like, say, if you select just a number, or last insertion id etc. –  Aug 10 '13 at 20:04