• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

how to debug SQL statements

 
J. Kevin Robbins
Bartender
Pie
Posts: 1801
28
Chrome Eclipse IDE Firefox Browser jQuery Linux MySQL Database Netbeans IDE
  • Mark post as helpful
  • send pies
  • 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
Pie
Posts: 1801
28
Chrome Eclipse IDE Firefox Browser jQuery Linux MySQL Database Netbeans IDE
  • Mark post as helpful
  • send pies
  • 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?
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • 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
Pie
Posts: 1801
28
Chrome Eclipse IDE Firefox Browser jQuery Linux MySQL Database Netbeans IDE
  • Mark post as helpful
  • send pies
  • 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: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • 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
Pie
Posts: 1801
28
Chrome Eclipse IDE Firefox Browser jQuery Linux MySQL Database Netbeans IDE
  • Likes 1
  • Mark post as helpful
  • send pies
  • 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.
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • 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:

 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic