returning results of a database query in a web service

  • Thread starter Thread starter cj
  • Start date Start date
C

cj

I wrote this code in a windows app and it works:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim ds As New DataSet

Dim myDbConnection As New OdbcConnection("Driver={Microsoft
Visual FoxPro Driver};" & _
"SourceType=DBF;" & _

"SourceDB=i:\probill\;" & _
"Exclusive=No;" & _
"Collate=Machine;" & _
"NULL=NO;" & _
"DELETED=NO;" & _
"BACKGROUNDFETCH=YES")
Dim mydbcommand As New OdbcCommand("select flag from act_frau
where act = '" + TextBox1.Text.Trim + "'", myDbConnection)
myDbConnection.Open()
Dim results As String = mydbcommand.ExecuteScalar()
myDbConnection.Close()
MessageBox.Show(results)
End Sub


So why doesn't this work:

<WebMethod()> _
Public Function CashOnly(ByVal act String) As String
Dim ds As New DataSet
Dim myOdbcConnection As New OdbcConnection("Driver={Microsoft
Visual FoxPro Driver};" + _
"SourceType=DBF;" + _
"SourceDB=i:\probill\;" + _
"Exclusive=No;" + _
"Collate=Machine;" + _
"NULL=NO;" + _
"DELETED=NO;" + _
"BACKGROUNDFETCH=YES")

Dim myOdbcCommand As New OdbcCommand("select flag from act_frau
where act = '" + act.Trim + "'", myOdbcConnection)

myOdbcConnection.Open()
Dim results As String = myOdbcCommand.ExecuteScalar()
myOdbcConnection.Close()

Return results
End Function

I get:

System.Data.Odbc.OdbcException: ERROR [42S02] [Microsoft][ODBC Visual
FoxPro Driver]File 'act_frau.dbf' does not exist.
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle,
RetCode retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior
behavior, String method, Boolean needReader, Object[] methodArguments,
SQL_API odbcApiMethod)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior
behavior, String method, Boolean needReader)
at System.Data.Odbc.OdbcCommand.ExecuteScalar()
at TestWebService1.PaymentService1.CashOnly(String act) in
C:\Documents and Settings\cj\My Documents\Visual Studio
2008\Projects\TestWebService1\TestWebService1\PaymentService1.asmx.vb:line
47
 
cj,

maybe the odbc driver is not on the server, but why are you using this
incomplete provider on a server.

As I was you I would visit the FoxPro newsgroup. I know that there are much
more reliable solutions and they know it.

Just the way I would solve your problem.

Cor
 
¤ So why doesn't this work:
¤
¤ <WebMethod()> _
¤ Public Function CashOnly(ByVal act String) As String
¤ Dim ds As New DataSet
¤ Dim myOdbcConnection As New OdbcConnection("Driver={Microsoft
¤ Visual FoxPro Driver};" + _
¤ "SourceType=DBF;" + _
¤ "SourceDB=i:\probill\;" + _
¤ "Exclusive=No;" + _
¤ "Collate=Machine;" + _
¤ "NULL=NO;" + _
¤ "DELETED=NO;" + _
¤ "BACKGROUNDFETCH=YES")
¤
¤ Dim myOdbcCommand As New OdbcCommand("select flag from act_frau
¤ where act = '" + act.Trim + "'", myOdbcConnection)
¤
¤ myOdbcConnection.Open()
¤ Dim results As String = myOdbcCommand.ExecuteScalar()
¤ myOdbcConnection.Close()
¤
¤ Return results
¤ End Function
¤
¤ I get:
¤
¤ System.Data.Odbc.OdbcException: ERROR [42S02] [Microsoft][ODBC Visual
¤ FoxPro Driver]File 'act_frau.dbf' does not exist.
¤ at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle,
¤ RetCode retcode)
¤ at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior
¤ behavior, String method, Boolean needReader, Object[] methodArguments,
¤ SQL_API odbcApiMethod)
¤ at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior
¤ behavior, String method, Boolean needReader)
¤ at System.Data.Odbc.OdbcCommand.ExecuteScalar()
¤ at TestWebService1.PaymentService1.CashOnly(String act) in
¤ C:\Documents and Settings\cj\My Documents\Visual Studio
¤ 2008\Projects\TestWebService1\TestWebService1\PaymentService1.asmx.vb:line
¤ 47

