Solved

Reading values from vfoxpro in powershell

Posted on 2011-03-13
6
1,846 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
ActiveSync Report 2007 3 19
Powershell SMO script not working. 18 101
Powershell 16 32
how to run vb script in powershell 2 17
This is a PowerShell web interface I use to manage some task as a network administrator. Clicking an action button on the left frame will display a form in the middle frame to input some data in textboxes, process this data in PowerShell and display…
This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

758 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

18 Experts available now in Live!

Get 1:1 Help Now