Page 1 of 4 123 ... Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    293
    Rep Power
    16

    How can I do multirow insert in Firebird?


    I would like to find a way to do a multirow insert in Firebird similar to the examples below ... if possible.
    Note that I need it to work with constant values and not values selected from a(nother) table (ref examples below).

    Anyone?

    In MySQL and DB2 I can do this:
    insert into pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
    values (8000,0,'Multi 8000',1),(8001,0,'Multi 8001',1)

    In MSSQL, PostgreSQL, and SQLite I can do this:
    insert into pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
    select 8000,0,'Multi 8000',1 union all select 8001,0,'Multi 8001',1
    BongoBongo
  2. #2
  3. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,517
    Rep Power
    543
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    293
    Rep Power
    16
    As you probably know allready (from your asking in one of my other threads), I'm creating a web application that shall be able to run against different databases.

    For both Oracle and Firebird I have not yet found a working solution to my question.

    That was why I posted those questions on both Oracle and Firebird forums. ...different databases and probably different solutions...

    By the way I tried this against Oracle:
    insert into pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
    select 8000,0,'Multi 8000',1 from dual
    union all select 8001,0,'Multi 8001',1 from dual

    Which worked nicely thanks for that.....

    But could not make it work for Firebird....

    Could you please add more details to how to do it in Firebird

    Regards.
    Last edited by phpweb; December 2nd, 2006 at 08:47 AM.
    BongoBongo
  6. #4
  7. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,517
    Rep Power
    543
    Originally Posted by phpweb
    ...
    But could not make it work for Firebird....
    This means nothing. What error/strange behaviour do you get?
    What's your actual statement?
    But, most of all, why did you go into forcing your app to use all mysql's non standard extensions/behaviours if you want to make it work with many different databases? You should have kept it simple.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    293
    Rep Power
    16
    As I said, I did not understand how to do it on Firebird using your proposed partial solution which was:

    Add an "FROM rdb$database;" to your select (sounds like Oracle eh?)

    So if you provided a full "insert into ..." statement that would explain it all.

    You said:
    But, most of all, why did you go into forcing your app to use all mysql's non standard extensions/behaviours if you want to make it work with many different databases? You should have kept it simple.

    How do you know that I'm to force MySQL syntax on the rest of the databases?

    I'm just trying to learn how the different databases can handle multiple inserts in one query (one of many challenges when developing a multi database application).

    From what I know, sending multiple queries to the databases, one for each row to be inserted is way more slower than doing it in one query.

    Now I have a working solution for the other databases, just need to see if there also is a solution for this in Firebird.

    From my standpoint it is quite smart to first learn what is possible to do against the different databases SQL wise before starting the development process.... will probably save me lots of hours.

    It might be that you do not agree with my angle of approach but then you do not know what I'm to develop either.

    It is a simple question, which there probably is a simple answer as well.

    Regards
    BongoBongo
  10. #6
  11. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,517
    Rep Power
    543
    Originally Posted by phpweb
    ...
    It is a simple question, which there probably is a simple answer as well.
    Shure, here it is
    sql Code:
    INSERT INTO pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
    SELECT 8000,0,'Multi 8000',1 FROM rdb$database
    UNION ALL SELECT 8001,0,'Multi 8001',1 FROM rdb$database
    and it's really like Oracle's
    sql Code:
    INSERT INTO pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
    SELECT 8000,0,'Multi 8000',1 FROM dual
    UNION ALL SELECT 8001,0,'Multi 8001',1 FROM dual
    Originally Posted by phpweb
    How do you know that I'm to force MySQL syntax on the rest of the databases?
    from your questions I guess, but I was speaking about MySQL's behaviour, not strictly syntax.
    Originally Posted by phpweb
    It might be that you do not agree with my angle of approach
    Shure
    Originally Posted by phpweb
    but then you do not know what I'm to develop either.
    Again, but I know what an app able to support multiple databases is and how it's built and I don't think you are going the right way (or the easy way for instance) but I'm shure that you'll learn a lot from these efforts if not from my words.
    Last edited by pabloj; December 2nd, 2006 at 10:19 AM.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    293
    Rep Power
    16
    Hi.

    Tried this:

    INSERT INTO pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
    SELECT 8000,0,'Multi 8000',1 FROM rdb$database
    UNION ALL SELECT 8001,0,'Multi 8001',1 FROM rdb$database

    and get this in return:
    Error code='-802' ErrMessage='arithmetic exception, numeric overflow, or string truncation '

    If I change the "database" in:
    rdb$database
    to the databasename in use I get:

    Error code='-204' ErrMessage='Dynamic SQL Error SQL error code = -204 Table unknown RDB$PARENTS At line 3, column 1 '

    Do you have some links that describe how to make this work against Firebird..., or another working solution?

    I cannot make it work with the syntax you posted.

    Regards

    Comments on this post

    • pabloj disagrees : You can't, all others do, check your code
    BongoBongo
  14. #8
  15. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,517
    Rep Power
    543
    It's working perfectly fine for me, you must have messed up something, are you on a database client or using some kind of script?

    Like a string value too long for the field ...
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    293
    Rep Power
    16
    UPDATE:

    I can do this:
    INSERT INTO pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
    SELECT 8000,0,'Multi 8000',1 FROM rdb$database

    but not this:
    INSERT INTO pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
    SELECT 8000,0,'Multi 8000',1 FROM rdb$database
    UNION ALL SELECT 8001,0,'Multi 8001',1 FROM rdb$database

    where I get the errormessage previously posted

    strange....


    Thanks for superfast replies...

    the PAG_NAME is char(50)

    I'm using PHP 5.2 on an XP machine.

    All installed locally, and using Firebird 2.0.

    Doing simple inserts against table work like a charm.

    What is "rdb$database" anyway.

    Is this something I have to define within Firebird to make it work ???

    If you have a link to some place where queries the one you suggested are discussed I would like to see those?

    Then I tried this:
    select * from rdb$database

    and get one row returned with these column-names:
    RDB$DESCRIPTION
    RDB$RELATION_ID
    RDB$SECURITY_CLASS
    RDB$CHARACTER_SET_NAME

    Doing this:
    select "8001 cool" as PagName from rdb$database

    returns one row with col-name as PagName

    so at least that is working

    I'm stuck... for now

    Regards
    Last edited by phpweb; December 2nd, 2006 at 11:16 AM.
    BongoBongo
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2006
    Posts
    205
    Rep Power
    19
    Pablo's SQL works for me just as fine.

    That RDB$DATABASE is a system table which contains only one record, all firebird DBs have this by default. You can list the system tables in iSQL by the SHOW TABLES command.

    For that SQL above you can use any table you like from your DB, cuz it doesn't matter the source since it has constant returning values, but it will return as many rows as many records that table has. Since RDB$DATABASE has only one row it returns 1.
    It exactly like if u use another table (any of yours) and add the FIRST 1 to the select clause.

    SELECT first 1 8000,0,'Multi 8000',1 FROM mytable
    UNION ALL SELECT first 1 8001,0,'Multi 8001',1 FROM mytable;

    But no need to complicate it this way, i just showed why it is so. Go on Pablo's way.

    Comments on this post

    • pabloj agrees
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    293
    Rep Power
    16
    To nagysz (and pabloj)

    Thanks for replies

    Yep... I understand that now.

    And I do belive it is working for you guys.

    The funny thing is that this query is working for me (1 row):

    INSERT INTO pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
    SELECT 8000,0,'Multi 8000',1 FROM rdb$database

    But this is not (2 rows):

    INSERT INTO pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
    SELECT 8000,0,'Multi 8000',1 FROM rdb$database
    UNION ALL SELECT 8001,0,'Multi 8001',1 FROM rdb$database

    where I get this error:
    Error code='-802' ErrMessage='arithmetic exception, numeric overflow, or string truncation '

    Which does not say much to me at all.... since my PAG_NAME field are 50 characters long and there should be no problem with the numbers either.

    I also tried to insert a shorter string into PAG_NAME using only 'M', but still got same error.

    Any suggestions
    BongoBongo
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    62
    Rep Power
    17
    See EXECUTE BLOCK SQL Language Extension
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    293
    Rep Power
    16

    Getting closer


    I'm finally closer to making this multirow query work on firebird.

    What I found is this:

    First this is my creation string for the database:

    CREATE DATABASE 'localhost:c:/ibase/PARENTS.FDB' DEFAULT CHARACTER SET UTF8

    Table def look like this:

    CREATE TABLE PAGEIT (PAG_ID INTEGER DEFAULT 0 NOT NULL,
    PAG_PARENT INTEGER DEFAULT 0 NOT NULL,
    PAG_NAME CHAR(50) NOT NULL,
    PAG_ACTIVE SMALLINT DEFAULT 0 NOT NULL,
    PRIMARY KEY (PAG_ID));

    I'm using ibWebAdmin as admin interface against the database...puh....not on par with phpmyadmin though but anyway....

    Today I connected to the database using ibWebAdmin and wrongly selected ISO8859_1 as charset against the database.

    Then I tested this query (which everybody else seem to get going)

    INSERT INTO pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
    SELECT 8000,0,'Multi 8000',1 FROM rdb$database
    UNION ALL SELECT 8001,0,'Multi 8001',1 FROM rdb$database

    And it worked.
    But does not when query comes from my PHP script.....

    Okay... then I logout from database (ibWebAdmin), and login again, but this time I select charset UTF8 (which I should have done in first place)....and then run same query.

    Booom.... same error message as from my php script.

    I can do this:
    INSERT INTO pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
    values (8000,0,'Multi 8000',1)

    which work

    but not the multirow query.....

    Anybody with a clue what is going on here

    Definitely has something with charset to do, but in what way????

    If the database is in UTF8, why does this multirow query not work if I connect to database with UTF8 charset?
    Last edited by phpweb; December 5th, 2006 at 04:00 AM.
    BongoBongo
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    388
    Originally Posted by phpweb
    Table def look like this:

    CREATE TABLE PAGEIT (PAG_ID INTEGER DEFAULT 0 NOT NULL,
    PAG_PARENT INTEGER DEFAULT 0 NOT NULL,
    PAG_NAME CHAR(50) NOT NULL,
    PAG_ACTIVE SMALLINT DEFAULT 0 NOT NULL,
    PRIMARY KEY (PAG_ID));
    Just curious, why:
    PAG_NAME CHAR(50) NOT NULL

    and not

    PAG_NAME VARCHAR(50) NOT NULL

    Wondering if the changed definition would make a difference.

    Clive
  28. #15
  29. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    293
    Rep Power
    16

    Found it, partly....


    Thanks for the replies guys.

    Finally I found what caused it.

    It was the charset that was wrong.

    As I posted in my previous post here, it worked from a firebase frontend called ibWebAdmin.

    The database is in UTF8.
    When I connected from ibWebAdmin last time I "wrongly" selected the ISO8859_1 charset when connecting to the database.
    Then I tried the multirow query and it worked.

    INSERT INTO pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
    SELECT 8000,0,'Multi 8000',1 FROM rdb$database
    UNION ALL SELECT 8001,0,'Multi 8001',1 FROM rdb$database


    Earlier I had used this when connecting from PHP:
    $this->db = @ibase_connect($dbpath.$dbname, $dbuser, $dbpass, 'UTF8', '0', '1')

    changing the charset from UTF8 to ISO8859_1 made it also work from PHP.

    Is this a bug?

    Why should I not be able to run the query from PHP when setting charset = UTF8 in the connection?

    Also... I'm using this after connecting:
    @ibase_query($this->db, 'SET NAMES UTF8')

    Would appreciate if someone could enlighten me here.

    One other thing I found with firebird and multirow query.

    It bailes out if I insert more than 128 rows using the "multirow" query above.

    This is the error message I get if using more than 128 rows:

    Errcode: -902
    Error: Dynamic SQL Error Too many Contexts of Relation/Procedure/Views. Maximum allowed is 255

    Is there some settings that would allow me to bypass that limitation or is this some firebird engine limitation?
    BongoBongo
Page 1 of 4 123 ... Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo