• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

how to debug SQL statements

 
Bartender
Posts: 1810
28
jQuery Netbeans IDE Eclipse IDE Firefox Browser MySQL Database Chrome Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Since the last upgrade of SpiceWorks, our reports are not working. It appears to be a problem with the date function in SQLite.

How can I test a function to see how it evaluates? For instance, the code contains this:



The report works if I put in a hard date, so I suspect that this is the problem child.

How can I see what these are evaluating to? I guess I'm looking for the SQL equivalent of System.out.println(), if such a thing exists.

I have WinSQL for browsing the database and running queries.
 
J. Kevin Robbins
Bartender
Posts: 1810
28
jQuery Netbeans IDE Eclipse IDE Firefox Browser MySQL Database Chrome Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
My google-fu is weak today. It turns out that the publisher is aware of this bug (since at least last December!!) We just avoided the update until a couple of weeks ago.

But for future reference, I'd still like to know how to test evaluate a SQL function like this. Can it be assigned to a var and then the var printed to the console?
 
Sheriff
Posts: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
In Microsoft SQL Server you could use PRINT and RAISERROR for this purpose. Don't know if SQLite has a similar function.

But if you just want to verify if a function is returning the value you expect, can't you simply use a SELECT statement, likeThis way you can verify if the returned value is meeting your expectations.
 
J. Kevin Robbins
Bartender
Posts: 1810
28
jQuery Netbeans IDE Eclipse IDE Firefox Browser MySQL Database Chrome Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I didn't try PRINT, but your select worked. It returned the dates I expected. That tells me that the problem is in the SpiceWorks report generator app.

I think I'll be writing my own web pages to replace these reports.

Thanks for the select tip. I only know enough SQL to be very dangerous. My head still spins when I see 'inner left join'.
 
Roel De Nijs
Sheriff
Posts: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

J. Kevin Robbins wrote:My head still spins when I see 'inner left join'.


Mine goes
 
J. Kevin Robbins
Bartender
Posts: 1810
28
jQuery Netbeans IDE Eclipse IDE Firefox Browser MySQL Database Chrome Linux
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Roel De Nijs wrote:

J. Kevin Robbins wrote:My head still spins when I see 'inner left join'.


Mine goes


I think you can get professional help for that.
 
Bartender
Posts: 598
26
Oracle Notepad Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

J. Kevin Robbins wrote:Since the last upgrade of SpiceWorks, our reports are not working. It appears to be a problem with the date function in SQLite.

How can I test a function to see how it evaluates? For instance, the code contains this:



The report works if I put in a hard date, so I suspect that this is the problem child.

How can I see what these are evaluating to? I guess I'm looking for the SQL equivalent of System.out.println(), if such a thing exists.

I have WinSQL for browsing the database and running queries.



I don't have SQL Server installed, but in general, you can just use the SELECT clause itself:



You can also create a recursive query that generates every possible DATE (either the short date, or give a reasonable limit) and use the WHERE clause to see which ones it pulls. Something like:

 
This tiny ad is wafer thin:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic