Skip to content

Unexpected Execute behaviour #1017

Open
Open
Unexpected Execute behaviour#1017
Labels
bugConfirmed bugs or reports that are very likely to be bugs.
Milestone
3.11.0 - Where we take over the world! :D

Description

@fatscally
fatscally
opened on May 23, 2017

Details for the issue

Unexpected behaviour.
Seems like the "Execute SQL" and "Execute current line" buttons are crossed.

I want to run this code

SELECT * FROM Transactions 
WHERE [Date] Between '2017-05-23 00:00:00:001' 
AND '2017-05-23 23:59:59'

If I select the first line "SELECT * FROM Transactions " and click the Execute SQL arrow >
This is the output: 4 rows returned in 0ms from: SELECT * FROM Transactions

but if I click the Execute Current Line the output is:

2 rows returned in 0ms from: SELECT * FROM Transactions 
WHERE [Date] Between '2017-05-23 00:00:00:001' 
AND '2017-05-23 23:59:59'

Useful extra information

I'm opening this issue because:

  • DB4S is crashing
  • DB4S has a bug
  • DB4S needs a feature
  • DB4S has another problem

I'm using DB4S on:

  • Windows: ( version: ___ )
  • Linux: ( distro: ___ )
  • Mac OS: ( version: ___ )
  • Other: ___

I'm using DB4S version:

  • 3.9.1
  • 3.9.0
  • Other: ___

I have also:

