O'Reilly Forums: Linqpad Sql And Print - O'Reilly Forums

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Linqpad Sql And Print Rate Topic: -----

#1 User is offline   lehel 

  • New Member
  • Pip
  • Group: Members
  • Posts: 8
  • Joined: 18-November 09

Posted 09 September 2011 - 05:39 AM

I have multiple SQL commands in a LINQPad file for checking errors in the database.
I would like to show some comments between the Result Sets returned by those commands.
I figured out that I could use the PRINT command to print some text to the output, but even though my PRINT commands are between the select statements, all of them are printed out on the top and all SELECT results are coming after.
Is there any other command which I could use to print some comments between the query results? Or give some titles to the Result Sets. Otherwise I have to count all result sets and count my code section to see which one referred to which result.
(Usually my result sets return 0 rows if there is no error, and return the result if there are errors, so I have to check which was the code referring to it, and I have my comments there in my code section, but it would be much easier to see the comment printed beside the result)

Thank you,
Steven
0

#2 User is offline   sgmoore 

  • Active Member
  • PipPip
  • Group: Members
  • Posts: 36
  • Joined: 20-November 08

Posted 13 September 2011 - 10:53 AM

There are a number of ways.

You can add a 'go' before your print. eg

Print 'Stage 1'
Select ....
Go
Print 'Stage 2'
select  ...
Go
Print 'Stage 3'
select  ...


You can also use
 Select 'Status Message' . 

However, this will take up three lines - as in
   Result Set (1 item)  
   Column1 
   Status Message


If you are doing a select statement and are only interested in the non-empty results, you can add an extra static column , for example

select 'Check Customer Balances'  as Routine, Code, Name 
from customer where Balance > 100000000


if there are no customers with balances over 100million, you just get a line saying
 Result Set (0 items)


otherwise you get something like
Result Set (1 item)  
Routine                     Code    Name 
Check Customer Balances     BLOGGS1 Joe Bloggs


so the first column will show you which routine has failed.
0

#3 User is offline   lehel 

  • New Member
  • Pip
  • Group: Members
  • Posts: 8
  • Joined: 18-November 09

Posted 15 September 2011 - 06:54 AM

Thank you very much.
Using the GO statement was enough.
I found this interesting but couldn't understand exactly:
> select 'Check Customer Balances' as Routine, Code, Name from customer where Balance > 100000000

What is Code in this case?
Or in your example, what is Code = BLOGGS1 ? Is it a stored procedure?
I don't really want to create stored procedures which stay in the database, only want to run some scripts for analyzing the DB, or maybe some temprorary stored procedures which are valid until the script runs.

Thanks,
Steven
0

#4 User is offline   sgmoore 

  • Active Member
  • PipPip
  • Group: Members
  • Posts: 36
  • Joined: 20-November 08

Posted 15 September 2011 - 11:19 AM

View Postlehel, on 15 September 2011 - 06:54 AM, said:


I found this interesting but couldn't understand exactly:
> select 'Check Customer Balances' as Routine, Code, Name from customer where Balance > 100000000

What is Code in this case?
Or in your example, what is Code = BLOGGS1 ? Is it a stored procedure?


Code is a column in the customer table, Bloggs is just an example customer code.

The sql statement is returning two columns (code and name) from the customer table in order to identify customers with balances outside normal parameters. (It also returns a column of static text to identity the routine or test that is running)

In this example, the query returns the details of just one customer whose name is 'Joe Bloggs' and his Code (or Account Number/ID) happens to be BLOGGS1.
0

#5 User is offline   lehel 

  • New Member
  • Pip
  • Group: Members
  • Posts: 8
  • Joined: 18-November 09

Posted 28 September 2011 - 06:48 PM

Using the GO statement looks good, but I tried to use it in a script where I have local variables and those variables are not valid anymore after the GO statement.

For example:
SET @ID = '4638813'

Print 'Profile information:'
SELECT ID, Full_Name, Full_Address FROM User_Profile
WHERE ID = @ID
GO

After this I cannot use more select statements with the same @ID variable.

Is there a way that I could use some kind of variables and print a text + show the select results?

Your 2nd solution (Select 'Status Message') looks very busy, and hard to see the real information.

The 3rd solution is fine, but in case is 0 results, it doesn't show the text either.

I would prefer the 1st solution (print, go) but it would be nice to be able to use some kind of variables too.

Thank you,
Steven
0

#6 User is offline   JoeAlbahari 

  • Super Veteran Member
  • PipPipPipPipPipPipPipPipPipPipPip
  • Group: Members
  • Posts: 529
  • Joined: 15-February 08
  • Gender:Male
  • Location:Perth, Australia

Posted 04 October 2011 - 12:12 AM

I've figured out to fix this in LINQPad - the next beta due out in a week or so will have this mod.

Cheers
Joe
0

#7 User is offline   lehel 

  • New Member
  • Pip
  • Group: Members
  • Posts: 8
  • Joined: 18-November 09

Posted 27 October 2011 - 06:16 PM

Hi Joe,

Have you figured out some solution for this? Are you using some kind of LINQPad system variables?
Does the newest version of LINQPad has the solution?

Thanks,
Steven
0

#8 User is offline   JoeAlbahari 

  • Super Veteran Member
  • PipPipPipPipPipPipPipPipPipPipPip
  • Group: Members
  • Posts: 529
  • Joined: 15-February 08
  • Gender:Male
  • Location:Perth, Australia

Posted 02 November 2011 - 11:02 PM

This is now in the latest beta:
http://www.linqpad.net/beta.aspx

Joe
0

#9 User is offline   lehel 

  • New Member
  • Pip
  • Group: Members
  • Posts: 8
  • Joined: 18-November 09

Posted 03 November 2011 - 05:05 PM

Hi Joe,

I see that the beta has lots of new features, and you mentioned that you found a fix for this problem in that.
What is the solution for this problem in the newest beta?
The variables with @ you still cannot use after you used a GO statement.

Thanks,
Steven
0

#10 User is offline   JoeAlbahari 

  • Super Veteran Member
  • PipPipPipPipPipPipPipPipPipPipPip
  • Group: Members
  • Posts: 529
  • Joined: 15-February 08
  • Gender:Male
  • Location:Perth, Australia

Posted 05 November 2011 - 08:59 PM

You don't need a GO statement - just do this:

print 'Stage 1'
select ....
print 'Stage 2'
select ...
print 'Stage 3'
select ...

and LINQPad will display everything in the correct order.
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users