Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2006
    Posts
    15

    Question Unanswered: select nur einen datensatz

    sorry, wrong language english translation at the end of text.

    hallo,
    ich habe vor eine routine zu schreiben um die feldnamen aus verschiedenen tabellen auszulesen. das soll unabhängig von der datenbank und tabelle sein.
    dazu wollte ich mir einen recordset mit einer zeile füllen, den durchlaufen und die feldnamen auslesen. das klappt bisher auch hervorranden aber jetzt kommt das problem.
    wenn ich ein "select * from TblName" mache, dauert die antwort unter umständen sehr lange, weil die datenbank sehr voll sein könnte.
    ich möchte jetzt nur einen oder von mir aus eine handvoll records zurückbekommen, da mich der inhalt nicht interessiert, sondern nur die feldnamen.
    da ich nicht weiss welche tabelle abgefragt wird, kann ich kein "select * where id = (select max(id))" oder sowas machen. weil ich nicht weiss welcher datenbanktyp benutzt wird, kann ich kein top, limit oder head einsetzten.
    mein letzter strohhalm wäre eine abfrage welcher db typ eingesetzt wird und danach branchen um dann doch dbserver abhängig ein top oder limit einzusetzen.
    mir wäre eine standard-sql lösung, die ohne branch auskommt aber die liebste.

    hello,
    I am looking for a way to select only one record from a table. I want to write a function, that gets the fieldnames from a database table, independent what database is used. I can not use top, limit or head or something like that, because I can not find out what database type is used. I can not use "select * where ID=(select max(ID))" because I dont know if subselects are allowed and if there is a field named ID in the table in question.
    My last hope would be to put the type of database in the functions parameterlist and branch insinde of my function, so I can use top, limit, show fieldnames or anything like that. I'd rather a standard-sql solution to be absolutly independt of the used database.
    Last edited by spjansen; 09-07-06 at 05:06.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you can use your first technique, to query the table, but use a WHERE clause that returns no rows but still gives you the column names --
    Code:
    select * from TblName where 1=0
    alternatively you can query the INFORMATION_SCHEMA views which are part of the sql standard and supported by most databases --
    Code:
    select column_name
         , data_type
         , is_nullable
         , column_default
      from information_schema.columns
     where table_name = 'TblName'
    order
        by ordinal_position
    ich wollte auf deutsch antworten aber mein deutsch ist leider schwach und ziemlich unsicher

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    First solution may not produce any 'column' names (header) in some dbms'.
    Second solution is M$ SQL and Sybase (not all dbms').
    TOP 1 is also limited to a few dbms'
    Even if you code a procedure to branch per dbms, it may not compile in some of them.

    Good luck!

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

    Arrow

    Quote Originally Posted by LKBrwn_DBA
    Second solution is M$ SQL and Sybase (not all dbms').
    actually, second solution is standard sql
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Not for all dbms':
    Code:
    SQL> select column_name
      2       , data_type
      3       , is_nullable
      4       , column_default
      5    from information_schema.columns
      6   where table_name = 'HELP'
      7  order
      8*     by ordinal_position;
    
      from information_schema.columns
                              *
    ERROR at line 5:
    ORA-00942: table or view does not exist



    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hey, what do you know, oracle doesn't support standard sql (as if that's a news flash)

    see http://www.dbazine.com/db2/db2-disarticles/pelzer4
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down

    Quote Originally Posted by r937
    actually, second solution is standard sql

    Even if it's "standard" sql, 'information_schema' will refer to one and only 'database' as defined by non-oracle terms. In Oracle the equivalent would be one schema (user) in a database (which contains many schema's).

    Still, he will not have a 'generic' solution.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •