Thread: Table size?

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    10
    Rep Power
    0

    Table size?


    I have a table with a lot of Varchar fields.

    Like: VARCHAR (10000)

    But it seems that I am running out of space on this table.

    When a try to add a new varchar field (10000), I get this error:



    "fmCompile.trCompile:
    This operation is not defined for system tables.
    Unsucceddfull metadata update.
    New record size of 69412 bytes is too long.
    TABLE MYTABLE"


    Any help?
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    1
    Rep Power
    0

    Table Size


    There is a row width limit of 64k BYTES. ie Unicode chars will be half, etc.

    Easy fix, just use BLOBS - each field uses 10 bytes ( it points to the real data)

    see this:

    http://www.volny.cz/iprenosil/interb...ib_strings.htm

    cheers - dave
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    250
    Rep Power
    14
    You can use BLOBs for larger text columns, however, they cannot be indexed, easily searched and should be updated/inserted in different ways.

    A more important and logical question would be:
    Why on earth do you need VARCHAR(10000) columns?
    Martijn Tonies
    Database Workbench: developer IDE for Firebird, MySQL, InterBase, MSSQL Server and Oracle
    Upscene Productions
    http://www.upscene.com
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    10
    Rep Power
    0
    Hi upscene & wagga:


    Thanks for your reply.


    I need the big varchar fields to store some graphics settings in.
    I don't generete the settings, I just need to store them.

    I will try the Blob field later.

    Thanks for now.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    250
    Rep Power
    14
    In that case, you really should use BLOBs. But are these settings this large?

    Well, good luck.
    Martijn Tonies
    Database Workbench: developer IDE for Firebird, MySQL, InterBase, MSSQL Server and Oracle
    Upscene Productions
    http://www.upscene.com
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    10
    Rep Power
    0
    Hi upscene:

    Are there a special way to handle BLOB fields when update/insert?


    This:
    UPDATE myTABLE SET settings = "TEST"

    dont work.


    Error:
    Unsuccessful execution caused by system error that does not preclude successful execution of subsequent statements.
    feature is not supported.
    BLOB and array data types are not supported for move operation
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    250
    Rep Power
    14
    Are there a special way to handle BLOB fields when update/insert?
    First of all, don't use double quotes ("value") but single quotes for values ('value'). With double quotes, Firebird thinks you're using a column name.

    Second, BLOBs can be updated/inserted directly if you're using text blobs (BLOB SUB_TYPE TEXT), but others require you to use prepared statements:

    INSERT INTO ... (MYBLOB) VALUES (:MYBLOB)

    Prepare the statement.

    (In Delphi)
    Use something like:
    ParamByName('MYBLOB').LoadFromStream(etc)
    Martijn Tonies
    Database Workbench: developer IDE for Firebird, MySQL, InterBase, MSSQL Server and Oracle
    Upscene Productions
    http://www.upscene.com
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    10
    Rep Power
    0
    Hi upscene:

    Again thanks.


    I use Database workbench as database editor.
    Really great product.

    I am quit new to Firebird, and I want to optimize my speed/mem usage on my database.
    Maybe you can help me explain some of these terms:


    - Page size
    - Allocated pages
    - Page buffers
    - Sweep interval


    Thanks

    Regards
    Thomas
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    250
    Rep Power
    14
    Hi Thomas,

    >>I use Database workbench as database editor.
    >>Really great product.

    Thank you for your kind comments.

    I advise you to download the InterBase 6 documentation (from www.ibphoenix.com) - most of it is still valid for Firebird.

    Short explanations:
    >> Page size

    Each database is build from "pages". Blocks of database-data with a certain size. Depending on your average record length, one page might be better than another. Most people use 8192 bytes as their page size.

    >> Allocated pages

    The number of pages used for your database.

    >> Page buffers

    The number of pages Firebird will allocate as cache memory when someone connects to this database.

    >> Sweep interval

    Check this elsewhere, I'm unable to explain this quickly.
    Martijn Tonies
    Database Workbench: developer IDE for Firebird, MySQL, InterBase, MSSQL Server and Oracle
    Upscene Productions
    http://www.upscene.com

IMN logo majestic logo threadwatch logo seochat tools logo