haskelldb-users

[Haskelldb-users] Getting a reference back to newly inserted record

[R. E. B. <re...@cs...>]

Hi all,

I'm developing a HaskellDB application using a console interface (with
HCL library). At some points, I have menus represented to the user. User
either selects an item from the menu (then I return the value for that
item) or add a new item using the "Add" option. When user adds the new
item, I need to be able to get a referece back to the newly inserted
item to use the unique id of that item throughout other parts of my
program.

Is there a way to get back the value of the latest insertion?

-- 
R. Emre Başar
İstanbul Bilgi University
Department of Computer Science

Re: [Haskelldb-users] Getting a reference back to newly inserted record

[Justin B. <jgb...@gm...>]

If you look at your specific database and see how to get the primary
key back out, maybe you can run those statements within a transaction
and get the result back (HaskellDB.Database.transaction). You may have
more luck looking at the underlying driver (e.g., HDBC).

2009/11/14 R. Emre Başar <re...@cs...>:
> Hi all,
>
> I'm developing a HaskellDB application using a console interface (with
> HCL library). At some points, I have menus represented to the user. User
> either selects an item from the menu (then I return the value for that
> item) or add a new item using the "Add" option. When user adds the new
> item, I need to be able to get a referece back to the newly inserted
> item to use the unique id of that item throughout other parts of my
> program.
>
> Is there a way to get back the value of the latest insertion?
>
> --
> R. Emre Başar
> İstanbul Bilgi University
> Department of Computer Science
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.9 (GNU/Linux)
>
> iD8DBQFK/uIl86wLPNcWbrwRAlTyAJsECVEteNeXeO/8oJyqTc0ZLUzHkACfVMY5
> ceHUhuU8h9/ULo5L4ZXM0DA=
> =KVdS
> -----END PGP SIGNATURE-----
>
> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
> trial. Simplify your report design, integration and deployment - and focus on
> what you do best, core application coding. Discover what's new with
> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
> _______________________________________________
> Haskelldb-users mailing list
> Has...@li...
> https://lists.sourceforge.net/lists/listinfo/haskelldb-users
>
>

Re: [Haskelldb-users] Getting a reference back to newly inserted record

[R. E. B. <re...@cs...>]

I'm using MySQL with HSQL. MySQL supports this by executing a "SELECT
LAST_INSERT_ID()" query. But AFAIK, it is not possible to execute
arbitrary SQL in HaskellDB. I can do that if I can access the underlying
Connection object of HSQL.

Is there a standard method for accomplishing that.

Or, to take an alternative approach, is it possible to define that type
of query as a HaskellDB table?

Justin Bailey der ki:
> If you look at your specific database and see how to get the primary
> key back out, maybe you can run those statements within a transaction
> and get the result back (HaskellDB.Database.transaction). You may have
> more luck looking at the underlying driver (e.g., HDBC).
> 
> 2009/11/14 R. Emre Başar <re...@cs...>:
> > Hi all,
> >
> > I'm developing a HaskellDB application using a console interface (with
> > HCL library). At some points, I have menus represented to the user. User
> > either selects an item from the menu (then I return the value for that
> > item) or add a new item using the "Add" option. When user adds the new
> > item, I need to be able to get a referece back to the newly inserted
> > item to use the unique id of that item throughout other parts of my
> > program.
> >
> > Is there a way to get back the value of the latest insertion?
> >
> > --
> > R. Emre Başar
> > İstanbul Bilgi University
> > Department of Computer Science
> >

-- 
R. Emre Başar
İstanbul Bilgi University
Department of Computer Science

Re: [Haskelldb-users] Getting a reference back to newly inserted record

[Justin B. <jgb...@gm...>]

On Sat, Nov 14, 2009 at 11:55 AM, R. Emre Başar <re...@cs...> wrote:
> Or, to take an alternative approach, is it possible to define that type
> of query as a HaskellDB table?
>

Probably that is your best approach. It will be tricky. The
haskelldb-th project might help - it has some utilities for defining
tables, etc.

> Justin Bailey der ki:
>> If you look at your specific database and see how to get the primary
>> key back out, maybe you can run those statements within a transaction
>> and get the result back (HaskellDB.Database.transaction). You may have
>> more luck looking at the underlying driver (e.g., HDBC).
>>
>> 2009/11/14 R. Emre Başar <re...@cs...>:
>> > Hi all,
>> >
>> > I'm developing a HaskellDB application using a console interface (with
>> > HCL library). At some points, I have menus represented to the user. User
>> > either selects an item from the menu (then I return the value for that
>> > item) or add a new item using the "Add" option. When user adds the new
>> > item, I need to be able to get a referece back to the newly inserted
>> > item to use the unique id of that item throughout other parts of my
>> > program.
>> >
>> > Is there a way to get back the value of the latest insertion?
>> >
>> > --
>> > R. Emre Başar
>> > İstanbul Bilgi University
>> > Department of Computer Science
>> >
>
> --
> R. Emre Başar
> İstanbul Bilgi University
> Department of Computer Science
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.9 (GNU/Linux)
>
> iD8DBQFK/wsq86wLPNcWbrwRAoxRAJ9BC7Fq1zW3uEfjUpS/64Lz3dQMRACeMV5h
> RxcOpYwmpuaDdDyu7ui3IP4=
> =BMdT
> -----END PGP SIGNATURE-----
>
>

