SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    Umm. PHP Guru....Naaaah jaswinder_rana's Avatar
    Join Date
    Jul 2004
    Location
    canada
    Posts
    3,193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question will auto_increment work for just MySQL or all databases !!

    i am using database abstraction layer. Right now i am using MySQL. i am using auto_increment.
    I was just wondering would auto_increment work the same way in other databases like oracle or NOT

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    no, it doesn't work the same way in oracle

    (aside: nothing works the same in oracle)

    in oracle you have sequences which are separate from the tables

    you use a function called NEXTVAL to pull the next value off a sequence, and then you use that value when inserting into the table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Umm. PHP Guru....Naaaah jaswinder_rana's Avatar
    Join Date
    Jul 2004
    Location
    canada
    Posts
    3,193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So, how i should design my tables so that if i change database i should not have to change anything in my database. what i mean is i have created an sql file which i will run. so if i change the database then i'll just run that file against that database and i won't have to change anything.

    i hope i am being clear enough

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jaswinder_rana
    So, how i should design my tables so that if i change database i should not have to change anything in my database.
    you cannot

    every relational database product is different

    if you want a script of sql statements that will work absolutely the same without change in every database, you are wishing for the moon

    and if you write application code to overcome the fact that you are not utilizing auto_increments in mysql or sequences in oracle because they cannot both be accommodated by the same script, then you are doing yourself harm
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Umm. PHP Guru....Naaaah jaswinder_rana's Avatar
    Join Date
    Jul 2004
    Location
    canada
    Posts
    3,193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So, what is the use for database abstraction layer? i mean if everything changes then suppose we are doing something with this newly created id and if we change our database from mysql to oracle then that would break RIGHT???

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    that is correct

    the way to write a database abstraction layer is to write the layer at a very high level, like "add item to cart" and then you must have a separate plugin module for every different database system that you plan to support

    kind of like writing an application for a generic database, except then you have to write all the database-handling scripts for each database separately
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Umm. PHP Guru....Naaaah jaswinder_rana's Avatar
    Join Date
    Jul 2004
    Location
    canada
    Posts
    3,193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it means when we would use database abstraction layer and if we change the database it would automatically take care for everything.
    and it means we should not use any database dependent code in our application but in database abstraction layer. RIGHT??

    ok say for example we inserted an automatically generated id an table and we can get it in mysql like mysql_insert_id. so we will create a function, so that if database is mysql, then it will use this function else it would use othe rone depending on other database right??

    thanks for the answers. please tell anything that you tihnk i should know.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, that sounds like a good approach
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Member
    Join Date
    Jun 2004
    Location
    us of a
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Jassi,
    For abstraction layer example you can look at adodb php library code.
    In Interbase/Firebird database you have to create a Generator for generating autoincrement value and a Trigger which calls Generator at the time of Insert.

    slamdunkinpool

  10. #10
    SitePoint Guru MikeBigg's Avatar
    Join Date
    Jun 2004
    Location
    Reading, UK
    Posts
    970
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    (aside: nothing works the same in oracle)
    LOL - ain't that the truth

    Mike


Bookmarks

Posting Permissions

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