Where is the i drive? Is this a mapped drive letter to a network resource or is it a drive local to
the web server?


Paul
~~~~
Microsoft MVP (Visual Basic)
 
i: is a network drive. I was using i: in the first example that worked.
I don't understand why that code will work in the button click of a
windows app and not as part of my web service. I'm publishing the web
service to localhost.
 
¤ i: is a network drive. I was using i: in the first example that worked.
¤ I don't understand why that code will work in the button click of a
¤ windows app and not as part of my web service. I'm publishing the web
¤ service to localhost.
¤

The web service runs under a non interactive account and process so it probably can't see the mapped
network drive. Try using a UNC path name instead of mapped drive letter to start. After that you may
have some security issues to resolve since your accessing a file over the network.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Well, I think your onto something here. I switched it from
"SourceDB=i:\probill\;"
to
"SourceDB=\\fileserver\i\probill\;"

but unfortunately I still get the same thing. So I guess there are some
more settings. Any idea what to look for and where to look for them?

To verify a few things I tried the web service using a copy of the
database on my local d: drive and it works. I also tried
\\fileserver\i\probill in the windows app and that works,
which pretty much confirms some security setting I think.

P.S. In case you haven't read my other post yet. I'm now known as cj2
instead of cj.
 
try making

"SourceDB=\\fileserver\i\probill\;"

a shared directory on the server and change the above to

"SourceDB=\\fileserver\probill\;"

The drive letter "i" may still be causing you a problem.


Rich
 
Actually, that i is a directory on the fileserver. I think once upon a
time f: and i: were on different drives and as you probably know things
get used to running on specific drives and referring to things by drive
letter. So, when f: and i: moved to this fileserver they were put in
seperate f and i directories and each user's pc maps f: to fileserver\f
and i: to fileserver\i.

in windows i: is mapped to \\fileserver\i
 
¤ Well, I think your onto something here. I switched it from
¤ "SourceDB=i:\probill\;"
¤ to
¤ "SourceDB=\\fileserver\i\probill\;"
¤
¤ but unfortunately I still get the same thing. So I guess there are some
¤ more settings. Any idea what to look for and where to look for them?
¤

I think you've probably run into the security issue. The web service runs under either the
NetworkService (Windows 2003 or higher) or ASPNET (2000 or XP). These are local accounts which
wouldn't have access to the network resource by default.

I'm not as familiar with web service security but I believe it's the same as that implemented for a
web application. See if the following helps:

Common security issues when you access remote resources from ASP.NET applications
http://support.microsoft.com/kb/891031


Paul
~~~~
Microsoft MVP (Visual Basic)
 
I found it!!! One simple little change.

Change "BACKGROUNDFETCH=YES") to "BACKGROUNDFETCH=NO")

Now why????????? I've been Googling but haven't come up with what
backgroundfetch does yet. Anyone know? I asked on the VFP board too.
 
¤ I found it!!! One simple little change.
¤
¤ Change "BACKGROUNDFETCH=YES") to "BACKGROUNDFETCH=NO")
¤
¤ Now why????????? I've been Googling but haven't come up with what
¤ backgroundfetch does yet. Anyone know? I asked on the VFP board too.
¤

Beats me, but I would suspect that one of FoxPro people would know.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
AFAIK a web service has to run on a web server and I can't imagine that
is where we should be storing these massive foxpro databases that are
part of our legacy system. With all the technology these days surely
it's acceptable practice for a web service running on a web server to
refer to files located on a central file server.

Anyway, I'm not really a server person and don't understand all about
domains etc but if you have any hints on how to get a web server to
access files on a fileserver faster I'm all ears. If I hear something
useful I'll pass it on to the folks that do that stuff around here.

Along those lines, I find it a bit strange that I had to add <identity
impersonate="true" userName="myco\cj" password="pass"/> to my web
service's web.config file for it to work too. Currently I'm running it
on localhost but I'm told I'll still need this when I go to publish it
to webserver1. You stated that "Impersonation is a relatively expensive
solution". If you mean that it slows things down then I'd be interested
in hearing how to speed them up. Things aren't as fast as I'd like but
do work. Again, if it is not a programming change I'll have to pass
that info on to folks here who might better understand.
 
Back
Top