none
Regarding error "SQL: Statement too long." while updating the record RRS feed

  • Question

  • Hi,

    We are facing the "SQL: Statement too long." error message while updating the record. We are using nhibernate. Using nhibernate we save/update records. When we update small table records where no of  columns in that table is 50-90 it is working fine. but when we have a large no of columns we are getting the above error and the transaction is not commited.

    we are implementing  optimestic-lock in our application,If the record is modified application will show the errror mesage to user. when we call the update method nhibenate will include all the columns previous values in where clause of the update statement.

    Please check the follwing update statement.

    UPDATE company SET aimsbuild = ?, company = ?, phone = ?, duns = ?, gstno = ?, website = ?, ordformat = ?, invformat = ?, pikformat = ?, crmformat = ?, poformat = ?, repformat = ?, appformat = ?, raformat = ?, stmformat = ?, cutformat = ?, upsaccess = ?, upsuser = ?, upspw = ?, fdxacct = ?, fdxmeter = ?, spname = ?, spaddr1 = ?, spaddr2 = ?, spaddr3 = ?, spcity = ?, spstate = ?, spcountry = ?, spcntrycd = ?, spzip = ?, spphone = ?, spfax = ?, spemail = ?, divnamea = ?, divnameb = ?, divnamec = ?, divnamed = ?, divnamee = ?, divnamef = ?, divnameg = ?, divnameh = ?, divnamei = ?, divnamej = ?, divnamek = ?, divnamel = ?, divnamem = ?, divnamen = ?, divnameo = ?, divnamep = ?, divnameq = ?, divnamer = ?, divnames = ?, divnamet = ?, divnameu = ?, divnamev = ?, divnamew = ?, divnamex = ?, divnamey = ?, divnamez = ?, divisiona = ?, divisionc = ?, divisiond = ?, divisione = ?, divisionh = ?, divisioni = ?, divisionj = ?, divisionk = ?, divisionl = ?, divisionm = ?, divisionn = ?, divisiono = ?, divisionp = ?, divisionq = ?, divisionr = ?, divisions = ?, divisiont = ?, divisionu = ?, divisionv = ?, divisionw = ?, divisionx = ?, divisiony = ?, divisionz = ?, fax = ?, addr1 = ?, addr2 = ?, city = ?, state = ?, country = ?, cntrycd = ?, zip = ?, whname = ?, whaddr1 = ?, whaddr2 = ?, whcity = ?, whstate = ?, whcountry = ?, whcntrycd = ?, whzip = ?, whphone = ?, whfax = ?, currency = ?, edidirect = ?, weblinkurl = ?, weblinkexe = ?, cartexe = ?, carturl = ?, seasona = ?, seasonb = ?, seasonc = ?, seasond = ?, seasone = ?, seasonf = ?, seasony = ? WHERE primkey = ? AND aimsbuild = ? AND company = ? AND phone = ? AND duns = ? AND gstno = ? AND website = ? AND ordformat = ? AND invformat = ? AND pikformat = ? AND crmformat = ? AND poformat = ? AND repformat = ? AND appformat = ? AND raformat = ? AND stmformat = ? AND cutformat = ? AND upsaccess = ? AND upsuser = ? AND upspw = ? AND fdxacct = ? AND fdxmeter = ? AND spname = ? AND spaddr1 = ? AND spaddr2 = ? AND spaddr3 = ? AND spcity = ? AND spstate = ? AND spcountry = ? AND spcntrycd = ? AND spzip = ? AND spphone = ? AND spfax = ? AND spemail = ? AND divnamea = ? AND divnameb = ? AND divnamec = ? AND divnamed = ? AND divnamee = ? AND divnamef = ? AND divnameg = ? AND divnameh = ? AND divnamei = ? AND divnamej = ? AND divnamek = ? AND divnamel = ? AND divnamem = ? AND divnamen = ? AND divnameo = ? AND divnamep = ? AND divnameq = ? AND divnamer = ? AND divnames = ? AND divnamet = ? AND divnameu = ? AND divnamev = ? AND divnamew = ? AND divnamex = ? AND divnamey = ? AND divnamez = ? AND divisiona = ? AND divisionc = ? AND divisiond = ? AND divisione = ? AND divisionh = ? AND divisioni = ? AND divisionj = ? AND divisionk = ? AND divisionl = ? AND divisionm = ? AND divisionn = ? AND divisiono = ? AND divisionp = ? AND divisionq = ? AND divisionr = ? AND divisions = ? AND divisiont = ? AND divisionu = ? AND divisionv = ? AND divisionw = ? AND divisionx = ? AND divisiony = ? AND divisionz = ? AND fax = ? AND addr1 = ? AND addr2 = ? AND city = ? AND state = ? AND country = ? AND cntrycd = ? AND zip = ? AND whname = ? AND whaddr1 = ? AND whaddr2 = ? AND whcity = ? AND whstate = ? AND whcountry = ? AND whcntrycd = ? AND whzip = ? AND whphone = ? AND whfax = ? AND currency = ? AND edidirect = ? AND weblinkurl = ? AND weblinkexe = ? AND cartexe = ? AND carturl = ? AND seasona = ? AND seasonb = ? AND seasonc = ? AND seasond = ? AND seasone = ? AND seasonf = ? AND seasony = ?;p0 = 'DATA1\', p1 = 'GENEXUS INTERNATIONAL, INC.1', p2 = '(310) 680-03331', p3 = 'as1', p4 = '', p5 = 'www.bobitees.com1', p6 = 'dye340.frx', p7 = 'ord153.frx', p8 = 'fab200.frx', p9 = 'con310.frx', p10 = 'imagem.frx', p11 = 'wip410s.frx', p12 = 'ar310.frx', p13 = 'bom020.frx', p14 = 'pl190.frx', p15 = 'lcr320p.frx', p16 = '', p17 = 'genexus', p18 = 'mododoc1', p19 = '', p20 = '', p21 = 'dsgfdsgds', p22 = 'gdsgdsg', p23 = 'gds', p24 = 'dsgds', p25 = 'APO', p26 = 'AA', p27 = 'China', p28 = 'CN  ', p29 = '34006', p30 = 'dsg', p31 = 'sdgds', p32 = '', p33 = 'BOBI', p34 = 'Mod-O-Doc', p35 = 'Mod-O-Doc', p36 = '', p37 = 'Divine Wear', p38 = 'TRUE RELIGION', p39 = 'MODODOC UNISEX', p40 = 'dgds', p41 = 'gsg', p42 = 'dsg', p43 = 'dgdg', p44 = 'gdsg', p45 = 'dsg', p46 = 'dsgdsgds', p47 = 'gd', p48 = 'sg', p49 = 'dsgdsg', p50 = 'dsgdsg', p51 = 'dsgdsg', p52 = 'dsgdsdg', p53 = 'dssggdsg', p54 = 'dsgdsg', p55 = 'dsgds', p56 = 'g', p57 = 'gdsgds', p58 = 'dgdsg', p59 = 'BOBI a', p60 = 'MOD-WMN', p61 = '', p62 = 'DIVINE', p63 = 'hj', p64 = 'gdsg', p65 = 'dsgsdg', p66 = 'dsgds', p67 = 'gdsgds', p68 = 'gdsg', p69 = 'dsgdsg', p70 = 'dsgdsg', p71 = 'gdsgdg', p72 = 'dsgds', p73 = 'gdsgdss', p74 = 'g', p75 = 'dsgdsgdsg', p76 = 'dsgds', p77 = 'gdsgdsg', p78 = 'dsgds', p79 = 'ds', p80 = 'dsgsd', p81 = 'dsgdsg', p82 = '(310) 680-03201', p83 = '431 N. OAK STREET1', p84 = 'dert1', p85 = 'APO', p86 = 'AA', p87 = 'India', p88 = 'IN  ', p89 = '34034', p90 = 'GENEXUS INTERNATIONAL, INC.1', p91 = '431 N. OAK STREET1', p92 = '1', p93 = 'APO', p94 = 'AA', p95 = 'USA                           ', p96 = '', p97 = '34005', p98 = '(310) 680-0333', p99 = '(310) 680-0320', p100 = '', p101 = 'dgds', p102 = 'http://localhost/AIMSWeblinkSite/', p103 = 'f:\aimsweblink\aimsweblink.exe', p104 = 'f:\aimsshoplink\aimsshoplink.exe', p105 = 'http://preview.focaltechnology.com/easyshop4/aimsservice.asmx                                                                                                                                                                                             ', p106 = 'SPRING1', p107 = 'SUMMER1', p108 = 'FALL1', p109 = 'HOLIDAY1', p110 = 'RESORT1', p111 = 'CLOSEOUT', p112 = 'Year-Rnd', p113 = 1, p114 = 'DATA1\    ', p115 = 'GENEXUS INTERNATIONAL, INC.             ', p116 = '(310) 680-0333                ', p117 = 'as          ', p118 = '                    ', p119 = 'www.bobitees.com                                  ', p120 = 'dye340.frx                                        ', p121 = 'ord153.frx                                        ', p122 = 'fab200.frx                                        ', p123 = 'con310.frx                                        ', p124 = 'imagem.frx                                        ', p125 = 'wip410s.frx                                       ', p126 = 'ar310.frx                                         ', p127 = 'bom020.frx                                        ', p128 = 'pl190.frx                                         ', p129 = 'lcr320p.frx                                       ', p130 = '                    ', p131 = 'genexus             ', p132 = 'mododoc1            ', p133 = '                    ', p134 = '                    ', p135 = '', p136 = '', p137 = '', p138 = '', p139 = '', p140 = '', p141 = '', p142 = '', p143 = '', p144 = '', p145 = '', p146 = '', p147 = 'BOBI                                              ', p148 = 'Mod-O-Doc                                         ', p149 = 'Mod-O-Doc                                         ', p150 = '                                                  ', p151 = 'Divine Wear                                       ', p152 = 'TRUE RELIGION                                     ', p153 = 'MODODOC UNISEX                                    ', p154 = '                                                  ', p155 = '                                                  ', p156 = '                                                  ', p157 = '                                                  ', p158 = '                                                  ', p159 = '                                                  ', p160 = '                                                  ', p161 = '                                                  ', p162 = '                                                  ', p163 = '                                                  ', p164 = '                                                  ', p165 = '                                                  ', p166 = '                                                  ', p167 = '                                                  ', p168 = '                                                  ', p169 = '                                                  ', p170 = '                                                  ', p171 = '                                                  ', p172 = '                                                  ', p173 = 'BOBI a  ', p174 = 'MOD-WMN ', p175 = '        ', p176 = 'DIVINE  ', p177 = 'hj      ', p178 = '        ', p179 = '        ', p180 = '        ', p181 = '        ', p182 = '        ', p183 = '        ', p184 = '        ', p185 = '        ', p186 = '        ', p187 = '        ', p188 = '        ', p189 = '        ', p190 = '        ', p191 = '        ', p192 = '        ', p193 = '        ', p194 = '        ', p195 = '        ', p196 = '(310) 680-0320                ', p197 = '431 N. OAK STREET                       ', p198 = 'dert                                    ', p199 = 'INGLEWOOD           ', p200 = 'CA ', p201 = 'USA                           ', p202 = '', p203 = '90302     ', p204 = 'GENEXUS INTERNATIONAL, INC.', p205 = '431 N. OAK STREET                       ', p206 = '', p207 = 'INGLEWOOD           ', p208 = 'CA ', p209 = 'USA                           ', p210 = '', p211 = '90302     ', p212 = '(310) 680-0333', p213 = '(310) 680-0320', p214 = '   ', p215 = '', p216 = 'http://localhost/AIMSWeblinkSite/', p217 = 'f:\aimsweblink\aimsweblink.exe', p218 = 'f:\aimsshoplink\aimsshoplink.exe', p219 = 'http://preview.focaltechnology.com/easyshop4/aimsservice.asmx                                                                                                                                                                                             ', p220 = 'SPRING', p221 = 'SUMMER', p222 = 'FALL', p223 = 'HOLIDAY', p224 = 'RESORT', p225 = 'CLOSEOUT', p226 = 'Year-Rnd'

    when we call the transaction commit it is throwing the message  "SQL: Statement too long."

    What is mean by "SQL: Statement too long."?
    How can we resolve this error.?

    Any help can be appriciated. we are stuck at this point.

    Regards,
    KK

    Tuesday, March 2, 2010 2:10 PM

