Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Return results from Sybase DB Query with Powershell

Posted on 2014-01-07
7
1,666 Views
Last Modified: 2014-10-06
I hae the following powershell script I am working on... Basically I have utilized the Sybase ASE Client Assembly, Stored my connection info and dates vars . I can open the connection to the server and as I understand it run the query. However the issue I am having now is how to input the results of the query into a variable. What I am looking to do is run the query and store the value returned in a var called $results.
Now to the issue... Should I be using  Invoke-Command to fire off the query or something else. I have seen a couple of different ways (for MS SQL) of doing it but not sure the best route.
#Add Sybase ASE Client Assembly
  Add-Type -AssemblyName "Sybase.AdoNet2.AseClient, Version=2.157.701.0, Culture=neutral, PublicKeyToken=9x9x9x9x9x9x9x9x9x9e"
  
  #database and  Connection vars
  $user = "user"
  $pwd = "99999999"
  $db = "Reporting"
  $beginDate = (Get-Date).AddDays(-31)
  $endDate = (Get-Date).AddDays(-2)
  $WebUsageQuery="select count( * )  from Reporting..WebUsage w where w.LogDate between '$beginDate' and '$endDate'"

  write-host $WebUsageQuery  
  #Create Connection String 
  $SybConn = New-Object Sybase.Data.AseClient.AseConnection
  $SybConn.ConnectionString = "Data Source='sybserverA';Port=5800;Database='$db';UID='$user';PWD='$pwd';"
  $SybConn.Open()

  $sybCmd = New-Object Sybase.Data.AseClient.AseDataAdapter
  $sybCmd.SelectCommand =  "$WebUsageQuery"
  $sybCmd.SelectCommand.Connection = $SybConn

  Write-Host $sybCmd and $SybConn

  $sybAdapter = New-Object Sybase.Data.AseClient.AseDataAdapter
  $sybAdapter.SelectCommand = "$sybCmd"
  
  $DataSet = New-Object Sybase.Data.AseClient.AseDataAdapter
  #$sybAdapter.Fill($DataSet)

  $SybConn.Close()

Open in new window

0
Comment
Question by:AjarnJonesy
  • 2
7 Comments
 
LVL 18

Expert Comment

by:x-men
ID: 39764878
$x = MyPSScript.ps1
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 39767423
Is that a simplificated example only, or your real query? The approach depends on how much data you expect to get back. For single value we can use something much more simple than for a more generic query. The DataAdapter/DataSet is needed only for "complex" stuff, but of course you can use it here too. Just fill the DataSet var, and then dump the first table of it either to the output or into another var:
  $DataSet = New-Object Sybase.Data.AseClient.AseDataAdapter
  $sybAdapter.Fill($DataSet)
  $result = $DataSet.Tables[0]

Open in new window

0
 

Author Comment

by:AjarnJonesy
ID: 39768322
Qlemo
Thanks for the response.
that is the complete query, all I am looking to do is get a row count from a specific date range. Once I get that working I am going to basically kick it out with a simple
If less than 200,000 rows for the specified range then fire off an email with specific verbage. This is just used to check a row count for the date range then send an automated email if the row count is below what the expected inserts are supposed to be.. I will tinker around with what you have posted and see if I can get it working..
Thanks
0
 
LVL 69

Accepted Solution

by:
Qlemo earned 500 total points
ID: 39904195
I reckon this should give you a start:
#Add Sybase ASE Client Assembly
Add-Type -AssemblyName "Sybase.AdoNet2.AseClient"

#database and  Connection vars
$user = "user"
$pwd = "99999999"
$db = "Reporting"
$beginDate = (Get-Date).AddDays(-31)
$endDate = (Get-Date).AddDays(-2)
$WebUsageQuery="select count( * )  from Reporting..WebUsage w where w.LogDate between '$beginDate' and '$endDate'"

$SybConn = New-Object Sybase.Data.AseClient.AseConnection
$SybConn.ConnectionString = "Data Source='sybserverA';Port=5800;Database='$db';UID='$user';PWD='$pwd';"
$SybConn.Open()

[Sybase.Data.AseClient.AseCommand] $sybCmd = $SybConn.CreateCommand();
$sybCmd.CommandText =  "$WebUsageQuery"
$count = $sybCmd.ExecuteScalar()

if ($count -lt 200000)
{
  Send-MailMessage -SmtpServer mx.Domain.Com -From Me@Domain.Com -To Me@Domain.Com -Subject "Reporting warning with $count rows"
}

$SybConn.Close()

Open in new window

0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
Set OWA language and time zone in Exchange for individuals, all users or per database.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question