simple procedure question

Hi,

I'm new to Foxpro, and I'm trying to create a stored procedure that returns
a set of records. The following does not return records:

procedure GetRecords
select * from customer
endproc

Do I need to create a cursor or something? If so, what does that look like?

tia,

John

Dan
Fri Jan 30 16:30:32 CST 2004

If you run that stored proc as is, it should show you a browse window with
the customer records. Is it?

You generally need an output target for SELECT statements.

Select * from Customer into cursor csrCustomer

Dan

John wrote:
> Hi,
>
> I'm new to Foxpro, and I'm trying to create a stored procedure that
> returns a set of records. The following does not return records:
>
> procedure GetRecords
> select * from customer
> endproc
>
> Do I need to create a cursor or something? If so, what does that
> look like?
>
> tia,
>
> John

John
Sat Jan 31 08:26:25 CST 2004

I'd like to call this stored procedure from outside FoxPro using ADO or
ADO.Net. When I do so, no records are returned. If using a cursor is the
way to do this, how do I expose the cursor to the caller?

Thanks,

John

"Dan Freeman" <spam@microsoft.com> wrote in message
news:OcEALC45DHA.2348@TK2MSFTNGP10.phx.gbl...
> If you run that stored proc as is, it should show you a browse window with
> the customer records. Is it?
>
> You generally need an output target for SELECT statements.
>
> Select * from Customer into cursor csrCustomer
>
> Dan
>

Wolfgang
Sat Jan 31 09:04:08 CST 2004

Hi John!

Do you use the OLE-Driver vor VFP?

--
_________________

MFG
Wolfgang Schmale

MS Visual FoxPro MVP

--------------------------------
"John" <john@spam.com> schrieb im Newsbeitrag
news:evAEuYA6DHA.1948@TK2MSFTNGP12.phx.gbl...
> I'd like to call this stored procedure from outside FoxPro using ADO or
> ADO.Net. When I do so, no records are returned. If using a cursor is the
> way to do this, how do I expose the cursor to the caller?
>
> Thanks,
>
> John
>
> "Dan Freeman" <spam@microsoft.com> wrote in message
> news:OcEALC45DHA.2348@TK2MSFTNGP10.phx.gbl...
> > If you run that stored proc as is, it should show you a browse window
with
> > the customer records. Is it?
> >
> > You generally need an output target for SELECT statements.
> >
> > Select * from Customer into cursor csrCustomer
> >
> > Dan
> >
>
>

John
Sat Jan 31 09:48:17 CST 2004

Yes, it's the MS OLE DB Provider for Visual FoxPro (VFPOLEDB.1)

John

"Wolfgang Schmale" <w_schmale@wsnews.de> wrote in message
news:%237qEytA6DHA.2380@TK2MSFTNGP10.phx.gbl...
> Hi John!
>
> Do you use the OLE-Driver vor VFP?
>
> --
> _________________
>
> MFG
> Wolfgang Schmale
>
> MS Visual FoxPro MVP

Wolfgang
Sat Jan 31 10:31:44 CST 2004

Hi John!

I'm not the ADO.NET freak. But AFAIK you should return a data object and not
a cursor file to the caling function.
I would try:

Select * from customers into cursor myCustomers
scan
scatter memo to name oCustomers additive
endscan
return oCustomers

--
_________________

MFG
Wolfgang Schmale

MS Visual FoxPro MVP

--------------------------------
"John" <john@spam.com> schrieb im Newsbeitrag
news:eHeYbGB6DHA.3004@tk2msftngp13.phx.gbl...
> Yes, it's the MS OLE DB Provider for Visual FoxPro (VFPOLEDB.1)
>
> John
>
> "Wolfgang Schmale" <w_schmale@wsnews.de> wrote in message
> news:%237qEytA6DHA.2380@TK2MSFTNGP10.phx.gbl...
> > Hi John!
> >
> > Do you use the OLE-Driver vor VFP?
> >
> > --
> > _________________
> >
> > MFG
> > Wolfgang Schmale
> >
> > MS Visual FoxPro MVP
>
>

John
Sat Jan 31 11:28:16 CST 2004

