I am running Win Vista.
I create a project with Lein new fruit in C:\projects
I cd to fruit in C:/projects/fruit
I copy code from the mysql project that runs when reading the book table.
This is the project code and the core code to access mysql.
I added code to create the table "fruit" in test db.
I run Lein deps
I run lein repl
I enter statement"(load "/fruit/core")
I enter statement "(fruit.core/create-fruit)
I get error message
==============================
user=> (fruit.core/insert-rows-fruit)
Exception no current database connection
clojure.java.jdbc.internal/connection*
(internal.clj:40)
user=>
fruit/core.clj fiele
======================
I have deleted create-fruit
I'm not sure what you mean by
How exactly are you running your code
(you never answer this one)?
===================
(ns fruit.core
(:require [clojure.java.jdbc :as sql]))
(def db {:classname "com.mysql.jdbc.Driver"
:subprotocol "mysql"
:subname "//localhost:3306/test"
:user "root"
:password "pass"})
; Inserting multiple rows
;If you want to insert complete rows, you can use insert-rows and provide
the values as a simple vector for each row. Every column's value must be
present in the same order the columns are declared in the table. This
performs a single insert statement. If you attempt to insert a single row, a
map of the generated keys will be returned.
(defn insert-rows-fruit
(sql/with-connection db
"Insert complete rows"
[]
(sql/insert-record :books
(sql/insert-rows
:fruit
["Apple" "red" 59 87]
["Banana" "yellow" 29 92.2]
["Peach" "fuzzy" 139 90.0]
["Orange" "juicy" 89 88.6]))
;Inserting partial rows
;If you want to insert rows but only specify some columns' values, you can
use insert-values and provide the names of the columns following by vectors
containing values for those columns. This performs a single insert
statement. If you attempt to insert a single row, a map of the generated
keys will be returned.
(defn insert-values-fruit
"Insert rows with values for only specific columns"
[]
(sql/insert-values
:fruit
[:name :cost]
["Mango" 722]
["Feijoa" 441]))
;Inserting a record
;If you want to insert a single record, you can use insert-record and
specify the columns and their values as a map. This performs a single insert
statement. A map of the generated keys will be returned.
(defn insert-record-fruit
"Insert a single record, map from keys specifying columsn to values"
[]
(sql/insert-record
:fruit
{:name "Pear" :appearance "green" :cost 99}))
;Inserting multiple records
;If you want to insert multiple records, you can use insert-records and
specify each record as a map of columns and their values. This performs a
separate insert statement for each record. The generated keys are returned
in a sequence of maps.
(defn insert-records-fruit
"Insert records, maps from keys specifying columns to values"
[]
(sql/insert-records
:fruit
{:name "Pomegranate" :appearance "fresh" :cost 585}
{:name "Kiwifruit" :grade 93}))
;Using transactions
;You can write multiple operations in a transaction to ensure they are
either all performed, or all rolled back.
(defn db-write
"Write initial values to the database as a transaction"
[]
(sql/with-connection db
(sql/transaction
; (drop-fruit)
; (create-fruit)
(insert-rows-fruit)
(insert-values-fruit)
(insert-records-fruit)))
nil)
;Reading and processing rows
;To execute code against each row in a result set, use with-query-results
with SQL.
(defn db-read
"Read the entire fruit table"
[]
(sql/with-connection db
(sql/with-query-results res
["SELECT * FROM fruit"]
(doseq [rec res]
(println rec)))))
(defn db-read-all
"Return all the rows of the fruit table as a vector"
[]
(sql/with-connection db
(sql/with-query-results res
["SELECT * FROM fruit"]
(into [] res))))
(defn db-grade-range
"Print rows describing fruit that are within a grade range"
[min max]
(sql/with-connection db
(sql/with-query-results res
[(str "SELECT name, cost, grade "
"FROM fruit "
"WHERE grade >= ? AND grade <= ?")
min max]
(doseq [rec res]
(println rec)))))
(defn db-grade-a
"Print rows describing all grade a fruit (grade between 90 and 100)"
[]
(db-grade-range 90 100))
;Updating values across a table
;To update column values based on a SQL predicate, use update-values with a
SQL where clause and a map of columns to new values. The result is a
sequence of update counts, indicating the number of records affected by each
update (in this case, a single update and therefore a single count in the
sequence).
(defn db-update-appearance-cost
"Update the appearance and cost of the named fruit"
[name appearance cost]
(sql/update-values
:fruit
["name=?" name]
{:appearance appearance :cost cost}))
(defn db-update
"Update two fruits as a transaction"
[]
(sql/with-connection db
(sql/transaction
(db-update-appearance-cost "Banana" "bruised" 14)
(db-update-appearance-cost "Feijoa" "green" 400)))
nil)
;Updating values or Inserting records conditionally
;If you want to update existing records that match a SQL predicate or insert
a new record if no existing records match, use update-or-insert-values with
a SQL where clause and a map of columns to values. This calls update-values
first and if no rows were updated, this calls insert-values. The result is
either the sequence of update counts from the update or the sequence of
generated key maps from the insert.
(defn db-update-or-insert
"Updates or inserts a fruit"
[record]
(sql/with-connection db
(sql/update-or-insert-values
:fruit
["name=?" (:name record)]
record)))
;Exception Handling and Transaction Rollback
;Transactions are rolled back if an exception is thrown, as shown in these
examples.
(defn db-exception
"Demonstrate rolling back a partially completed transaction on exception"
[]
(sql/with-connection db
(sql/transaction
(sql/insert-values
:fruit
[:name :appearance]
["Grape" "yummy"]
["Pear" "bruised"])
;; at this point the insert-values call is complete, but the
transaction
;; is not. the exception will cause it to roll back leaving the
database
;; untouched.
(throw (Exception. "sql/test exception")))))
(defn db-sql-exception
"Demonstrate an sql exception"
[]
(sql/with-connection db
(sql/transaction
(sql/insert-values
:fruit
[:name :appearance]
["Grape" "yummy"]
["Pear" "bruised"]
["Apple" "strange" "whoops"]))))
(defn db-batchupdate-exception
"Demonstrate a batch update exception"
[]
(sql/with-connection db
(sql/transaction
(sql/do-commands
"DROP TABLE fruit"
"DROP TABLE fruit"))))
(defn db-rollback
"Demonstrate a rollback-only trasaction"
[]
(sql/with-connection db
(sql/transaction
(prn "is-rollback-only" (sql/is-rollback-only))
(sql/set-rollback-only)
(sql/insert-values
:fruit
[:name :appearance]
["Grape" "yummy"]
["Pear" "bruised"])
(prn "is-rollback-only" (sql/is-rollback-only))
(sql/with-query-results res
["SELECT * FROM fruit"]
(doseq [rec res]
(println rec))))
(prn)
(sql/with-query-results res
["SELECT * FROM fruit"]
(doseq [rec res]
(println rec)))))
;Copyright 2007-2011 by Rich Hickey and the various contributors
;Logo & site design by Tom Hickey.
===========================================\(defproject mysql
"1.0.0-SNAPSHOT"
:description "FIXME: write description"
:dependencies [[org.clojure/clojure "1.3.0"]
[org.clojure/java.jdbc "0.0.6"]
[mysql/mysql-connector-java "5.1.6"]])
----- Original Message -----
From: "Sean Corfield" <seancorfi...@gmail.com>
To: <clojure@googlegroups.com>
Sent: Monday, January 09, 2012 9:57 AM
Subject: Re: mysql and fruit tables
On Sun, Jan 8, 2012 at 8:26 PM, jayvandal <s...@ida.net> wrote:
I have been able to access tables in mysql, but not able to add
records. I look at the examples fo "fruit so I created a lein named
fruitI made th project file as the mysql project file. I copied the
database instructions as in mysql. I added all of the statements for
the "fruit" example but I can't even get it to access the connection
or the database
You must provide more detail when asking questions: what is the exact
error message / problem you see? How exactly are you running your code
(you never answer this one)?
Here is the project file ;
defproject fruit "1.0.0-SNAPSHOT"
:description "FIXME: write description"
:dependencies [[org.clojure/clojure "1.3.0"]]
[org.clojure/java.jdbc "0.0.6"]
[mysql/mysql-connector-java "5.1.6"])
I assume you haven't pasted this correctly?
There was no ( before defproject and you close the dependencies on
line 3 so java.jdbc and mysql-connector-java are not part of that
list. Here's what it should look like:
(defproject fruit "1.0.0-SNAPSHOT"
:description "FIXME: write description"
:dependencies [[org.clojure/clojure "1.3.0"]
[org.clojure/java.jdbc "0.0.6"]
[mysql/mysql-connector-java "5.1.6"]])
(defn db {:classname "com.mysql.jdbc.Driver"
:subprotocol "mysql"
:subname "//localhost:3306/world"
:user "root"
:password "pass"})
This should be a def not a defn.
(defn create-fruit []
(sql/with-connection db
(sql/create-table
:fruit
[:id :integer "PRIMARY KEY" "AUTO_INCREMENT"]
[:name "varchar(25)"]
[:appearance "varchar(25)"]
[:cost "integer(5)"]))
You're missing a closing parenthesis here.
(defn insert-rows-fruit
"Insert complete rows"
[]
(sql/insert-rows
:fruit
["Apple" "red" 59 87]
["Banana" "yellow" 29 92.2]
["Peach" "fuzzy" 139 90.0]
["Orange" "juicy" 89 88.6]))
This doesn't match how you declared the table: you declared ID
(integer), NAME (varchar), APPEARANCE (varchar), COST (integer) but
you are trying to insert string, string, integer, double.
The http://clojure.github.com/java.jdbc documentation is correct but
you have changed the code to something that does not work.
--
Sean A Corfield -- (904) 302-SEAN
An Architect's View -- http://corfield.org/
World Singles, LLC. -- http://worldsingles.com/
"Perfection is the enemy of the good."
-- Gustave Flaubert, French realist novelist (1821-1880)
--
You received this message because you are subscribed to the Google
Groups "Clojure" group.
To post to this group, send email to clojure@googlegroups.com
Note that posts from new members are moderated - please be patient with your
first post.
To unsubscribe from this group, send email to
clojure+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/clojure?hl=en
--
You received this message because you are subscribed to the Google
Groups "Clojure" group.
To post to this group, send email to clojure@googlegroups.com
Note that posts from new members are moderated - please be patient with your
first post.
To unsubscribe from this group, send email to
clojure+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/clojure?hl=en