Re: [Haskelldb-users] Getting a reference back to newly inserted record

[R. E. B. <re...@cs...>]

Justin Bailey der ki:
> On Sat, Nov 14, 2009 at 11:55 AM, R. Emre Başar <re...@cs...> wrote:
> > Or, to take an alternative approach, is it possible to define that type
> > of query as a HaskellDB table?
> >
> 
> Probably that is your best approach. It will be tricky. The
> haskelldb-th project might help - it has some utilities for defining
> tables, etc.
> 

Thanks. I managed to get it to some level. I defined a table like:

$(mkDBDirectTableWithName "LastInsertId" "Server" [("LAST_INSERT_ID()", [t|Int|])])

This gives me the last insert id for a table, but the problem is it
generates an SQL like: SELECT LAST_INSERT_ID() FROM Server as T1; which
is table specific.

Is it possible to remove the FROM clause, so that it can work on any
insert? As far as I dived into the source of HaskellDB I got the
impression that it is not possible to build queries without a FROM
component. Is that correct?

-- 
R. Emre Başar
İstanbul Bilgi University
Department of Computer Science

Re: [Haskelldb-users] Getting a reference back to newly inserted record

[Justin B. <jgb...@gm...>]

On Sat, Nov 14, 2009 at 1:35 PM, R. Emre Başar <re...@cs...> wrote:
> Thanks. I managed to get it to some level. I defined a table like:
>
> $(mkDBDirectTableWithName "LastInsertId" "Server" [("LAST_INSERT_ID()", [t|Int|])])
>

Nice work! Unfortunately you are right - you have to give a table
name. If the table name is irrelevant (i.e., it doesn't affect
LAST_INSERT_ID), try using information_schema.schema_catalog_name.
That's a SQL-standard table that always has one row. It's useful for a
"fake" query like this.

If you are feeling adventerous, the darcs version of haskelldb has the
"func" and "literal" query operators which would let you build this
query fairly easily:

  lastInsertID = func "last_insert_id()"

or

  lastInsertID = constant (literal "last_insert_id()")

but it is has undergone some major changes in syntax. The underlying
Record system was shifted over to HList and the library is still very
much in flux. In any case you can find it at

  http://code.haskell.org/haskelldb


> This gives me the last insert id for a table, but the problem is it
> generates an SQL like: SELECT LAST_INSERT_ID() FROM Server as T1; which
> is table specific.
>
> Is it possible to remove the FROM clause, so that it can work on any
> insert? As far as I dived into the source of HaskellDB I got the
> impression that it is not possible to build queries without a FROM
> component. Is that correct?
>
> --
> R. Emre Başar
> İstanbul Bilgi University
> Department of Computer Science
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.9 (GNU/Linux)
>
> iD8DBQFK/yKF86wLPNcWbrwRAvOVAJ9gecd4U8InxYBX0L+wfMsLWLZLugCghY6p
> 5tAS994UAjwwtyNyoGwLkkU=
> =e4HE
> -----END PGP SIGNATURE-----
>
>

Re: [Haskelldb-users] Getting a reference back to newly inserted record

[R. E. B. <re...@cs...>]

Justin Bailey said:
> On Sat, Nov 14, 2009 at 1:35 PM, R. Emre Başar <re...@cs...>
> wrote:
> > Thanks. I managed to get it to some level. I defined a table like:
> >
> > $(mkDBDirectTableWithName "LastInsertId" "Server" [("LAST_INSERT_ID()", [t|Int|])])
> >
> 
> Nice work! Unfortunately you are right - you have to give a table
> name. If the table name is irrelevant (i.e., it doesn't affect
> LAST_INSERT_ID), try using information_schema.schema_catalog_name.
> That's a SQL-standard table that always has one row. It's useful for a
> "fake" query like this.
> 

Unfortunately, the table name is relevant. If there is no table name,
you can have an action which always produces the value of the last
insert, if you use it with a table name you get the value of the last
insert for a table. (this also changes the type from Int to Maybe Int
since the table might not have any insertions but that's another story)

It looks that the only solution is to create a last insert table for
each table that I will insert.

> If you are feeling adventerous, the darcs version of haskelldb has the
> "func" and "literal" query operators which would let you build this
> query fairly easily:
> 
>   lastInsertID = func "last_insert_id()"
> 
> or
> 
>   lastInsertID = constant (literal "last_insert_id()")
> 
> but it is has undergone some major changes in syntax. The underlying
> Record system was shifted over to HList and the library is still very
> much in flux. In any case you can find it at
> 
>   http://code.haskell.org/haskelldb
> 

I will take a look at it. Since I have a deadline for that project, I
cannot be too adventureous, but I will test the new version.

Thanks.

-- 
R. Emre Başar
İstanbul Bilgi University
Department of Computer Science