headers
Liam M. Healy | 2 Jan 2006 04:18
Picon

Oracle user identified externally

I am trying to connect using clsql-oracle to an Oracle instance with a
user identified externally, that is, authenticated by the operating
system and so with no Oracle username or password.  I have tried to
connect in many different ways, 
  (clsql:connect (list "xxx" "" "") :database-type :oracle)
  (clsql:connect (list "xxx" " " " ") :database-type :oracle)
  (clsql:connect (list "xxx" nil nil) :database-type :oracle)
none of which work.  I have a Pro*C function that works for this kind
of authentication (see http://paste.lisp.org/display/14025) but I
don't know how to turn this into a clsql form.

I would appreciate any advice on how to make this connection.

Liam
Permalink | Reply | 0 comments |
headers
Will McCutchen | 4 Jan 2006 20:35
Picon

Automatically update primary keys

Hi all,

I'm sorry if this is an issue that has been brought up before...  I
searched for a solution the best I could, but maybe I'm not using the
correct terms.

Is there a way to define a view-class that will have an
automatically-generated id as its primary key?  I would like to be
able to create new instances of my view-classes without having to
calculate a unique ID for each instance, if possible.

To this point, I've been working solely with SQLite 3, but eventually
will be moving over to Postgres for production.  I've tried, as a
simple example, many variations on this view-class:

(def-view-class foo ()
  ((id :reader foo-id
       :type integer
       :db-kind :key
       :db-constraints (:auto-increment :not-null :unique)
       :initform nil)
   (bar :accessor foo-bar
        :type string
        :db-constraints :not-null
        :initarg :bar
        :initform nil)))

Am I approaching the problem the wrong way?  Is there a built-in
solution that I'm overlooking?
(Continue reading)

Permalink | Reply | 7 comments |
headers
Alan Caulkins | 4 Jan 2006 18:36

Re: Automatically update primary keys

Last I checked, there was an :auto-incrememnt option for fields, but it
only worked in Postgresql. I use Postgresql, and it worked, but I wanted a
more portable solution. I think the best way to do it is something like
this:

 (def-view-class foo ()
   ((id :reader foo-id
        :type integer
        :db-kind :key
        :db-constraints :not-null
        :initform (sequence-next 'some-sequence-name))))

, assuming you have a database sequence named some_sequence_name. The
:unique constraint is already enforced by :db-kind :key, and the :initform
will increment the field whenever a new object is created (pulling objects
from the db with select will not cause :initforms to execute).

I'm not at all a RDBMS expert, but I've seen auto-increment fields in
various DB systems, and they all feel pretty kludgy (case in point -
finding the last assigned auto-increment number in order to query with the
ID of a newly created record). I've concluded that the sequence method
shown above is probably the preferred way of doing it, since many
auto-increment features seem to be (unportable) shorthand for exactly the
same thing.

If some more learned person would like to shed some light on the question,
I'd be grateful.

-A
(Continue reading)

Permalink | Reply | 3 comments |
headers
Will McCutchen | 4 Jan 2006 23:47
Picon

Re: Automatically update primary keys

Thanks for the response, Alan.

[snip]
> I think the best way to do it is something like
> this:
>
>  (def-view-class foo ()
>    ((id :reader foo-id
>         :type integer
>         :db-kind :key
>         :db-constraints :not-null
>         :initform (sequence-next 'some-sequence-name))))
>
> , assuming you have a database sequence named some_sequence_name.

Could you elaborate a little on this part?  I haven't spent too much
time doing database programming, and I don't know what you mean by "a
database sequence."  Some quick googling led me to this page:

http://db.apache.org/ojb/docu/howtos/howto-use-db-sequences.html

which includes the following simple example:

CREATE TABLE thingie
(
    name VARCHAR(50),
    id INTEGER DEFAULT NEXTVAL('UniqueIdentifier')
)
CREATE SEQUENCE UniqueIdentifier;
(Continue reading)

Permalink | Reply | 2 comments |
headers
Alan Caulkins | 4 Jan 2006 19:31

Re: Automatically update primary keys

> Thanks for the response, Alan.

Sure thing.

> Could you elaborate a little on this part?  I haven't spent too much
> time doing database programming, and I don't know what you mean by "a
> database sequence."  Some quick googling led me to this page:

A database sequence is basically just a persistent integer variable that
you define in the DB with the CREATE SEQUENCE statement. In SQL, you can
increment the variable and get the result all at once by using the
nextval() function. You can also get the value of the last increment
using the lastval() function. Since this can all happen within the
protection of a transaction, it's a nice way to generate unique keys. I
suspect that's what it was designed for in the first place.

Here's more info:
	http://www.postgresql.org/docs/8.1/static/functions-sequence.html

> I think I understand the concept.  Is CLSQL smart enough to know that
> the database has a sequence defined?

No, it's not smart enough to divine the existance of the sequence
automatically - you have to have it created before your code runs, just
like you have to create tables beforehand using create-view-from-class or
an SQL statement. You can make a sequence in lisp by using the
create-sequence function from cl-sql:
	(create-sequence 'some-name :database database-object)

Hope this helps!
(Continue reading)

Permalink | Reply | 1 comment |
headers
Will McCutchen | 5 Jan 2006 18:58
Picon

Re: Automatically update primary keys

Aha, I think I'm starting to get it, a little bit.

> > I think I understand the concept.  Is CLSQL smart enough to know that
> > the database has a sequence defined?
>
> No, it's not smart enough to divine the existance of the sequence
> automatically - you have to have it created before your code runs, just
> like you have to create tables beforehand using create-view-from-class or
> an SQL statement. You can make a sequence in lisp by using the
> create-sequence function from cl-sql:
>         (create-sequence 'some-name :database database-object)

When I read your first response, I thought (sequence-next) was a CL
function, not a CLSQL function... reading the docs a little closer
helps, sometimes.

> Hope this helps!

It did.  Thanks a lot.

Will.
Permalink | Reply | 0 comments |
headers
Aurelio Bignoli | 8 Jan 2006 19:21
Picon

Automatically update primary keys

Will McCutchen writes:
 > Hi all,
 > 
 > I'm sorry if this is an issue that has been brought up before...  I
 > searched for a solution the best I could, but maybe I'm not using the
 > correct terms.
 > 
 > Is there a way to define a view-class that will have an
 > automatically-generated id as its primary key?  I would like to be
 > able to create new instances of my view-classes without having to
 > calculate a unique ID for each instance, if possible.
 > 
 > To this point, I've been working solely with SQLite 3, but eventually
 > will be moving over to Postgres for production.

as explained by Alan, the portable (across different RDBMSs) solution
is to use a sequence. However, it is possible to take advantage of
SQLite3 built-in autoincrement keys by simply specializing two CLSQL
internal methods:

(defmethod clsql-sys:database-get-type-specifier ((type (eql 'integer)) 
						  args database 
						  (db-type (eql :sqlite3)))
  (declare (ignore database db-type))
  (if args
      (format nil "INTEGER(~A)" (car args))
    "INTEGER"))

(defmethod clsql-sys::database-pkey-constraint ((class clsql-sys::standard-db-class) 
						(database clsql-sqlite3::database)))
(Continue reading)

Permalink | Reply | 2 comments |
headers
Will McCutchen | 9 Jan 2006 00:58
Picon

Re: Automatically update primary keys

On 1/8/06, Aurelio Bignoli <clsql@...> wrote:
> as explained by Alan, the portable (across different RDBMSs) solution
> is to use a sequence. However, it is possible to take advantage of
> SQLite3 built-in autoincrement keys by simply specializing two CLSQL
> internal methods:

Thanks for the info, Aureliano.  I think I had already seen you post
your two specialized CLSQL internal methods somewhere else, and I had
already tried them (with success).  Since I am planning to use
Postgres in production, I would rather avoid any database-specific
hacks, if possible.  I was glad to learn about sequences, so my code
could remain a little bit more portable.

Again, thanks for the help,

Will.
Permalink | Reply | 1 comment |
headers
Will McCutchen | 9 Jan 2006 00:59
Picon

Re: Automatically update primary keys

> Thanks for the info, Aureliano.

Geez, sorry.  Thanks for the info, *Aurelio*.  I need to learn to read
a little bit better.  Sorry to spam the list...
Permalink | Reply | 0 comments |
headers
Zak Wilson | 14 Jan 2006 07:35
Picon

Postgresql - case lost in text fields with view-class

Hi. I'm new to PostgreSQL, CLSQL and this mailing list. I may be missing something obvious, but I didn't see anything topical in the documentation. I have a text field in my database, which is also included in a view class. The contents of the slot in the view-class are always upper-case regardless of the actual data, which is not what I expected. This doesn't happen with varchar fields, nor does it happen if I use the query function. I suspect something is calling string-upcase, which is not what I want. Is there a trivial way to change this behavior? 

_______________________________________________
CLSQL-Help mailing list
CLSQL-Help@...
http://lists.b9.com/mailman/listinfo/clsql-help
Permalink | Reply | 0 comments |

Gmane