It works in the FoxPro environment, but I haven't figured out how to call it
properly using ADO.Net. Thanks for your help.

John

"Wolfgang Schmale" <w_schmale@wsnews.de> wrote in message
news:udyOveB6DHA.2696@TK2MSFTNGP09.phx.gbl...
> Hi John!
>
> I'm not the ADO.NET freak. But AFAIK you should return a data object and
not
> a cursor file to the caling function.
> I would try:
>
> Select * from customers into cursor myCustomers
> scan
> scatter memo to name oCustomers additive
> endscan
> return oCustomers
>
> --
> _________________
>
> MFG
> Wolfgang Schmale
>
> MS Visual FoxPro MVP

Dan
Sat Jan 31 13:39:05 CST 2004

Sorry. FoxPro cursors are meaningless outside the FoxPro engine.

If you're using ADO, use the ADO RecordSet object and let it perform the
query. Or create an ADO recordset within FoxPro and RETURN it to the caller.

Why don't you tell us what you're trying to accomplish instead of telling us
how you're trying to do it?

Dan

John wrote:
> I'd like to call this stored procedure from outside FoxPro using ADO
> or ADO.Net. When I do so, no records are returned. If using a
> cursor is the way to do this, how do I expose the cursor to the
> caller?
>
> Thanks,
>
> John
>
> "Dan Freeman" <spam@microsoft.com> wrote in message
> news:OcEALC45DHA.2348@TK2MSFTNGP10.phx.gbl...
>> If you run that stored proc as is, it should show you a browse
>> window with the customer records. Is it?
>>
>> You generally need an output target for SELECT statements.
>>
>> Select * from Customer into cursor csrCustomer
>>
>> Dan

John
Mon Feb 02 13:12:07 CST 2004

Thanks Dan.

I'm trying to understand the basics of creating a stored procedure in FoxPro
that returns records. I plan to call these procedures from managed code
using the classes from the System.Data.OleDb namespace (ado.net). I know
that I don't need to use a procedure to simply request records.

But I'd like to know how to do it with a procedure because the next thing I
want to look into is trying to implement long-term locks on records. For
example, a user will be able to effectively "check-out" a record a work on
it for hours or even days. One way I'm thinking of implementing this is to
create a procedure that locks the record, checks to see if another user has
it checked out, if not, update it to indicate that the current user has it
checked out, and then return the record to the caller.

Any thoughts on how to best do this with FoxPro would be appreciated.

John

"Dan Freeman" <spam@microsoft.com> wrote in message
news:O2e9BHD6DHA.2628@TK2MSFTNGP10.phx.gbl...
> Sorry. FoxPro cursors are meaningless outside the FoxPro engine.
>
> If you're using ADO, use the ADO RecordSet object and let it perform the
> query. Or create an ADO recordset within FoxPro and RETURN it to the
caller.
>
> Why don't you tell us what you're trying to accomplish instead of telling
us
> how you're trying to do it?
>
> Dan

Dan
Mon Feb 02 14:25:39 CST 2004

Persistent locks, or semaphores, are best handled by writing information to
a table used specifically to keep track of them.

I think you're trying to put more functionality in the database than was
intended. Remember that VFP is no Oracle. :-)

Dan

John wrote:
> Thanks Dan.
>
> I'm trying to understand the basics of creating a stored procedure in
> FoxPro that returns records. I plan to call these procedures from
> managed code using the classes from the System.Data.OleDb namespace
> (ado.net). I know that I don't need to use a procedure to simply
> request records.
>
> But I'd like to know how to do it with a procedure because the next
> thing I want to look into is trying to implement long-term locks on
> records. For example, a user will be able to effectively "check-out"
> a record a work on it for hours or even days. One way I'm thinking
> of implementing this is to create a procedure that locks the record,
> checks to see if another user has it checked out, if not, update it
> to indicate that the current user has it checked out, and then return
> the record to the caller.
>
> Any thoughts on how to best do this with FoxPro would be appreciated.
>
> John
>
> "Dan Freeman" <spam@microsoft.com> wrote in message
> news:O2e9BHD6DHA.2628@TK2MSFTNGP10.phx.gbl...
>> Sorry. FoxPro cursors are meaningless outside the FoxPro engine.
>>
>> If you're using ADO, use the ADO RecordSet object and let it perform
>> the query. Or create an ADO recordset within FoxPro and RETURN it to
>> the caller.
>>
>> Why don't you tell us what you're trying to accomplish instead of
>> telling us how you're trying to do it?
>>
>> Dan

