[Zope] Refer to sequence-index within SQLmethod?

[Jim Sanford]

You must remember that the SQL statement gets evaluated once by zope and then passed to the backend database. Any "calculations"
done must use operators and "variables" the backend database understands. If the backend database is a xbase derivative (like Visual
FoxPro which I use) that uses the record number concept, the following should work:

update user_login_table set unique_row_id = str(recno(),9,0)

Lacking a backend that has record numbers or one that automatically assigns a unique id, the following should work. It is untested
and I don't know how long it would take:

Create a new, empty table called user_login_table2 with the exact same structure as user_login_table

Create an SQL method called SQLoop:
           select * from user_login_table

Next create SQL Method like this

<dtml-var in SQLoop>
insert into user_login_table2 (unique_row_id, field2, field3, ...)
values (<dtml-sqlvar"_.string.zfill(_.str(_.int('sequence-index')),9)" type=nb>,
<dtml-sqlvar field2 type=whatever>,
<dtml-sqlvar field3 type=whatever>,
...)
</dtml-in>

******replace whatever with the appropriate type**********

This creates a new table that is identical to your old table  except for the unique_row_id field.

Then rename the tables and put code in your "add method" to create unique ids when records are added.

I use a SQL method called NextID that looks like this:
   select max(unique_id)+1  as nextid from online
yours would need to be:
   select max(val(unique_id))+1  as nextid from online

I would recomend ints or longints instead of strings for unique ids. Their indexes tend to be smaller and faster on many SQL
backends
 __________________________________________________________________

              Jim Sanford
      .       Database/Web Engineer
     / \  /   Accelerated Technology, Inc.
    /   /     720 Oak Circle Drive East
   /  /  \    Mobile, AL 36609
  / /     \   Voice: 334-661-5770  fax: 334-661-5788
 /         \  E-Mail: jsanford@atinucleus.com
              Web: http://www.atinucleus.com

 Source Code, No Royalties, Any CPU...It just make sense !
 __________________________________________________________________

----- Original Message -----
From: <Jerry.Spicklemire@IFLYATA.COM>
To: <zope@zope.org>
Sent: Friday, January 14, 2000 4:18 PM
Subject: [Zope] Refer to sequence-index within SQLmethod?


Hi again,

Perhaps if I get in the habit of adding more detail in the first message, a
higher number of Zenful types will feel they have enough information to
hazard a guess.

Better late than never, so here's a sample of the code in the SQL Query
Template Edit Box :

update user_login_table set unique_row_id = <dtml-sqlvar
"_.string.zfill(_.str(_.int('sequence-index')),9)" type=nb>

and, here are even more ways to try adding a "sequence-item" argument into
the ZSQLmethod Edit Window that don't work :

_.getitem(sequence-index)
,

"_.getitem(sequence-index)"
,

expr=_.getitem(sequence-index)
,

expr="_.getitem(sequence-index)"
,

and,

"expr=_.getitem(sequence-index)

BTW, the question is bound to come up, as in, "Why in the name of Zope do
you need to do this?"

Wouldn't the world would be a lovely place if every table that any Desktop
Database user had ever misconceived included a Record ID? Alas no. The
shameful truth is, all I'm really trying to do is stuff a string that's
predictably exclusive into each row, so that I can comfortably fall back
into my bad old habits from dBase days, and easily retrieve single records
for processing . . .

Thanks for any clues!
Jerry S.

_______________________________________________
Zope maillist  -  Zope@zope.org
http://lists.zope.org/mailman/listinfo/zope
**   No cross posts or HTML encoding!  **
(Related lists -
 http://lists.zope.org/mailman/listinfo/zope-announce
 http://lists.zope.org/mailman/listinfo/zope-dev )