Solved

Reading values from vfoxpro in powershell

Posted on 2011-03-13
6
1,885 Views
Last Modified: 2012-05-11
I'm not a programmer by trade, so please don't rough me up too badly!  I'm needing to pull values from a visual fox pro database (from a .dbc container and .dbf table files).  I've managed to pull together some code from other sources around the web, and I'm very close.  I'm certain this is a knowledge gap with my "thrown to the wolves" education I'm going through now.  

Essentially the problem is that I can display the data I want in powershell, and I can grab the number of items (although, I'm certain this is due to my inexperience with the syntax).  For example, if my sql calls for all deliveries with a date of today, I can pull the count of those records.  The problem is when I want to sum the miles for each drop off.  I can run a sql / vfoxpro query against the db and get a table view, however, I cannot for the life of me extract the VALUE of the table data, only the record count.

Here's what I've got:

$ConnString = "Provider=vfpoledb.1;Data Source=C:\dispatch\dispatch.dbc;Collating Sequence=machine;"
$Conn = new-object System.Data.OleDb.OleDbConnection($connString) 
$conn.open() 
$sql = "select ((sum(endmile)) - (sum(pickupmileage))) as MilesToday from dispatch where date = DTOT(date())"
$obj = new-object System.object

#Total Miles Today Query

$cmd = new-object System.Data.OleDb.OleDbCommand($sql,$Conn) 
$da = new-object System.Data.OleDb.OleDbDataAdapter($cmd) 
$dt = new-object System.Data.dataTable  
$da.fill($dt) 
$dt

Open in new window


The output for that gives:

1

                                                                                 milestoday
                                                                                 ----------
                                                                                         16

What I really want to do is to pass the value '16' via:
$items = $da.fill($dt)

add-member -inputobject $obj -membertype NoteProperty -Name TotalMiles -value $items
Write-output $obj

Open in new window


Unmodified, I get the number of records.  This code works well for calling the number of deliveries with "peaches" for example, but I can't pass any of the data, just the row counts by my code.  What am I missing?

Thanks for any help you can provide!
0
Comment
Question by:safenetworksolutions
  • 3
  • 3
6 Comments
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 35125971
Take a look at the Fill method of the dataadapter: http://msdn.microsoft.com/en-us/library/377a8x4t.aspx

The Fill method fills data into the datatable ($dt in your case), the return value is the number of records returned or updated. Therfore there is nothing more in $items than the record count.

Next error: The Fill method of a dataadapter fills a dataset, not a datatable. So you'd rather need a $ds = new-object System.DataSet, not a datatable.

Bye, Olaf.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 35126027
Searching a bit more...

Ok, there also is a Fill(datatable) here: http://msdn.microsoft.com/en-us/library/905keexk.aspx
So your code should work and fill $dt. Still the $items is only the number of records.

$dt.Rows[0]["MilesToday"] should give you the miles today. Maybe a 1 instead of 0, but I think the .net DataRows collection is 0 based.

Bye, Olaf.
0
 

Author Comment

by:safenetworksolutions
ID: 35159045
Olaf -

Thank you SO much for the response.  I will try your suggestion tonight when I am back at the client site.  Again, I really appreciate your input!!
0
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 
LVL 29

Accepted Solution

by:
Olaf Doschke earned 500 total points
ID: 35238656
It's been almost two weeks. Whats up?

Bye, Olaf.
0
 

Author Comment

by:safenetworksolutions
ID: 35352538
Sorry for the delay - Had numerous setbacks as 1 routine needed exclusive access to the DB.  We've overcome those issues -

Your solution worked perfectly!

Here's the revised code:

 
$ConnString = "Provider=vfpoledb.1;Data Source=C:\dispatch\dispatch.dbc;Collating Sequence=machine;"
$Conn = new-object System.Data.OleDb.OleDbConnection($connString) 
$conn.open() 

$sql = "select ((sum(endmile)) - (sum(pickupmileage))) as MilesToday from dispatch where date = DTOT(date())"
$obj = new-object System.object

#Total Miles Today Query

$cmd = new-object System.Data.OleDb.OleDbCommand($sql,$Conn) 
$da = new-object System.Data.OleDb.OleDbDataAdapter($cmd) 
$dt = new-object System.Data.dataTable  
$da.fill($dt)
$items = $dt.Rows[0]["MilesToday"]

Open in new window


Thanks so much!  I appreciate the input!
0
 

Author Closing Comment

by:safenetworksolutions
ID: 35352541
Thanks again!  Really appreciate the input!
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

The article will show you how you can maintain a simple logfile of all Startup and Shutdown events on Windows servers and desktops with PowerShell. The script can be easily adapted into doing more like gracefully silencing/updating your monitoring s…
"Migrate" an SMTP relay receive connector to a new server using info from an old server.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

910 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now