RE: Finding out the type expected for a placeholder
Michael A Chase wrote: >>Is there a way I can prepare a statement handle for some SQL like >> >>insert into some_table values (:a, :b) >> >>and then find out what types the database is expecting for the two >>placeholders? I'm using DBD::Oracle. >Placeholders are VARCHAR2 by default. If you know what columns you are >inserting into (and you should), you can use $dbh->column_info() to >find out the column types. Sorry, I gave the wrong example. In fact my query is just a select statement. I'd like to find out whether Oracle is expecting a given bind variable to be a datetime, an integer or whatever. -- Ed Avis <[EMAIL PROTECTED]>
DBI module installation problem on solaris 10
HI I am trying to install DBI and DBD modules on Solaris 10 and getting following error. Please suggest is there any solution for this problem. Perl.xsc && mv Perl.xsc Perl.c cc -c-D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -xarch=v8 -D_TS_ERRNO -xO3 -xspace -xildoff-DVERSION=\"1.48\" -DXS_VERSION=\"1.48\" -KPIC "-I/usr/perl5/5.8.4/lib/sun4-solaris-64int/CORE" -DDBI_NO_THREADS Perl.c sh: cc: not found *** Error code 1 make: Fatal error: Command failed for target `Perl.o' This is perl, v5.8.4 built for sun4-solaris-64int perl -v details # perl -V Summary of my perl5 (revision 5 version 8 subversion 4) configuration: Platform: osname=solaris, osvers=2.10, archname=sun4-solaris-64int uname='sunos localhost 5.10 sun4u sparc SUNW,Ultra-2' config_args='' hint=recommended, useposix=true, d_sigaction=define usethreads=undef use5005threads=undef useithreads=undef usemultiplicity=undef useperlio=define d_sfio=undef uselargefiles=define usesocks=undef use64bitint=define use64bitall=undef uselongdouble=undef usemymalloc=n, bincompat5005=undef Compiler: cc='cc', ccflags ='-D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -xarch=v8 -D_TS_ERRNO', optimize='-xO3 -xspace -xildoff', cppflags='' ccversion='Sun WorkShop', gccversion='', gccosandvers='' intsize=4, longsize=4, ptrsize=4, doublesize=8, byteorder=87654321 d_longlong=define, longlongsize=8, d_longdbl=define, longdblsize=16 ivtype='long long', ivsize=8, nvtype='double', nvsize=8, Off_t='off_t', lseeksize=8 alignbytes=8, prototype=define Linker and Libraries: ld='cc', ldflags ='' libpth=/lib /usr/lib /usr/ccs/lib libs=-lsocket -lnsl -ldl -lm -lc perllibs=-lsocket -lnsl -ldl -lm -lc libc=/lib/libc.so, so=so, useshrplib=true, libperl=libperl.so gnulibc_version='' Dynamic Linking: dlsrc=dl_dlopen.xs, dlext=so, d_dlsymun=undef, ccdlflags='-R /usr/perl5/5.8.4/lib/sun4-solaris-64int/CORE' cccdlflags='-KPIC', lddlflags='-G' /usr/perl5/5.8.4/lib/sun4-solaris-64int /usr/perl5/5.8.4/lib /usr/perl5/site_perl/5.8.4/sun4-solaris-64int /usr/perl5/site_perl/5.8.4 /usr/perl5/site_perl /usr/perl5/vendor_perl/5.8.4/sun4-solaris-64int /usr/perl5/vendor_perl/5.8.4 /usr/perl5/vendor_perl Regards shailesh _ This e-mail may contain confidential information and/or copyright material. This e-mail is intended for the use of the addressee only. Any unauthorised use may be unlawful. If you receive this e-mail by mistake please advise the sender immediately by using the reply facility in your e-mail software. The Friends Provident group of companies includes these Friends Provident plc subsidiary companies: Friends Provident Life and Pensions Limited. Registered number 4096141. Friends Provident Life Assurance Limited. Registered number 782698. Friends Provident Pensions Limited. Registered number 475201. Friends Provident Marketing Limited. Registered number 5059179. Each of these subsidiary companies is authorised and regulated by the Financial Services Authority. The ultimate holding company is Friends Provident plc. Registered number 4113107. All are incorporated companies limited by shares and registered in England. Registered and Head Office: Pixham End, Dorking, Surrey RH4 1QA. www.friendsprovident.com _
Re: DBI module installation problem on solaris 10
On 06/13/2005 05:39 AM, Shailesh Mali said: I am trying to install DBI and DBD modules on Solaris 10 and getting following error. Please suggest is there any solution for this problem. Perl.xsc && mv Perl.xsc Perl.c cc -c-D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -xarch=v8 -D_TS_ERRNO -xO3 -xspace -xildoff-DVERSION=\"1.48\" -DXS_VERSION=\"1.48\" -KPIC "-I/usr/perl5/5.8.4/lib/sun4-solaris-64int/CORE" -DDBI_NO_THREADS Perl.c sh: cc: not found *** Error code 1 make: Fatal error: Command failed for target `Perl.o' What's unclear about the message? You haven't installed the compiler that was used to build perl. You can either install that compiler or build your own perl with a compiler you have and then use it to build modules too. This is perl, v5.8.4 built for sun4-solaris-64int perl -v details # perl -V Summary of my perl5 (revision 5 version 8 subversion 4) configuration: Compiler: cc='cc', ccflags ='-D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -xarch=v8 -D_TS_ERRNO', ccversion='Sun WorkShop', gccversion='', gccosandvers='' -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
Re: Finding out the type expected for a placeholder
On 06/13/2005 02:56 AM, Avis, Ed said: Michael A Chase wrote: Placeholders are VARCHAR2 by default. If you know what columns you are inserting into (and you should), you can use $dbh->column_info() to find out the column types. Sorry, I gave the wrong example. In fact my query is just a select statement. I'd like to find out whether Oracle is expecting a given bind variable to be a datetime, an integer or whatever. The original example may have been wrong, but the advice still stands. You can control the type of each placeholder. To decide what type you want it to be, you have to know what column it is interacting with. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
RE: Finding out the type expected for a placeholder
Michael A Chase wrote: >>I'd like to find out whether Oracle is expecting a given bind >>variable to be a datetime, an integer or whatever. > >The original example may have been wrong, but the advice still stands. >You can control the type of each placeholder. No; because I don't know in advance what the query will be. The user is entering the SQL to use, with bind variables, and I would like to prepare the statement handle and find out from the database what the expected type of each bind variable will be. -- Ed Avis <[EMAIL PROTECTED]>
Re: Finding out the type expected for a placeholder
On 06/13/2005 06:28 AM, Avis, Ed said: Michael A Chase wrote: >> Ed said: I'd like to find out whether Oracle is expecting a given bind variable to be a datetime, an integer or whatever. The original example may have been wrong, but the advice still stands. You can control the type of each placeholder. No; because I don't know in advance what the query will be. The user is entering the SQL to use, with bind variables, and I would like to prepare the statement handle and find out from the database what the expected type of each bind variable will be. That's showing an awful lot of trust in the users' good intentions and skill. All placeholders are VARCHAR unless the type is explicitly given in the bind_param*() call. If any values provided by the user have to be treated differently, they'll have to tell you by some method unless you parse the SQL to figure it out. I think SQL::Statement might be able to help, but it is likely to take a lot of work to get right since many cases are likely to be ambiguous. -- Mac :}) ** I usually forward private questions to the appropriate mail list. ** Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html Give a hobbit a fish and he eats fish for a day. Give a hobbit a ring and he eats fish for an age.
RE: Finding out the type expected for a placeholder
Quoting "Avis, Ed" <[EMAIL PROTECTED]>: > Michael A Chase wrote: > >> All placeholders are VARCHAR unless the type is explicitly given in the >> bind_param*() call. > > I'm not quite sure what you mean. It is quite possible for a > placeholder to be of DATETIME type, for example. Perl passes the > date as a string but nonetheless it has to be a date. I should have been more explicit. All DBI placeholders are VARCHAR unless explicitly defined differently in bind_param*(). This is not different from Oracle bind variables except that there is no default type for Oracle bind variables, you always have to define what type the Oracle bind variable will be before you use it. -- Mac :})
RE: Finding out the type expected for a placeholder
Michael A Chase wrote: >All placeholders are VARCHAR unless the type is explicitly given in the >bind_param*() call. I'm not quite sure what you mean. It is quite possible for a placeholder to be of DATETIME type, for example. Perl passes the date as a string but nonetheless it has to be a date. >If any values provided by the user have to be >treated differently, they'll have to tell you by some method unless you >parse the SQL to figure it out. I think SQL::Statement might >be able to help, but it is likely to take a lot of work to get right >since many cases are likely to be ambiguous. Yes - it would be very flaky to try and parse the SQL on the client side and work out the data types. The only reliable way to do it is to ask the database server what it is expecting. I wonder what Pro*C and other tools do when they compile some SQL with placeholders. Surely Pro*C is checking at compile time that the placeholder type matches the declared type in the source file. -- Ed Avis <[EMAIL PROTECTED]>
Re: Finding out the type expected for a placeholder
On Mon, Jun 13, 2005 at 02:28:41PM +0100, Avis, Ed wrote: > Michael A Chase wrote: > > >>I'd like to find out whether Oracle is expecting a given bind > >>variable to be a datetime, an integer or whatever. > > > >The original example may have been wrong, but the advice still stands. > >You can control the type of each placeholder. > > No; because I don't know in advance what the query will be. The user is > entering the SQL to use, with bind variables, and I would like to > prepare the statement handle and find out from the database what the > expected type of each bind variable will be. Oracle, like most databases, offers no natural way to do this. Tim.
RE: Finding out the type expected for a placeholder
> On Mon, Jun 13, 2005 at 02:28:41PM +0100, Avis, Ed wrote: > > Michael A Chase wrote: > > > > >>I'd like to find out whether Oracle is expecting a given bind > > >>variable to be a datetime, an integer or whatever. > > > > > >The original example may have been wrong, but the advice > still stands. > > >You can control the type of each placeholder. > > > > No; because I don't know in advance what the query will be. > The user is > > entering the SQL to use, with bind variables, and I would like to > > prepare the statement handle and find out from the database what the > > expected type of each bind variable will be. > > Oracle, like most databases, offers no natural way to do this. > You would have to query the database object tables prior to executing your bind executes. This will give you the information on what each column type is so you can predefine them.
RE: Finding out the type expected for a placeholder
From: Jones Robert TTMS Contractor [mailto:[EMAIL PROTECTED]
Sent: Monday, June 13, 2005 12:17 PM
To: dbi-users@perl.org
Subject: RE: Finding out the type expected for a placeholder
> On Mon, Jun 13, 2005 at 02:28:41PM +0100, Avis, Ed wrote:
> > Michael A Chase wrote:
> >
> > >>I'd like to find out whether Oracle is expecting a given bind
> > >>variable to be a datetime, an integer or whatever.
> > >
> > >The original example may have been wrong, but the advice
> still stands.
> > >You can control the type of each placeholder.
> >
> > No; because I don't know in advance what the query will be.
> The user is
> > entering the SQL to use, with bind variables, and I would like to
> > prepare the statement handle and find out from the database what the
> > expected type of each bind variable will be.
>
> Oracle, like most databases, offers no natural way to do this.
>
You would have to query the database object tables prior to executing
your bind executes. This will give you the information on what each column
type is so you can predefine them.
... jwm wrote
"IF" this is the way you choose to build your update/insert statements
please be aware that "date" formats vary by session... So you would need to
either do a to_date function and different mask for each date you handle or
set the session date format {my $sth = $dbh->prepare(q{alter session set
NLS_DATE_FORMAT = 'dd-Mon-'}); ...} AND convert each date to that
format... As for knowing the "type" of column for an insert/update I have
not had any problems (or need), with Oracle, to know the "type" of the
column when using placeholders EXCEPT for date types... SO I'm not sure
where your problem is unless it is dates... I use Oracle versions 7.x to
9.x... (There are "types" that I have NOT used - "glob" for one)
storing hierarchical values in db; methods
Hi all,
I have question about storing hierarchical values in db. I found some
methods, pick up the first easiest, but I ahve question about that -
have someone coded some methods, or knows some module, which helps me
with this issue ?
Ok, here is infos:
MYSQL tables:
CREATE TABLE `cat_name` (
`id` int(11) unsigned NOT NULL auto_increment,
`cat_name` varchar(150) NOT NULL default '',
PRIMARY KEY (`id`),
UNIQUE KEY `cat_name` (`cat_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
CREATE TABLE `cat_tree` (
`id` int(9) unsigned NOT NULL auto_increment,
`parent_id` int(11) unsigned NOT NULL default '0',
`child_id` int(11) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `parent_id` (`parent_id`),
KEY `child_id` (`child_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
input data:
name = 'some name...'
category = 'foo/bar/foo/bar'
name = 'something other...'
category = 'foo/bar/foo/bar'
Perl snippet:
foreach my $cat ( split '/', $hr->{category} ) {
next if $hash{$cat}++; #skip duplicates
$hr->{cat_id} = cat_get($cat) || cat_insert($cat);
print $hr->{cat_id}, "\n";
#need other functions to store/get (recursive) values into cat_tree
}
sub cat_get {
my ($id) = $dbh->selectrow_array("select id from cat_name where
cat_name = ?", {}, shift);
return ($id);
}
sub cat_insert {
my $cat_insert = $dbh->prepare("INSERT INTO cat_name (cat_name) values
(?)");
$cat_insert->execute( shift );
return $dbh->{'mysql_insertid'};
}
I don't know if this problem is solved like my "code", but it it my
first try. Could anyone helps on this, please ?
thanks.
Re: storing hierarchical values in db; methods
Looks like the stuff that Joe Celko describes in his book on processing
trees in SQL.
I'm not sure whether there's anything already written using DBI to help you.
On 6/13/05, Ing. Branislav Gerzo <[EMAIL PROTECTED]> wrote:
> I have question about storing hierarchical values in db. I found some
> methods, pick up the first easiest, but I ahve question about that -
> have someone coded some methods, or knows some module, which helps me
> with this issue ?
>
> Ok, here is infos:
>
> MYSQL tables:
> CREATE TABLE `cat_name` (
> `id` int(11) unsigned NOT NULL auto_increment,
> `cat_name` varchar(150) NOT NULL default '',
> PRIMARY KEY (`id`),
> UNIQUE KEY `cat_name` (`cat_name`)
> ) ENGINE=MyISAM DEFAULT CHARSET=utf8
>
> CREATE TABLE `cat_tree` (
> `id` int(9) unsigned NOT NULL auto_increment,
> `parent_id` int(11) unsigned NOT NULL default '0',
> `child_id` int(11) unsigned NOT NULL default '0',
> PRIMARY KEY (`id`),
> KEY `parent_id` (`parent_id`),
> KEY `child_id` (`child_id`)
> ) ENGINE=MyISAM DEFAULT CHARSET=utf8
>
> input data:
>
> name = 'some name...'
> category = 'foo/bar/foo/bar'
> name = 'something other...'
> category = 'foo/bar/foo/bar'
>
> Perl snippet:
>
> foreach my $cat ( split '/', $hr->{category} ) {
> next if $hash{$cat}++; #skip duplicates
> $hr->{cat_id} = cat_get($cat) || cat_insert($cat);
> print $hr->{cat_id}, "\n";
> #need other functions to store/get (recursive) values into cat_tree
> }
>
> sub cat_get {
> my ($id) = $dbh->selectrow_array("select id from cat_name where cat_name =
> ?", {}, shift);
> return ($id);
> }
>
> sub cat_insert {
> my $cat_insert = $dbh->prepare("INSERT INTO cat_name (cat_name) values
> (?)");
> $cat_insert->execute( shift );
> return $dbh->{'mysql_insertid'};
> }
>
> I don't know if this problem is solved like my "code", but it it my
> first try. Could anyone helps on this, please ?
>
> thanks.
>
>
--
Jonathan Leffler <[EMAIL PROTECTED]> #include
Guardian of DBD::Informix - v2005.01 - http://dbi.perl.org
"I don't suffer from insanity - I enjoy every minute of it."
Re: storing hierarchical values in db; methods
I have done this in a production environment.
THIS IS NOT TO BE UNDERTAKING BY THE FAINT OF HEART.
Caveats:
I have used multi parented relationships. In a static environment this
is not a big issue. In an environment where constant reparenting occurs,
along with the attendant woes of widow and orphaned hierarchies, use
dynamic sql and a cursoring technique and in terms of the table just
store the parent key and the child key.
In the years since I first did this, CPU and disk speeds have more than
made up for the cost of constructing the hierarchy on the fly.
There are a number of solutions to this problem. Attached are theory
behind how I implemented an idea that is quite similar to yours. While
it was a very interesting problem and the solution is rather elegant,
maintenance can become a nightmare. Take it from someone who implemented
this for a table of about a million parent->child relationships. If you
are interested in a full blown example, please contact me directly.
Your Friendly Neighborhood DBA,
Chuck
[EMAIL PROTECTED] wrote:
Hi all,
I have question about storing hierarchical values in db. I found some
methods, pick up the first easiest, but I ahve question about that -
have someone coded some methods, or knows some module, which helps me
with this issue ?
Ok, here is infos:
MYSQL tables:
CREATE TABLE `cat_name` (
`id` int(11) unsigned NOT NULL auto_increment,
`cat_name` varchar(150) NOT NULL default '',
PRIMARY KEY (`id`),
UNIQUE KEY `cat_name` (`cat_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
CREATE TABLE `cat_tree` (
`id` int(9) unsigned NOT NULL auto_increment,
`parent_id` int(11) unsigned NOT NULL default '0',
`child_id` int(11) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `parent_id` (`parent_id`),
KEY `child_id` (`child_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
input data:
name = 'some name...'
category = 'foo/bar/foo/bar'
name = 'something other...'
category = 'foo/bar/foo/bar'
Perl snippet:
foreach my $cat ( split '/', $hr->{category} ) {
next if $hash{$cat}++; #skip duplicates
$hr->{cat_id} = cat_get($cat) || cat_insert($cat);
print $hr->{cat_id}, "\n";
#need other functions to store/get (recursive) values into cat_tree
}
sub cat_get {
my ($id) = $dbh->selectrow_array("select id from cat_name where cat_name =
?", {}, shift);
return ($id);
}
sub cat_insert {
my $cat_insert = $dbh->prepare("INSERT INTO cat_name (cat_name) values
(?)");
$cat_insert->execute( shift );
return $dbh->{'mysql_insertid'};
}
I don't know if this problem is solved like my "code", but it it my
first try. Could anyone helps on this, please ?
thanks.
From: [EMAIL PROTECTED] (Scott Gray)
Date: 3 Jun 1996 08:02:07 -0400
Subject: Re: recursive to tree conversion?
Message-ID: <[EMAIL PROTECTED]>
Organization: VoiceNet Internet Access
In article <[EMAIL PROTECTED]>,
Scott Gray <[EMAIL PROTECTED]> wrote:
>There are some *very* nice algorithms for representing hierarchical
>structures in a relational database using only base SQL92 features
>(without cursors or recursion), which I have had great success with
>in the past. I would recommend reading "SQL for Smarties" for
>some examples.
>
>If there is sufficient interest, I can provide some examples.
Ok, I threw the following together, so I'm not promising that it is
entirely correct, but I hope that y'all find it useful. It is
written with a heavy slant towards Sybase, for obvious reasons, but
could easily be ported to other platforms.
I know that I said this didn't involve cursors, but this example
minimally involves them, and it should be relatively easy to
implement it without cursor.
-scott
[ snip ]--
Alright, so you wanna know more about representing hierarchies
in a relational database? Before I get in to the nitty gritty
I should at least give all of the credit for this algorithm
to: "_Hierarical_Structures:_The_Relational_Taboo!_, _(Can_
Transitive_Closure_Queries_be_Efficient?)_", by Michael J. Kamfonas
as published in 1992 "Relational Journal" (I don't know which
volume or issue).
The basic algorithm goes like this, given a tree (hierarchy) that
looks roughly like this (forgive the ASCII art--I hope you are using
a fixed font to view this):
a
/ \
/ \
/ \
b c
/ \ /|\
/ \/ | \
/ \ /| \
d e f gh
(note, that the tree need not be balanced for this algorithm to work).
The next step assigned two numbers to each node in the tree, called left
and right numbers, such that the left and right numbers of each node
contain the left and right numb
Re: storing hierarchical values in db; methods
if you have the pleasure to work with Oracle, it is all taken care of for you..
"connect by" takes care of everything for you. no coding necessary.
In 9i you can
o order the hierarchy in a connect by
o join a connect by
o get the "connect by path"
It is not available before.
Here is an example from 9i
[EMAIL PROTECTED]> create or replace view v
2 as
3 select emp.ename, emp.empno, emp.mgr, dept.dname from emp, dept
where emp.deptno = dept.deptno
4 /
View created.
[EMAIL PROTECTED]> column EmpName format a30
[EMAIL PROTECTED]> select rpad('*',2*level,'*')||ename EmpName, dname
2from v connect by a join...
3 start with mgr is null
4 connect by prior empno = mgr
5 /
EMPNAMEDNAME
-- --
**KING ACCOUNTING
CLARK ACCOUNTING
**MILLER ACCOUNTING
JONES RESEARCH
**FORD RESEARCH
SMITH RESEARCH
**SCOTTRESEARCH
ADAMS RESEARCH
BLAKE SALES
**ALLENSALES
**JAMESSALES
**WARD SALES
**TURNER SALES
**MARTIN SALES
14 rows selected.
[EMAIL PROTECTED]>
[EMAIL PROTECTED]>
[EMAIL PROTECTED]>
[EMAIL PROTECTED]> select rpad('*',2*level,'*')||ename EmpName, dname
2from v
3 start with mgr is null
4 connect by prior empno = mgr
5 order SIBLINGS by ename Order the hierarchy
6 /
EMPNAMEDNAME
-- --
**KING ACCOUNTING
BLAKE SALES
**ALLENSALES
**JAMESSALES
**MARTIN SALES
**TURNER SALES
**WARD SALES
CLARK ACCOUNTING
**MILLER ACCOUNTING
JONES RESEARCH
**FORD RESEARCH
SMITH RESEARCH
**SCOTTRESEARCH
ADAMS RESEARCH
14 rows selected.
[EMAIL PROTECTED]>
[EMAIL PROTECTED]> column cbp format a30
[EMAIL PROTECTED]>
[EMAIL PROTECTED]> select rpad('*',2*level,'*')||ename EmpName, dname,
2 sys_connect_by_path( ename, '/' ) cbp
3from v
4 start with mgr is null
5 connect by prior empno = mgr
6 order SIBLINGS by ename
7 /
EMPNAMEDNAME CBP
-- -- --
**KING ACCOUNTING /KING
BLAKE SALES /KING/BLAKE
**ALLENSALES /KING/BLAKE/ALLEN
**JAMESSALES /KING/BLAKE/JAMES
**MARTIN SALES /KING/BLAKE/MARTIN
**TURNER SALES /KING/BLAKE/TURNER
**WARD SALES /KING/BLAKE/WARD
CLARK ACCOUNTING /KING/CLARK
**MILLER ACCOUNTING /KING/CLARK/MILLER
JONES RESEARCH /KING/JONES
**FORD RESEARCH /KING/JONES/FORD
SMITH RESEARCH /KING/JONES/FORD/SMITH
**SCOTTRESEARCH /KING/JONES/SCOTT
ADAMS RESEARCH /KING/JONES/SCOTT/ADAMS
14 rows selected.
Chuck Fox <[EMAIL PROTECTED]> wrote:
I have done this in a production environment.
THIS IS NOT TO BE UNDERTAKING BY THE FAINT OF HEART.
Caveats:
I have used multi parented relationships. In a static environment this
is not a big issue. In an environment where constant reparenting occurs,
along with the attendant woes of widow and orphaned hierarchies, use
dynamic sql and a cursoring technique and in terms of the table just
store the parent key and the child key.
In the years since I first did this, CPU and disk speeds have more than
made up for the cost of constructing the hierarchy on the fly.
There are a number of solutions to this problem. Attached are theory
behind how I implemented an idea that is quite similar to yours. While
it was a very interesting problem and the solution is rather elegant,
maintenance can become a nightmare. Take it from someone who implemented
this for a table of about a million parent->child relationships. If you
are interested in a full blown example, please contact me directly.
Your Friendly Neighborhood DBA,
Chuck
[EMAIL PROTECTED] wrote:
>Hi all,
>
>I have question about storing hierarchical values in db. I found some
>methods, pick up the first easiest, but I ahve question about that -
>have someone coded some methods, or knows some module, which helps me
>with
