Hi all,
newbie here, please go easy on me...
x86 gentoo linux, sbcl-0.9.12, clsql-3.5.6
I have an existing mysql database table that has an uppercase database
name (ex. DATABASE) and lowercase table and column names (ex. table,
col1, col2, etc...). I didn't design the database so don't yell at me!
:-) I want to run some statistics on the data and generate a report
(HTML is fine) and I want to use clsql to access the data.
So I created a 'view class' (not sure I need this...) that mirrors the
existing mysql table. clsql connects fine to the database but defaults
all strings to uppercase. I looked through the code a little and saw
that it seems to do this for all databases except postgres. Since its
not my database I'm stuck with mysql.
So I started off on a journey to 'make it work' as a learning
experience and then I thought I'd ask some experts on how to do this
the right way. Below is what I had to do to get it to work. Is there
a better way?
Thanks,
Greg
;; ------------------------------------------------------------
(pushnew :clsql-lowercase-reader *features*)
(asdf:operate 'asdf:load-op 'clsql)
;; clsql remove this from the *features* so put it back in
(pushnew :clsql-lowercase-reader *features*)
;; change to clsql-sys package to re-write some functions
(in-package :clsql-sys)
(defun symbol-name-default-case (str)
#-clsql-lowercase-reader
(string-upcase str)
#+clsql-lowercase-reader
(string-downcase str))
(defmethod db-type-default-case ((db-type (eql :mysql)))
:lower)
(defun convert-to-db-default-case (str database)
(if database
(case (db-type-default-case (database-underlying-type database))
(:upper (string-upcase str))
(:lower (string-downcase str))
(t str))
;; change default behavior to downcase strings
(string-downcase str)))
;; ------------------------------------------------------------
;; rest of my code...
"crowbent" <········@gmail.com> writes:
> I have an existing mysql database table that has an uppercase database
> name (ex. DATABASE) and lowercase table and column names (ex. table,
> col1, col2, etc...). I didn't design the database so don't yell at me!
> :-) I want to run some statistics on the data and generate a report
> (HTML is fine) and I want to use clsql to access the data.
Oftentimes, mysql doesn't actually care about the case of names either....
> So I created a 'view class' (not sure I need this...) that mirrors the
> existing mysql table. clsql connects fine to the database but defaults
> all strings to uppercase. I looked through the code a little and saw
> that it seems to do this for all databases except postgres. Since its
> not my database I'm stuck with mysql.
Without taking the time to really look into this, perhaps using
lowercase names in the class definition will be sufficient. The
simplest way to do this is to put vertical bars around the symbol names:
|alowercasename|
--
Thomas A. Russ, USC/Information Sciences Institute
Hi Thomas,
Thanks for replying!
> Oftentimes, mysql doesn't actually care about the case of names either....
In this case mysql *is* caring about the case and complains that the
table 'TABLE' does not exist when I'm trying to connect to the table
'table'. Is there a way to change the way mysql reads in the case?
> Without taking the time to really look into this, perhaps using
> lowercase names in the class definition will be sufficient. The
> simplest way to do this is to put vertical bars around the symbol names:
>
> |alowercasename|
Tried this and it doesn't work. I think the functions that I re-wrote
in my previous post are actively converting all symbols and strings to
uppercase by default. When I override them with my definitions then I
can get it to work but it seems like an ugly hack.
Is there any other way to get clsql to use lower case for mysql? Or is
there a cleaner way to do what I've done in my original post?
Thanks,
Greg
"crowbent" <········@gmail.com> writes:
> Hi Thomas,
>
> Thanks for replying!
>
>> Oftentimes, mysql doesn't actually care about the case of names either....
>
> In this case mysql *is* caring about the case and complains that the
> table 'TABLE' does not exist when I'm trying to connect to the table
> 'table'. Is there a way to change the way mysql reads in the case?
Does
(def-view-class table ()
(...slots...)
(:base-table |table|))
work?
>> Without taking the time to really look into this, perhaps using
>> lowercase names in the class definition will be sufficient. The
>> simplest way to do this is to put vertical bars around the symbol names:
>>
>> |alowercasename|
>
> Tried this and it doesn't work. I think the functions that I re-wrote
> in my previous post are actively converting all symbols and strings to
> uppercase by default. When I override them with my definitions then I
> can get it to work but it seems like an ugly hack.
>
> Is there any other way to get clsql to use lower case for mysql? Or is
> there a cleaner way to do what I've done in my original post?
>
> Thanks,
> Greg
>
If it's possible, could you post the code for your DEF-VIEW-CLASS?
--
This is a song that took me ten years to live and two years to write.
- Bob Dylan
Hi Bill,
Thanks for the reply, any help is appreciated. Here is a simple case:
-- Begin SQL code --------------------------------------------------
create database DBTEST;
use DBTEST;
create table tabletest (
columntest int(11) not null auto_increment,
text varchar(240) default null,
primary key (columntest)
) type=MyISAM;
insert into tabletest (text) values ('test 1');
insert into tabletest (text) values ('test 2');
insert into tabletest (text) values ('test 3');
-- End SQL code--------------------------------------------------
And the lisp code:
;; BEGIN --------------------------------------------------
(in-package #:dbtest)
(defvar *database-type* :mysql)
(defvar *database-name* "DBTEST") ;; this mirrors my uppercase DB name
(defvar *database-user* "user")
(defvar *database-server* "localhost")
(defvar *database-password* "password")
(defun dbtest-connect ()
(clsql:connect `(,*database-server*
,*database-name*
,*database-user*
,*database-password*)
:database-type *database-type*))
(defun dbtest-disconnect ()
(clsql:disconnect))
;; this mirrors the table I want to read from.
(def-view-class tabletest ()
((columntest
:db-kind :key
:type integer
:initarg :columntest)
(text
:accessor text
:type (string 240)
:initarg :text))
(:base-table tabletest))
(def-view-class tabletest2 ()
((columntest
:db-kind :key
:type integer
:initarg :columntest)
(text
:accessor text
:type (string 240)
:initarg :text))
(:base-table |tabletest|))
;; this will read from 'TABLETEST' and therefore fail
(select 'tabletest)
;; ==>
;; While accessing database #<MYSQL-DATABASE localhost/DBTEST/user OPEN
{A89EC11}>
;; with expression "SELECT TABLETEST.TEXT,TABLETEST.COLUMNTEST FROM
TABLETEST":
;; Error 1146 / Table 'DBTEST.TABLETEST' doesn't exist
;; has occurred.
;; [Condition of type SQL-DATABASE-DATA-ERROR]
;; this will read from 'TABLETEST' and therefore get nil
(select 'tabletest2)
;; ==>
;; While accessing database #<MYSQL-DATABASE localhost/DBTEST/user OPEN
{A89EC11}>
;; with expression "SELECT TABLETEST.TEXT,TABLETEST.COLUMNTEST FROM
TABLETEST":
;; Error 1146 / Table 'DBTEST.TABLETEST' doesn't exist
;; has occurred.
;; [Condition of type SQL-DATABASE-DATA-ERROR]
;; this doesn't work either
(clsql:locally-enable-sql-reader-syntax)
(select [columntest] [text] :from [|tabletest|])
;; ==>
;; While accessing database #<MYSQL-DATABASE localhost/DBTEST/user OPEN
{A89EC11}>
;; with expression "SELECT COLUMNTEST,TEXT FROM TABLETEST":
;; Error 1146 / Table 'DBTEST.TABLETEST' doesn't exist
;; has occurred.
;; [Condition of type SQL-DATABASE-DATA-ERROR]
;;
;; END --------------------------------------------------
Thanks,
Greg
"crowbent" <········@gmail.com> wrote in news:1146876465.268776.303150
@u72g2000cwu.googlegroups.com:
> (select [columntest] [text] :from [|tabletest|])
This creates an expression with the table name in lower case
but it may not be what you want to do:
(select [columntest] [text] :from ["tabletest"])
Hi,
> This creates an expression with the table name in lower case
> but it may not be what you want to do:
>
> (select [columntest] [text] :from ["tabletest"])
This works! :-) And if I modify the columns to ["columntest"] they
work as well. Thanks!
I still can't get the def-view-class to work, but I can just use the
functional approach above or my hack in the original post.
Thanks,
Greg
On Fri, 05 May 2006 09:34:36 -0700, Thomas A. Russ wrote:
> "crowbent" <········@gmail.com> writes:
>
>> I have an existing mysql database table that has an uppercase database
>> name (ex. DATABASE) and lowercase table and column names (ex. table,
>> col1, col2, etc...). I didn't design the database so don't yell at me!
>> :-) I want to run some statistics on the data and generate a report
>> (HTML is fine) and I want to use clsql to access the data.
>
> Oftentimes, mysql doesn't actually care about the case of names either....
>
>> So I created a 'view class' (not sure I need this...) that mirrors the
>> existing mysql table. clsql connects fine to the database but defaults
>> all strings to uppercase. I looked through the code a little and saw
>> that it seems to do this for all databases except postgres. Since its
>> not my database I'm stuck with mysql.
>
> Without taking the time to really look into this, perhaps using
> lowercase names in the class definition will be sufficient. The
> simplest way to do this is to put vertical bars around the symbol names:
May i point out that this isn't really a problem of case ... ?
The original database "designer" must have completely ignored the
existence of reserved words in the SQL standard, "table" being one.
For a brief discussion of the problem have a look at
http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html
HTH Ralf Mattes
> |alowercasename|