sql

sql

[Hans Hagen]

Hi,

As part of context-on-demand support i'm looking into / playing with 
interfacing to (my)sql (from xfdf to start with). I wonder if it is 
possible to use patterns for field names? Say that i have v_1 ... v_20, is 
it possible to select v_5..v_10?

Hans
-------------------------------------------------------------------------
                                   Hans Hagen | PRAGMA ADE | pragma@wxs.nl
                       Ridderstraat 27 | 8061 GH Hasselt | The Netherlands
  tel: +31 (0)38 477 53 69 | fax: +31 (0)38 477 53 74 | www.pragma-ade.com
-------------------------------------------------------------------------
                                   fall-back web server: 
www.pragma-ade.nl
-------------------------------------------------------------------------

Re: sql

[Berend de Boer]

Hans Hagen <pragma@wxs.nl> writes:

> As part of context-on-demand support i'm looking into / playing with
> interfacing to (my)sql (from xfdf to start with). I wonder if it is
> possible to use patterns for field names? Say that i have v_1
> ... v_20, is it possible to select v_5..v_10?

I suggest a course data base design would be useful here :-)

Nope, this is not possible and for very good reasons. Array constructs
are not done with additional fields, but with another table.

I would also suggest using Firebird (InterBase) or PostgreSQL instead
of mysql.

For people new to data base design, the book "Semantic Data Modeling"
or "Database ontwerp" from J.H. ter Bekke are excellent readings. It
discusses the Xplain data definition and data retrieval language. If
you stick for 100% to this language, you cannot make a mistake in your
data base design (as long as you try out not only the data definition, but
also the data retrieval part: the data retrieval part helps you to
quickly find errors in your design, just like Hans detected with his
example).

My tool xplain2sql (http://www.pobox.com/~berend/xplain2sql) can
translate Xplain to SQL, including mysql, InterBase and PostgreSQL. At
least this tool saves you a lot of typing.

-- 
Groetjes,

Berend. (-:

Re: sql

[Hans Hagen]

At 04:48 PM 3/18/2002 +0100, you wrote:
>Hans Hagen <pragma@wxs.nl> writes:
>
> > As part of context-on-demand support i'm looking into / playing with
> > interfacing to (my)sql (from xfdf to start with). I wonder if it is
> > possible to use patterns for field names? Say that i have v_1
> > ... v_20, is it possible to select v_5..v_10?
>
>I suggest a course data base design would be useful here :-)

i'm definitely no database guru but was wondering (looking back at using 
spss) if there was a way to do it that way, now i have to reconstruct some 
xml; actually, i have been playing with just pushing xml into such a 
database, but so far i haven't seen easy solutions (i.e. without the need 
to install all kind of extra stuff, compiling, hacking, etc).

Hans
-------------------------------------------------------------------------
                                   Hans Hagen | PRAGMA ADE | pragma@wxs.nl
                       Ridderstraat 27 | 8061 GH Hasselt | The Netherlands
  tel: +31 (0)38 477 53 69 | fax: +31 (0)38 477 53 74 | www.pragma-ade.com
-------------------------------------------------------------------------
                                   fall-back web server: 
www.pragma-ade.nl
-------------------------------------------------------------------------

Re: sql

[Ed L Cashin]

Hans Hagen <pragma@wxs.nl> writes:

> At 04:48 PM 3/18/2002 +0100, you wrote:
> >Hans Hagen <pragma@wxs.nl> writes:
> >
> > > As part of context-on-demand support i'm looking into / playing with
> > > interfacing to (my)sql (from xfdf to start with). I wonder if it is
> > > possible to use patterns for field names? Say that i have v_1
> > > ... v_20, is it possible to select v_5..v_10?
> >
> >I suggest a course data base design would be useful here :-)
> 
> i'm definitely no database guru but was wondering (looking back at
> using spss) if there was a way to do it that way, now i have to
> reconstruct some xml; actually, i have been playing with just pushing
> xml into such a database, but so far i haven't seen easy solutions
> (i.e. without the need to install all kind of extra stuff, compiling,
> hacking, etc).

There is, sort of, a way.  Most of the time when you use SQL it's from
another environment.  The environment where I've done most SQL stuff
is perl.  In perl it is easy to write, e.g., a perl subroutine that
generates the SQL you need.  You can collect a small bunch of
subroutines to generate the kind of SQL you work with most frequently.

The most amazing example of using perl as an extra level in language
abstraction is in openssl, where there's a bunch of perl code that
generates *assembly* code that does the cryptographic operations!

This demo that just prints the SQL instead of using DBI and executing
it:

  #! /usr/bin/perl -w

  use strict;

  &demo;

  sub select_col_range {
      my ($base, $lower, $upper) = @_;
      my @fields;
      for my $n ($lower .. $upper) {
          push @fields, "${base}_$n";
      }
      return "select " . (join ", ", @fields);
  }

  sub demo {
      print(&select_col_range("v", 5, 10), " from tablename\n");
  }

-- 
--Ed L Cashin            |   PGP public key:
  ecashin@uga.edu        |   http://noserose.net/e/pgp/