Re: How to handle boolean in literal where clauses with squel 5.28
On Wednesday, January 15, 2020 at 1:14:50 AM UTC-8, Bernhard Weichel wrote: > > in https://github.com/oneclick/rubyinstaller2/issues/108 I found a > solution by using another ADO provider. > > replace > >provider = "Microsoft.Jet.OLEDB.4.0" > > with > > provider = "Microsoft.ACE.OLEDB.12.0" > > > It really seems to be that simple :-) triggering the question, if sequel > could raise a warning in this case. > I'm glad the fix was that simple. I don't think Sequel could raise a warning. How would Sequel know that you specified the wrong provider? Specifying the correct provider is and should be the responsibility of the user. Thanks, Jeremy -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to sequel-talk+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sequel-talk/5c7d4705-fde4-45d8-98a7-5a2a06e1e59a%40googlegroups.com.
Re: How to handle boolean in literal where clauses with squel 5.28
Am Mittwoch, 15. Januar 2020 01:37:21 UTC+1 schrieb Jeremy Evans: > > could it be that ruby 1.9.3 is 32 bit while 2.6.0 is 64 bit? But then it >> should not handle select statements without the where clause either. >> > > Could be, it's hard to say. It does appear to be an issue with Ruby and > not a Sequel-specific issue, though. > > Thanks, > Jeremy > were you able to run my example? in https://github.com/oneclick/rubyinstaller2/issues/108 I found a solution by using another ADO provider. replace provider = "Microsoft.Jet.OLEDB.4.0" with provider = "Microsoft.ACE.OLEDB.12.0" It really seems to be that simple :-) triggering the question, if sequel could raise a warning in this case. Nevertheless thank you very much for sequel and for your help. Bernhard -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to sequel-talk+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sequel-talk/c174daf9-70d3-494c-b778-0e77f165928d%40googlegroups.com.
Re: How to handle boolean in literal where clauses with squel 5.28
On Tuesday, January 14, 2020 at 2:42:30 PM UTC-8, Bernhard Weichel wrote: > > Jeremy, thanks for your response. > > I created a minimal self-contained example in the attached file. > I do not manage to perform > > it does work with ruby 1.9.3, having sequel 4.49 where it produces > > P:\OptigemBridge\obdev\80_sequel-tests>ruby testwithmdb.rb > all data unfiltered > --- > {:ID=>6, :Numerical=>1, :Boolean=>true, :Text=>"\"Record 1 boolean > true\""} > {:ID=>7, :Numerical=>2, :Boolean=>false, :Text=>"\"Record 2 boolean > false\""} > > playing with boolean > > > table.where(Boolean: true) -> SELECT * FROM [Optigem] WHERE ([Boolean] = > -1) > {:ID=>6, :Numerical=>1, :Boolean=>true, :Text=>"\"Record 1 boolean > true\""} > > P:\OptigemBridge\obdev\80_sequel-tests>gem which sequel > > P:/OptigemBridge/tools/ruby/lib/ruby/gems/1.9.1/gems/sequel-4.49.0/lib/sequel.rb > > sorry I do not manage to run it as bundle exec ruby testwithmdb.rb > > but with ruby 2.6 I get > > P:\OptigemBridge\obdev\80_sequel-tests>bundle exec ruby testwithmdb.rb > all data unfiltered > --- > {:ID=>6, :Numerical=>1, :Boolean=>true, :Text=>"\"Record 1 boolean > true\""} > {:ID=>7, :Numerical=>2, :Boolean=>false, :Text=>"\"Record 2 boolean > false\""} > > playing with boolean > > > table.where(Boolean: true) -> SELECT * FROM [Optigem] WHERE ([Boolean] = > -1) > WIN32OLERuntimeError: (in OLE method `Execute': ) > OLE error code:80040E14 in Microsoft JET Database Engine > Interner Fehler bei OLE-Automatisierung > HRESULT error code:0x80020009 > Ausnahmefehler aufgetreten. > > From: P:/OptigemBridge/obdev/80_sequel-tests/testwithmdb.rb @ line 34 : > > 29: puts > 30: puts "table.where(Boolean: true) -> #{boolean.sql}" > 31: puts boolean.all > 32: rescue Exception => e > 33: puts e > => 34: require 'pry';binding.pry > 35: end > [1] pry(main)> > > > could it be that ruby 1.9.3 is 32 bit while 2.6.0 is 64 bit? But then it > should not handle select statements without the where clause either. > Could be, it's hard to say. It does appear to be an issue with Ruby and not a Sequel-specific issue, though. Thanks, Jeremy -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to sequel-talk+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sequel-talk/a6558866-c156-4683-bfa6-d9cb30ed3c97%40googlegroups.com.
Re: How to handle boolean in literal where clauses with squel 5.28
Jeremy, thanks for your response. I created a minimal self-contained example in the attached file. I do not manage to perform it does work with ruby 1.9.3, having sequel 4.49 where it produces P:\OptigemBridge\obdev\80_sequel-tests>ruby testwithmdb.rb all data unfiltered --- {:ID=>6, :Numerical=>1, :Boolean=>true, :Text=>"\"Record 1 boolean true\""} {:ID=>7, :Numerical=>2, :Boolean=>false, :Text=>"\"Record 2 boolean false\""} playing with boolean table.where(Boolean: true) -> SELECT * FROM [Optigem] WHERE ([Boolean] = -1) {:ID=>6, :Numerical=>1, :Boolean=>true, :Text=>"\"Record 1 boolean true\""} P:\OptigemBridge\obdev\80_sequel-tests>gem which sequel P:/OptigemBridge/tools/ruby/lib/ruby/gems/1.9.1/gems/sequel-4.49.0/lib/sequel.rb sorry I do not manage to run it as bundle exec ruby testwithmdb.rb but with ruby 2.6 I get P:\OptigemBridge\obdev\80_sequel-tests>bundle exec ruby testwithmdb.rb all data unfiltered --- {:ID=>6, :Numerical=>1, :Boolean=>true, :Text=>"\"Record 1 boolean true\""} {:ID=>7, :Numerical=>2, :Boolean=>false, :Text=>"\"Record 2 boolean false\""} playing with boolean table.where(Boolean: true) -> SELECT * FROM [Optigem] WHERE ([Boolean] = -1) WIN32OLERuntimeError: (in OLE method `Execute': ) OLE error code:80040E14 in Microsoft JET Database Engine Interner Fehler bei OLE-Automatisierung HRESULT error code:0x80020009 Ausnahmefehler aufgetreten. From: P:/OptigemBridge/obdev/80_sequel-tests/testwithmdb.rb @ line 34 : 29: puts 30: puts "table.where(Boolean: true) -> #{boolean.sql}" 31: puts boolean.all 32: rescue Exception => e 33: puts e => 34: require 'pry';binding.pry 35: end [1] pry(main)> could it be that ruby 1.9.3 is 32 bit while 2.6.0 is 64 bit? But then it should not handle select statements without the where clause either. i have P:\OptigemBridge\obdev\80_sequel-tests>ruby -v ruby 2.6.5p114 (2019-10-01 revision 67812) [i386-mingw32] P:\OptigemBridge\obdev\80_sequel-tests>bundle exec gem which sequel P:/OptigemBridge/tools_26/Ruby26-x86/lib/ruby/gems/2.6.0/gems/sequel-4.49.0/lib/sequel.rb -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to sequel-talk+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sequel-talk/2acfe6c3-ce72-4ef9-a4fb-4aead508fda0%40googlegroups.com. <>
Re: How to handle boolean in literal where clauses with squel 5.28
On Sunday, January 12, 2020 at 1:53:23 PM UTC-8, Bernhard Weichel wrote: > > for your info, I tried to debug the line in question. rec[:"Nr"] yields a > numerical value. But i have the impression that the where clause only works > with strings, but not with numericals or booleans. > Incorrect, you can use a string, numeric, boolean as the value in a hash passed to Dataset#where (pretty much any value that Sequel can convert to SQL will work). You mentioned things didn't work with Sequel 4.49 either, but your backtrace shows use of Ruby 2.6, when you mentioned originally running your application with Ruby 1.9.3. Try using Sequel 4.49 with Ruby 1.9.3 and see if it works. If it works with Ruby 1.9.3 and not with Ruby 2.6, this is a Ruby issue and not a Sequel issue. If you continue to have problems, please create a minimal self-contained example showing the problem you are having, along with an SQL log. I do have access to Microsoft Access and should be able to debug the issue if you produce a minimal self-contained example. Thanks, Jeremy -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to sequel-talk+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sequel-talk/348cc1e5-ebf2-4d43-93f5-bf562388062e%40googlegroups.com.
Re: How to handle boolean in literal where clauses with squel 5.28
for your info, I tried to debug the line in question. rec[:"Nr"] yields a numerical value. But i have the impression that the where clause only works with strings, but not with numericals or booleans. From: P:/OptigemBridge/obdev/03_OptigemBridge/10_Sources/mediator_og_vr.rb @ line 133 : 128: rec[:job_de] = fields_bemerkung[:job_de] 129: rec[:job_en] = fields_bemerkung[:job_en] 130: 131: # process the visibility 132: visibility = VISIBLE_ROLES["#default"][1] => 133: require 'pry';binding.pry 134: visibility_rec = ogdb.person_category().where(:"Personen-Nr" => rec[:"Nr"]).all 135: visibility_rec = visibility_rec.select { |f| VISIBLE_ROLES.keys.include? f[:"Kurzname"] } # must be visible at all 136: visibility_rec = visibility_rec.map { |f| VISIBLE_ROLES[f[:"Kurzname"]] } # determin the visibility 137: visibility_rec = visibility_rec.sort_by { |f| f.first }.first # sort by the weight and take the strongest 138: visibility = visibility_rec[1] unless visibility_rec.nil? # take the name [1] pry(main)> [2] pry(main)> visibility_rec = ogdb.person_category().where(:"Personen-Nr" => rec[:"Nr"]).all Sequel::DatabaseError: WIN32OLERuntimeError: (in OLE method `Execute': ) OLE error code:80040E14 in Microsoft JET Database Engine Interner Fehler bei OLE-Automatisierung HRESULT error code:0x80020009 Ausnahmefehler aufgetreten. from P:/OptigemBridge/tools_26/Ruby26-x86/lib/ruby/gems/2.6.0/gems/sequel-4.49.0/lib/sequel/adapters/ado.rb:166:in `method_missing' Caused by WIN32OLERuntimeError: (in OLE method `Execute': ) OLE error code:80040E14 in Microsoft JET Database Engine Interner Fehler bei OLE-Automatisierung HRESULT error code:0x80020009 Ausnahmefehler aufgetreten. from P:/OptigemBridge/tools_26/Ruby26-x86/lib/ruby/gems/2.6.0/gems/sequel-4.49.0/lib/sequel/adapters/ado.rb:166:in `method_missing' [3] pry(main)> rec[:"Nr"] => 16644 [4] pry(main)> -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to sequel-talk+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sequel-talk/53b213fd-0598-49cf-9b2f-deba4deae75c%40googlegroups.com.
Re: How to handle boolean in literal where clauses with squel 5.28
Am Sonntag, 12. Januar 2020 20:43:51 UTC+1 schrieb Jeremy Evans: > > On Sunday, January 12, 2020 at 2:25:10 AM UTC-8, Bernhard Weichel wrote: >> >> Hi >> >> I have an application running with ruby 1.93 and sequel 4.18.0. I have >> statements like >> >> ogdb.projectcategorys.where('Zuordnungssperre = FALSE') >> >> No we want to upgrade the application to Ruby 2.6 and squel 5.28 the >> statement now throws: >> > > First upgrade to Sequel 4.49.0. Then fix all of the deprecation > warnings. Then upgrade to Sequel 5.28.0. > ok I will try this. > > The particular case you are running into is that you can no longer pass > normal Ruby strings as filter expressions as it makes it easy to introduce > security issues. You can use the auto_literal_strings extension that ships > with Sequel if you want to keep using the old behavior. > > Thanks, > I also tried to wrap the literal expression with Sequel.lit() but it did not work either. Nevertheless I tried with 4.49.0. there It breaks with the statement visibility_rec = ogdb.person_category().where(:"Personen-Nr" => rec[:"Nr"]).all which eventually trhows P:/OptigemBridge/tools_26/Ruby26-x86/lib/ruby/gems/2.6.0/gems/sequel-4.49.0/lib/sequel/adapters/ado.rb:166:in `method_missing': (in OLE method `Execute': ) (WIN32OLERuntimeError) OLE error code:80040E14 in Microsoft JET Database Engine Interner Fehler bei OLE-Automatisierung HRESULT error code:0x80020009 Jeremy > Th -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to sequel-talk+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sequel-talk/c24b745a-5876-4de3-8a54-ee0ef86cf0b3%40googlegroups.com.
Re: How to handle boolean in literal where clauses with squel 5.28
On Sunday, January 12, 2020 at 2:25:10 AM UTC-8, Bernhard Weichel wrote: > > Hi > > I have an application running with ruby 1.93 and sequel 4.18.0. I have > statements like > > ogdb.projectcategorys.where('Zuordnungssperre = FALSE') > > No we want to upgrade the application to Ruby 2.6 and squel 5.28 the > statement now throws: > First upgrade to Sequel 4.49.0. Then fix all of the deprecation warnings. Then upgrade to Sequel 5.28.0. The particular case you are running into is that you can no longer pass normal Ruby strings as filter expressions as it makes it easy to introduce security issues. You can use the auto_literal_strings extension that ships with Sequel if you want to keep using the old behavior. Thanks, Jeremy -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to sequel-talk+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sequel-talk/82865fd5-cf7b-467c-87ed-433fbb6dbdad%40googlegroups.com.
Re: How to handle boolean in literal where clauses with squel 5.28
It appeare to me that there are more incompatibilites between sequel 4.18 and 5.28 with respect to JET database engeine. I tried to overcome the problems before but then I get further internal errors P:/OptigemBridge/tools_26/Ruby26-x86/lib/ruby/gems/2.6.0/gems/sequel-5.28.0/lib/s equel::DatabaseError) OLE error code:80040E14 in Microsoft JET Database Engine Interner Fehler bei OLE-Automatisierung HRESULT error code:0x80020009 Ausnahmefehler aufgetreten. -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to sequel-talk+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sequel-talk/d7b5c838-deef-4e6c-a4bd-6501536870b2%40googlegroups.com.
How to handle boolean in literal where clauses with squel 5.28
Hi I have an application running with ruby 1.93 and sequel 4.18.0. I have statements like ogdb.projectcategorys.where('Zuordnungssperre = FALSE') No we want to upgrade the application to Ruby 2.6 and squel 5.28 the statement now throws: Sequel::Error: Invalid filter expression: "Zuordnungssperre = FALSE" from P:/OptigemBridge/tools_26/Ruby26-x86/lib/ruby/gems/2.6.0/gems/sequel-5.28.0/lib/sequel/dataset/query.rb:1311:in `filter_expr' I tried [19] pry(main)> ogdb.projectcategorys.where(Zuordnungssperre: false).all Sequel::DatabaseError: WIN32OLERuntimeError: (in OLE method `Execute': ) OLE error code:80040E14 in Microsoft JET Database Engine Interner Fehler bei OLE-Automatisierung HRESULT error code:0x80020009 Ausnahmefehler aufgetreten. from P:/OptigemBridge/tools_26/Ruby26-x86/lib/ruby/gems/2.6.0/gems/sequel-5.28.0/lib/sequel/adapters/ado.rb:162:in `method_missing' Caused by WIN32OLERuntimeError: (in OLE method `Execute': ) OLE error code:80040E14 in Microsoft JET Database Engine Interner Fehler bei OLE-Automatisierung HRESULT error code:0x80020009 Ausnahmefehler aufgetreten. from P:/OptigemBridge/tools_26/Ruby26-x86/lib/ruby/gems/2.6.0/gems/sequel-5.28.0/lib/sequel/adapters/ado.rb:162:in `method_missing' Any hint how to fix this would be highly appreciated. B. -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to sequel-talk+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sequel-talk/188dc959-81a7-4131-bf1a-aaa3124f6332%40googlegroups.com.