Answers

  • That is a badly designed table. Your field divnamea, divnameb,.... should be in another table as also divisiona, divisionb .....

    When you are updating, it should be enough to update based on primary key rather than all keys. Both these steps will decrease the size of your sql statement.
    • Proposed as answer by yanyee Tuesday, March 2, 2010 3:30 PM
    • Marked as answer by Martin_Xie Tuesday, March 9, 2010 4:39 AM
    Tuesday, March 2, 2010 2:59 PM

All replies

  • That is a badly designed table. Your field divnamea, divnameb,.... should be in another table as also divisiona, divisionb .....

    When you are updating, it should be enough to update based on primary key rather than all keys. Both these steps will decrease the size of your sql statement.
    • Proposed as answer by yanyee Tuesday, March 2, 2010 3:30 PM
    • Marked as answer by Martin_Xie Tuesday, March 9, 2010 4:39 AM
    Tuesday, March 2, 2010 2:59 PM
  • In Visual FoxPro, a single statement is limited to 8,192 characters.
    Can you check the length of the statement ?

    Systems Analyst
    Tuesday, March 2, 2010 4:58 PM

  •  MR KK:
         
         Analysis   of  DATABASE    is  not  Good , devide  table  to  many  other
         as Tushar  advice .
                                       Thanks  KK.                        
    azizsallam
    Wednesday, March 3, 2010 5:02 AM
  • Thanq for your reply.

    we are unable to modify the existing database. Because the database designed 15 years back and the application is in production.The same application we are migrating to WPF to give rich user interface and some more other features. we have to use the existing database only.

    Is there any other way?

    we can update the record using primkey column, In this case we are unable to implement optimistic-lock. Users can use either old foxpro application or  new WPF applciation.

    Our exact requirement is users can use our new WPF application or old FoxPro application at the same time.When some data is modified in any one of the application we have to show optimistic-lock message in other application when users try to edit and update the same record. This is working fine when we test with new WPF applcation instances. But when users try with old and new application instances the optimistic-lock is not working. because we added new column "version" to existing table and when any record is updated the version no is automatically incremented.Usiing this version no we are handle the optimistic-lock.

    But compare to old fox pro application we are not using this "version" column. when user updates any record "version" no is not incremented. So the WPF application is unable to recognize the chnages and the same  record is updated in wpf application without showing any message. To handle this we are including all the column in where clause.

    If we are including all the columns in where clause optimistic-lock is working fine in both applications. But we got the above error message when user modified all the fields.

    So is there any way to handle this error with out modifying the existing table structure.

    Thanks & Regards,
    KK
    • Edited by kk.parupalli Wednesday, March 3, 2010 5:51 AM some junk data included
    Wednesday, March 3, 2010 5:50 AM
  • What DBMS are you using? Couldn't you use stored procedures to perform the update? That could reduce the size of the statement.
    Thursday, March 4, 2010 10:36 PM
  • we are using Visual FoxPro as our database and Nhibernate is used to interact with database.
    Friday, March 5, 2010 5:57 AM
  • Thanq for your reply.

    we are unable to modify the existing database. Because the database designed 15 years back and the application is in production.The same application we are migrating to WPF to give rich user interface and some more other features. we have to use the existing database only.

    Is there any other way?

    we can update the record using primkey column, In this case we are unable to implement optimistic-lock. Users can use either old foxpro application or  new WPF applciation.

    Our exact requirement is users can use our new WPF application or old FoxPro application at the same time.When some data is modified in any one of the application we have to show optimistic-lock message in other application when users try to edit and update the same record. This is working fine when we test with new WPF applcation instances. But when users try with old and new application instances the optimistic-lock is not working. because we added new column "version" to existing table and when any record is updated the version no is automatically incremented.Usiing this version no we are handle the optimistic-lock.

    But compare to old fox pro application we are not using this "version" column. when user updates any record "version" no is not incremented. So the WPF application is unable to recognize the chnages and the same  record is updated in wpf application without showing any message. To handle this we are including all the column in where clause.

    If we are including all the columns in where clause optimistic-lock is working fine in both applications. But we got the above error message when user modified all the fields.

    So is there any way to handle this error with out modifying the existing table structure.

    Thanks & Regards,
    KK
    That is not true that you need all the columns to support optimistic locking. You could instead add a c(36) column as a unique version stamp and poke a Guid.NewGuid().ToString("D") there. You would simply check primary key + that Guid column for optimistic concurrency (that also would enable you to do the update in junks if need be).
    Friday, March 5, 2010 2:42 PM