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

    Join Date
    Mar 2007
    Posts
    177
    Rep Power
    40

    Access violation


    I use DELPHI XE6 with mysql5.7. I use myODBC 5.1 to connect to the db as well.
    I added a few extra fields into 1 of my tables. I have 306 fields currently in there.
    After i did this if a run a simple select all query i get an access violation. If a use a SELECTIVE select, lets say ony 10 fields or 20 in the select query it works.
    It only gives me the error on the SELECT *. I dont know if there is a limitation on the MYSQL, DELPHI, MYODBC or wherever.
    Does anyone have any advice for me please? Thanks
  2. #2
  3. Forgotten Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,109
    Rep Power
    9644
    Advice? 306 fields in a table is utterly ridiculous. You need to fix that table so (1) it has fewer fields and (2) you don't find yourself having to add more.

    What does the table look like and what are all the fields for?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2007
    Posts
    177
    Rep Power
    40

    Thumbs up


    Originally Posted by requinix
    Advice? 306 fields in a table is utterly ridiculous. You need to fix that table so (1) it has fewer fields and (2) you don't find yourself having to add more.

    What does the table look like and what are all the fields for?
    To be honest. I know you are right. We have been adding to this table for probably 18 years plus.
    There are a few fields i can remove that i know is ":dead" fields but surely there is no limit on the amount of fields? It is our main table for sales
    and to remove some fields will only fix the problem TEMPORARY. As we do more developing, new fields will be added and i will find myself
    in the same situation again. We added about 38 fields this year alone. And yes we must probably think twice in future before we do that.
    Is there anything i can set on the mysql side maybe? Otherwise we probably need to split our main table in 2.

    Thanks for replying.
  6. #4
  7. Forgotten Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,109
    Rep Power
    9644
    Actually there are limits on the table, though I'd expect you to come across them when adding columns or saving data - "Access violation" when SELECTing sounds particularly bad.

    Rearranging things might not be as hard as you think. We can give you advice but it depends on things like what the table is, how it's used, what all these fields are for, and why you need to add so many in such a short time span.
    For example, if you're using this table to store custom values then you can turn a wide table
    Code:
    id | field1 | field2 | field3...
    ---+--------+--------+-------
     1 |    123 |    456 |    789
    into a long table
    Code:
    id | key    | value
    ---+--------+------
     1 | field1 | 123
     1 | field2 | 456
     1 | field3 | 789
    It might seem odd but it will (can) have very good performance, and you'll never need to add columns ever again.

    But again, it depends on the answers to those questions.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2007
    Posts
    177
    Rep Power
    40
    Originally Posted by requinix
    Actually there are limits on the table, though I'd expect you to come across them when adding columns or saving data - "Access violation" when SELECTing sounds particularly bad.

    Rearranging things might not be as hard as you think. We can give you advice but it depends on things like what the table is, how it's used, what all these fields are for, and why you need to add so many in such a short time span.
    For example, if you're using this table to store custom values then you can turn a wide table
    Code:
    id | field1 | field2 | field3...
    ---+--------+--------+-------
     1 |    123 |    456 |    789
    into a long table
    Code:
    id | key    | value
    ---+--------+------
     1 | field1 | 123
     1 | field2 | 456
     1 | field3 | 789
    It might seem odd but it will (can) have very good performance, and you'll never need to add columns ever again.

    But again, it depends on the answers to those questions.
    Thanks for the reply. I managed to remove 31 fields but still the same error. So i see what you mean and definitely your suggestion should work
    but wow i am not sure how to proceed as we use that table probably 500 times all over the project. But let me tell you what we store in the table.

    Its literally a trip system

    Each record /sale has its own unique number. Each trip has a client, supplier, 4 drivers, 4 vehicles, up to 6 order numbers, product, town and regions from and to etc.
    Normally each of those has a code and name (and yes i know we shouldve had joins statements to other tables to get those. Then we have km for each vehicle,
    status of the trip, extra documentations, delivery dates. I can go on.But there is valid reasons for all of those.

    Ill try to sort through the table some more and see where i can safe some columns. Thanks for your help so far.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,679
    Rep Power
    4288
    Originally Posted by requinix
    Code:
    id | key    | value
    ---+--------+------
     1 | field1 | 123
     1 | field2 | 456
     1 | field3 | 789
    oh my god, please -- do not do this

    google EAV (entity attribute value) -- it performs fine for inserts, but pulling out meaningful and useful data is a real shít show

    better to vertically split the table into two or three tables with same PK

    Comments on this post

    • requinix agrees : ...huh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,679
    Rep Power
    4288
    Originally Posted by icuras
    Each trip has a client, supplier, 4 drivers, 4 vehicles, up to 6 order numbers
    you should normalize those repeating columns into their own tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. Forgotten Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,109
    Rep Power
    9644
    Originally Posted by r937
    oh my god, please -- do not do this

    google EAV (entity attribute value) -- it performs fine for inserts, but pulling out meaningful and useful data is a real shít show

    better to vertically split the table into two or three tables with same PK
    Really? Even with an index? It could be a large index but I can't imagine it would be that slow. Or even use a whatsit index containing all the columns.
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,679
    Rep Power
    4288
    Originally Posted by requinix
    ... I can't imagine it would be that slow.
    you've obviously never had to work with an EAV database

    it's not the performance... it's the complexity
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. Forgotten Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,109
    Rep Power
    9644
    Actually I have worked with an EAV database: it was a CMS that stored just about everything with that model, in MySQL, and I needed to do some work that was easier to accomplish on the database itself (not least because their code was terrible and horribly inefficient). While it was rather unpleasant to work with, the EAV model wasn't so much the problem (at least not for my purposes) but their poor implementation of versioning and usage of XML strings in certain locations.
    I certainly wouldn't suggest using it to store actual, mostly-fixed-schema describable entities, where a relational design is clearly more suitable, but without NoSQL-style data access EAV seems fairly well suited for the sort of arbitrary-metadata, key/value-pair style which it sounds like OP is trying to get. At least I can't imagine it being worse than a system where s/he needs to add a few columns a month - which also suggests a sparse nature to the data.

    Insert reiteration for more details about the table and its usage here.

IMN logo majestic logo threadwatch logo seochat tools logo