This is a bit off topic, but I felt I must mention it. Core SQL99 requires a
standard conforming DBMS to implement either embedded SQL or a modul
language (hope this is the technical correct translation of German
"Modulsprache"). The module language must have a "SELECT INTO" command and
the semantics is to put the (one row) result into host variables. So the
mysql and access semantics of "SELECT INTO" is not standard conforming.
Peter
-----Urspr�ngliche Nachricht-----
Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Gesendet am: Mittwoch, 23. Mai 2001 17:16
An: [EMAIL PROTECTED]
Betreff: Re: AW: (not) Too stupid to bind a variable... :-)
Yes, MS Access uses "SELECT ... INTO to put the results into another
table; I
can't remember for sure, but I think it actually creates the table,
like an
Access 'Make Table' query, rather than just inserting into an
existing one. And
to add to the confusion, MySQL uses SELECT ... INTO OUTFILE
'filename' FROM ...
to redirect the results of the select into a file on the filesystem.
This is one reason why using DBI or a similar API does not and
probably cannot
lead to completely database-independent code. If you use any
proprietary SQL
syntax, that SQL would have to be rewritten if you ever switch
databases. The
biggest example that comes to mind is retrieving the ID of a row you
just
inserted, that the database assigns. Yes, you could write extra
code to do this
manually in a db-independent way, but then you're not using the full
power of
your database; the same can be said of other proprietary SQL
constructs. They
can often be avoided, but not without a little pain, and not without
taking time
to figure out which constructs are proprietary and which are both
standard and
commonly implemented. The extensions are all generally added in
order to
distinguish the db vendor by better serving the user, so it seems to
make sense
to use them when they help.
"Tim Scott" <[EMAIL PROTECTED]> on
05/23/2001 10:43:20
AM
To: [EMAIL PROTECTED],
[EMAIL PROTECTED]
cc: (bcc: Wesley Sheldahl/Lex/Lexmark)
Subject: Re: AW: (not) Too stupid to bind a variable... :-)
It looks like there's some confusion over SQL syntax here. Is there
a
database which allows you to effect an insert into a table by doing
a
'select ... into ' ?
Oracle certainly doesn't and if Peter's using Oracle then what he
suggests
should (and does) work fine. Having said that TIMTOWTDI :) ...
Looking at the plsql.t test with the DBD::Oracle distribution (lines
234...)
this shows how a cursor can be used to fetch data. This seems like
the
neatest option to me and will possible prove the most optimal in
various
situations. How portable it is to other databases I can't say.
Regards,
Tim