John
Mon Feb 02 21:08:58 CST 2004

I see your point - thanks for the advice. Getting back to stored
procedures - I'm curious. Is there a way to return records from one in
FoxPro to an ado.net client?

Thanks,

John

"Dan Freeman" <spam@microsoft.com> wrote in message
news:Oom3Xqc6DHA.3308@TK2MSFTNGP11.phx.gbl...
> Persistent locks, or semaphores, are best handled by writing information
to
> a table used specifically to keep track of them.
>
> I think you're trying to put more functionality in the database than was
> intended. Remember that VFP is no Oracle. :-)
>
> Dan
>

John
Tue Feb 03 06:27:02 CST 2004

The comments below indicate what I want to do. Please help me with the
syntax::
I have a table called customer with fields: id, firstname, lastname,
userlock
I want to write the name of the username parameter to the userlock field if
I can lock it, and if it is null.
I want to the procedure to always return the record including the userlock
field whether the user is successful in locking the record to my ADO.Net
client. If unsuccessful, my GUI will treat the data as read-only.

procedure
parameters customerId, username
lparameters alreadyLocked

** lookup record
select * from customer where id = customerId

** make sure we found this record (not sure how to do this)

** try to lock the record
if( RLOCK() )

** check the userlock field of the customer record to see if it's
null (not sure how to do this)
if( userlock = null )

** set the userlock field in this record to the username of the
caller (is this most efficient?)
update customer set userlock = username where id = customerId
endif
endif

** return the record to the ado.net caller (not sure how to do this)
endproc

Thanks in advance.

John


"John" <john@nospam.com> wrote in message
news:OgoLZMg6DHA.1592@TK2MSFTNGP10.phx.gbl...
> I see your point - thanks for the advice. Getting back to stored
> procedures - I'm curious. Is there a way to return records from one in
> FoxPro to an ado.net client?
>
> Thanks,
>
> John
>

Dan
Tue Feb 03 12:11:11 CST 2004

See below.

John wrote:
> The comments below indicate what I want to do. Please help me with
> the syntax::
> I have a table called customer with fields: id, firstname, lastname,
> userlock
> I want to write the name of the username parameter to the userlock
> field if I can lock it, and if it is null.
> I want to the procedure to always return the record including the
> userlock field whether the user is successful in locking the record
> to my ADO.Net client. If unsuccessful, my GUI will treat the data as
> read-only.
>
> procedure
> parameters customerId, username
> lparameters alreadyLocked
>
> ** lookup record
> select * from customer where id = customerId

This does not "look up a record". This extracts a record to a separate
recordset. Try Seek() or Locate instead.

>
> ** make sure we found this record (not sure how to do this)

Found() or NOT Eof()

> ** try to lock the record
> if( RLOCK() )

If you are working on a recordset, this will NOT lock the original record.
It will lock the copy in the temporary recordset.

> ** check the userlock field of the customer record to see if
> it's null (not sure how to do this)

If Empty(userlock)

> ** set the userlock field in this record to the username
> of the caller (is this most efficient?)
> update customer set userlock = username where id =
> customerId endif

This would be the least efficient way. Instead...

Replace userlock WITH username

> endif

Endif

>
> ** return the record to the ado.net caller (not sure how to do
> this) endproc

You cannot return a VFP recordset. You can return individual field values,
or you could create and return an ADO recordset.

>
> Thanks in advance.
>
> John
>
>
> "John" <john@nospam.com> wrote in message
> news:OgoLZMg6DHA.1592@TK2MSFTNGP10.phx.gbl...
>> I see your point - thanks for the advice. Getting back to stored
>> procedures - I'm curious. Is there a way to return records from one
>> in FoxPro to an ado.net client?
>>
>> Thanks,
>>
>> John