Activity

  1. justinclift commented on May 23, 2017

    @justinclift
    justinclift
    on May 23, 2017
    Member

    Just to check, which version is this happening on? Asking because we've changed the code for the Execute SQL pane a lot since the 3.9.1 release. I'm pretty sure the behaviour will have changed since then.

    If the above is happening with our recent nightly releases though (found here), we should definitely look into it. 😄

  2. fatscally commented on May 23, 2017

    @fatscally
    fatscally
    on May 23, 2017 via email
    Author

  3. fatscally commented on May 23, 2017

    @fatscally
    fatscally
    on May 23, 2017 via email
    Author

  4. justinclift commented on May 23, 2017

    @justinclift
    justinclift
    on May 23, 2017
    Member

    Thanks, that's useful. Would you be ok to try the latest nightly build and see if it's happening there as well?

        https://nightlies.sqlitebrowser.org/latest/

    In theory (!), it shouldn't be a problem any more. 😁

  5. fatscally commented on May 23, 2017

    @fatscally
    fatscally
    on May 23, 2017 via email
    Author

  6. fatscally commented on May 23, 2017

    @fatscally
    fatscally
    on May 23, 2017 via email
    Author

  7. fatscally commented on May 23, 2017

    @fatscally
    fatscally
    on May 23, 2017 via email
    Author
  8. justinclift
    added
    bugConfirmed bugs or reports that are very likely to be bugs.
    on May 23, 2017

  9. justinclift commented on May 23, 2017

    @justinclift
    justinclift
    on May 23, 2017
    Member

    Damn, I could have sworn we fixed this a while back. We'll need to look into it again before the next release.

    Thanks for pointing it out @fatscally. 😄

  10. justinclift
    added this to the 3.10.0 - A Rubber Cactus Has Many Uses milestone on May 23, 2017

  11. fatscally commented on May 23, 2017

    @fatscally
    fatscally
    on May 23, 2017 via email
    Author

  12. justinclift commented on May 23, 2017

    @justinclift
    justinclift
    on May 23, 2017
    Member

    Thanks. 😄

    Yeah, everything has bugs, but at least we're somewhat more approachable. 😉

  13. chrisjlocke commented on May 24, 2017

    @chrisjlocke
    chrisjlocke
    on May 24, 2017
    Member

    Just a thought (haven't fully tested it, but wanted to confirm...) you mention "but highlight just the first row". Theoretically, no highlighting is necessary, and it might be that thats giving the 'false information'...
    I know this was working semi-recently, as it was me that highlighted the previous quirks with it.

  14. chrisjlocke commented on May 24, 2017

    @chrisjlocke
    chrisjlocke
    on May 24, 2017
    Member

    OK, so 'Execute SQL' (or F5) will execute the sql statements in the window, while 'Execute current line' is technically more 'Execute current statement'. A subtle, but obvious difference.

    The highlighting is important in that if some SQL is highlighted (ie, part of a statement) and 'Execute SQL' (or F5) is pressed, then only that SQL is executed. Highlighting has no effect on the 'Execute current line' button.

    The SQL window can hold multiple statements, eg:

    select * from invoices
where invoiceNumber=100;

select * from invoices;

    So the 'Execute Current Line' (if the cursor was on the 'where invoiceNumber=100' line) would only execute the top SQL statement, not the bottom one.

    I guess the button labels are slightly misleading...

    Personally, I've found it more beneficial to use -- comments, so if I didn't want the 'where' statement, its easier (and clearer) to set the SQL as

    select * from invoices
--where invoiceNumber=100;

    ...but that doesn't negate the fact that the buttons aren't accurately describing what they do....

  15. fatscally commented on May 24, 2017

    @fatscally
    fatscally
    on May 24, 2017 via email
    Author

  16. fatscally commented on May 24, 2017

    @fatscally
    fatscally
    on May 24, 2017 via email
    Author

  17. chrisjlocke commented on May 24, 2017

    @chrisjlocke
    chrisjlocke
    on May 24, 2017
    Member

    If I put this code in, it will fail

    Technically, its not valid SQL. DB4S will 'merge' all lines (as an SQL statement can be split over multiple lines, eg:

    select * 
from Tags 
where 
Id <= 2

    So DB4S is squishing your statements together and forming this:

    select * from Tags where Id <= 2 select * from Tags where Id >= 3

    Slap a semi colon on the end of each statement, and DB4S will know where one statement ends, and another begins. Other database programs might be more 'forgiving', but Db4S is quite strict! ;)

    1. Again, if you add the semi colon, Shift+F5 will work. Honest.

    image

    I pressed Shift+F5 on the middle statement, as it showed 12 rows. If I'd have pressed F5, it would have shown 1 row.

  18. fatscally commented on May 24, 2017

    @fatscally
    fatscally
    on May 24, 2017 via email
    Author

  19. chrisjlocke commented on May 24, 2017

    @chrisjlocke
    chrisjlocke
    on May 24, 2017
    Member

    Some programs 'detect' where the semi colon should go, others don't. I use SQL Studio and that gets confused if I don't add semi colons. You can't 'assume' that lines beginning with 'select' is a new statement, as you can have multiple 'select' statements in a select statement. The semi colon just explicitly defines where you want the ending to be.

    when I'm scripting outside of C#

    Welcome fellow .Net-er. I program mainly in VB, but use both SQLite and SQL Server. 👋

  20. fatscally commented on May 24, 2017

    @fatscally
    fatscally
    on May 24, 2017 via email
    Author

  21. chrisjlocke commented on May 24, 2017

    @chrisjlocke
    chrisjlocke
    on May 24, 2017
    Member

    Right now I want to use C# + Xamarin to create a mobile app across the 3 platforms

    Ditto. Well, I'd love to create some Android apps. I work in a manufacturing business, so to create a small app where someone could interact with a database 'somewhere' would be useful.
    99.99% of my coding is for Windows applications (WinForms, rather than WPF). I use SQLite for most of the databases (seems to work OK in a multi-user environment on a server, even though they say 'don't do that!'...) and SQL Server for bigger projects.
    I code in SharpDevelop, as thats a 40 MB download, compared to Visual Studio, which is just nuts - someone said 50 GB offline download, but I guess thats with 'everything'.

    Think we've gone off topic now! ;) hehe! Anyway - welcome to the DB4S group! 😸

  22. fatscally commented on May 24, 2017

    @fatscally
    fatscally
    on May 24, 2017 via email
    Author

  23. chrisjlocke commented on May 25, 2017

    @chrisjlocke
    chrisjlocke
    on May 25, 2017
    Member

    Custom keys can't be set yet, and changing the keys may not be possible, as Ctrl+E currently shows/hides the 'Edit Database Cell' tab.

  24. justinclift commented on May 25, 2017

    @justinclift
    justinclift
    on May 25, 2017
    Member

    Yeah, we do have an outstanding item for adding some kind of key binding change. But, it's not a small piece of work and it's probably not something we'll get done in the near future. 😇

  25. justinclift
    modified the milestones: 3.11.0 - Where we take over the world! :D, 3.10.0 - A Rubber Cactus Has Many Uses on Aug 20, 2017
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugConfirmed bugs or reports that are very likely to be bugs.

    Type

    No type

    Projects

    No projects

      Milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions