Returning Arrays And Cursors to .NET from VFP???

AlexBB

It is all about the business of Stored Procedures which I am trying to perfect. The SPs are called by my .NET C# app. A lot of the stuff I've had difficulaties initialy appears to be working now. Still there are numerous limitations and one has to get used to them. It is a new style of programming. Very austere. If you want to DELETE a record in a table and you have 15 of them and want to determine at runtime which one to choose, you cannot use DO CASE. It won't work, although it is listed neither in supported nor unsupported Ole DB keywords. You cannot even use a stack of IF/ELSE/ENDIF statements to make a selection inside one SP. You have to write 15 SPs separately.

Anyhow, I want to investigate (with very little hope for a positive resolution) a possibility of returning more than a single variable from a SP. A signle variable does come bacx. But I want to get an array back.

PROCEDURE testing

DIMENSION words (5,2)

words[1,1] = "a1"

words[1,2] = "a2"

words[2,1] = "b1"

words[2,2] = "b2"

words[3,1] = "c1"

words[3,2] = "c2"

words[4,1] = "d1"

words[4,2] = "d2"

words[5,1] = "f1"

words[5,2] = "f2"

RETURN (words)

ENDPROC

The rusult: only the first element ("a1") came back. That was it. I am wonderng if a pointer to the location of this array can be determined inside such code and passed back Is it possible I checked SYS ( ) functions but could not see anything remotely related to it.

Of ocurse, there are workarounds. I can send this array into an XML file with CURSORTOXML and I have done it but it will slow things down. Does anybody know how I can get either the whole array or a pointer to the memory location where it is positioned That might be a dead end, however. After the SP finished all memory is supposed to be cleared, isn't it

Another thing is to get a CURSOR out of a SP. This is the code that actually works:

PROCEDURE selectFromCrossRefByPointOne

LPARAMETERS p_one

SET PATH TO "C:\VFP_Projects\Data\ComeAndGetDatBase\"

OPEN DATABASE comeAndGet EXCLUSIVE

m.retValue = ""

SELECT * FROM crossRefTable WHERE pointer_one = p_one INTO CURSOR qTemp READWRITE ORDER BY pointer_two

SELECT qTemp

GO TOP

cou = 0

DO WHILE NOT EOF ()

IF (cou = 0)

m.retValue = pointer_two

ELSE

m.retValue = m.retValue + "-" + pointer_two

ENDIF

cou = cou + 1

SKIP

ENDDO

USE

CLOSE DATABASES ALL

RETURN (m.retValue)

ENDPROC

It returns a string of ANY LENGTH but when I tried to return a CURSOR qTmp I got an error: variable qTmp not found. I enclosed it in brackets [qTmp] - it gave me another error.

Anybody has any idea how I can return a cursor from a SP

Thanks.

PS. I want to expand on that ANY LENGTH issue in the next bill.

AlexBB

I want to elaborate a little on this ANY LENGTH highlight. In a recent thread at this forum http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=2314564&SiteID=1 Cetin posted this remarkable revelation:

"Alex,

If you say you can't drive a Ferrari that doesn't make Ferrari a bad car.

Anyway, both your samples are prone to have "Syntax error". In VFP a literal string cannot be over 255 in length. When you try to pass a literal over 255 in length you would get a Syntax error. Solution was given to you before. Use parameters instead. I know that you insist not following my advice but I would repeat: Instead of trying to build a single string with StringBuilder pass a simple commandtext with parameters. Parameters themselves can be Mbs long."

Aside from the fact that the string he was talking about was way below 255 chars long (only 136, I think) I do not remember if I've ever run into this limitation before. When I coded my VFP stuff I used strings all the time. I should have noticed that. This is what I answered:

"I do not remember if I ran into this problem while coding my FP stuff, perhaps I did. As so much Foxes strangeness it did not stick"

Anyhow, now, in retrospect, I find it completely bizarre that a "moderator" with Cetin's experience and professed love for the Fox culture has never checked if this declarative statement is factually true. It is NOT. When I ran the second SP above the first time around it returned a string of 672 characters long UNMOLSETED. I am now absolutely certain (although it is kind of difficult for me to retrace some of my completely debugged code since it refuses to work in Vista, I have to go to XP for that) that in many instances I was operating strings of some thousands bytes long. Even more bizarre is that fact that almost 800 peope have checked this thread so far and nobody made a comment on this now obviously erroneous statement.

My hunch is that this limitation, if ever existed, (along with Y2K and other limits) has been eliminated year ago but still persists in someone's memory. Another explanation is that peheps he confuses a limit on the LENGTH of a CHARACTER field in a VFP Table with a memory char string! And this is again my point. A lot of people I've noticed memorize MSDN pages but have little experience with coding really workable applications. What they do is mostly demos, webpages and webcasts. I thought Cetin was above that. Apparently I was mistaken.

Quoting Cetin again: "Alex, ..... I hope you get it now."

CetinBasoz

Alex,

Looks like you're trying to draw my attention. You succeeded.

If you haven't understood the statement I repeat it here again for you:

In VFP a string literal cannot exceed 255 characters.

Nobody made a comment on that statement because, unlike you, they know that the statement is right. What did you expect Everybody posting a comment saying "yes I confirm that statement is true" Do you expect a confirmation from others when I say "2+2=4"

That limitation exists from the first version of foxpro and even foxbase, to latest VFP9. It's still in my memory, very true. Not only in my memory but it is a fact that holds ground with the latest version of VFP too. Just show me a single VFP version, a single code sample that proves the otherwise. You may ask on any fox forums about it and if you find a second person that thinks that statement was wrong I am here to train both of you. Just ask kindly. I will even help you further and provide a string literal 255 in length. Just show me it could be a single character longer with any VFP related command/function you can think of. The error might not be "syntax error" exactly but something like "Command .. not recognized" etc. The bottomline is that it can't exceed 255 in length.

"1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890 ABCD"

If you reread the message you referenced you would see what is a 'string literal'. If you don't understand the difference between a literal and variable it should be due to my imperfect English. Read the VFP documentation. You can find VFP system capacities here (and maybe the persons who wrote that documentation are wrong like me and you're right Smile

http://msdn2.microsoft.com/en-us/library/3kfd3hw9(VS.80).aspx

You know that I don't have a perfect English I don't even understand what "unmolseted" mean.

Alex,

You're wasting your time and mine. That statement is right. When it's about VFP don't try to test my skills. At least you're not the person who could do it. Luckily it's also in the documentation this time so I can't be saying it with an imperfect English. This is a bad way of drawing one's attention. Instead you could simply ask how to return a cursor from a procedure (and array). For array I don't have an answer but for cursor I'd tell you to look at SetResultSet(). If your message's attitude wasn't bad I would even give you a full sample code.

CetinBasoz

BTW being a moderator, I don't have an idea why I have been a moderator, who made me a moderator. I don't use any moderator power at all (well did twice really, once to remove a duplicate post and once to mark "someone else's" answer as answer). I never had an interest being a moderator in other words. I don't know what I can do about it to satisfy you.

Darren Woodford

Alex, I don't have anywhere near as much VFP knowledge and experience of VFP as Cetin but I am a native English speaker so hopefully I can help out here and make sure you understand what he is saying here. Personally I think his English is excellent but I will try and state it as I understand it just in case....

I have made this mistake myself before and couldn't understand why one of my SQL statements was failing for no apparent reason. The confusion for me was the difference between a string literal and a variable which contains a string.

A string literal is one you put in between two quotes eg "foobar string". As Cetin says these cannot be over 255 chars in length.

A variable however can contain a string way over 255 chars (I don't know what limit there is to this!) and can easily be formed from several string literals (or variables containing strings) concatenated together.

Eg

stringvariable = "a string of 255 chars or less" + "another string of 255 chars or less" && resulting variable contents can be over 255 chars

I hope that makes sense.

If you don't agree with what someone else is stating it's probably safer to assume you have misunderstood and ask them to clarify the answer. Programming can be a very frustrating experience at times and if we aren't careful sometimes our frustrations can come across negatively in a posting.

Cetin, I think 'unmolseted' is probably a typo for unmolested (adj. Not interfered with, disturbed, or harmed) showing that the string had been returned in its expected form without been truncated or affected in any other way.

Regards

Darren

AlexBB

CetinBasoz wrote:

BTW being a moderator, I don't have an idea why I have been a moderator, who made me a moderator. I don't use any moderator power at all (well did twice really, once to remove a duplicate post and once to mark "someone else's" answer as answer). I never had an interest being a moderator in other words. I don't know what I can do about it to satisfy you.

It is cute. I did not expect this. All said and done, you deserve to be a moderator more than anyone else on this forum, for sure. But you should keep your head cool, sometimes it gets overheated.

I do not need any special treatment as I have said many times in the past. I have a right to be here. I pay $900.00 a year for MSDN subscription, I am a user, not a dedicated teacher as others. I do appreciate their effort nonetheless, believe me, although it may be hard for you to do since you once said that I have insulted everyone.

All I want for people who know something on the subject to share their knowledge even if they prove to be wrong. Part of the process of getting at the solution for me is this posting, Even if I never get a satisfactory response, while writing the bill I think everything through. When people answer some clues come indirectly at times.

The thing I do not need is frequent suggestions that I am ignorant and should take their lessons. I explained clearly before that I am ingorant but do not want to take their lessons.I learn through being here. I always get what I want one way or another.

All said and done you've helped me a lot in the past, more than anyone else, and I do appreciate it greatly. You've always been willing to go an extra mile to share what you know or worked things out on the fly. A real life example is the most recent code sample you posted. It was on the subject of OleDbDataAdapter and UPDATE command passed through it. I was hypnotized by REPLACE command that did not work since it is not supported and wasted a lot of time on trying to debug it. Although I do not like DataAdapter's for the reason that they load the whole dataTable into you app I still have this code working in a few parts of my program, although I have already made one SP that does away with it. And I remember your help.

AlexBB

Darren Woodford wrote:

Cetin, I think 'unmolseted' is probably a typo for unmolested (adj. Not interfered with, disturbed, or harmed) showing that the string had been returned in its expected form without been truncated or affected in any other way.

Regards

Darren

Sure 'unmolseted' is a typo. I often change positions of two neighbouring chars when I type. It is a sort of keyboard dyslexia. Many people know it (a lot of folks, I've noticed exhibit it) and pay no attention. I just swap them in my mind while reading.

Well, that literal string in question was in debug. I commented before posting the code that it was a temporary solution since I wanted to find out how the code was working basically. I also mentioned that enclosing variables in singe quites was a bad idea (regardless of the issue of the string length, that string was only 136 chars long). The reason that it was a bad idea is that a single qoute might happen to be a part of the string itself once upon a time. Should that thing worked, another delimiter must have been found, that was it. He attacked me (read that post) unfairly and unjustifiably on the grounds that I wanted to pass a long string as part of a SELECT statement or something. I wanted to make sure that the principle worked. The routine failed for a different reason. There was a bug in the code: I enclosed the table name in single qoutes--that was it. It is frustrating how inattentive poeple are. You post one thing but they read it totally different, even considering the fact that I try my best to make everything very clear.

Cetin did not notice that the string was short, he did not read the preambule to the code. he started attacking me like I was mentally retarded and did not know simple things about Fox. I've written perhaps 250,000 lines of Fox code and it all worked reasonalby well until I switched to Vista. Here it is dead in the water and I have given up on it. All I want is to access my tables from .NET hopefully with SPs if possible. I am almost 95% done but I want to consider if some optimization can be done and this is why I am still psoting here.

BTW, on a positive note, and perhaps, Cetin will be interested, I've proved that when you call a SP from .NET that SP in turn can call another SP located in the same file (under Stored Procedure node in the DataBase Browser), get a result back and exit. I haven't tried it yet, but I am pretty certain that I will be able to call any of my .prg files eventully and they will work. Some of that code may be salvaged. It is a very good news for me.

I cannot care less about the length of Fox literals.As far as I am concerned they can go to hell.

AlexBB

Could you possibly explain to me in a simple way why when I write code in SP I cannot say

SEEK ( [word] ) && where word is "e-mail"

I have to write

SEEK (word) && this will work

However when I try things in Command Window (my God, hopefully this Fox time will end soon), I have to type the same command as

SEEK ([e-mail]) && after I opened the table and SET ORDER TO 1

otherwise I get an error "Variable 'e' not found." This all is so idiotically confusing I simply hate it. There is no logic in it whatsoever.

Also, any hope on the main subject of this post: How can I get a cursor out of Fox databases Perhaps ODBC will allow it or another driver Is it possible to get an array out of Fox to .NET

CetinBasoz

Alex,

(It wouldn't help me if you correctly wrote "unmolested", I still didn't know what it meant, tried thesaurus and it pointed to unmolested but totally irrelevant entries. Use easier words when talking to me. To you it may sound like bread and butter but I got lost).

You need to surround e-mail with quotes or brackets because otherwise it's interpreted as e minus mail. The same logic applies in SQL server and .Net (IMHO in .Net the logic is even harder).

For arrays I don't have an answer as I said before. There is:

Procedure getArrayRef(tcArrayName)

return @&tcArrayName && or with a class array return @this.arrayName

endproc

to do that in code to return an array reference to pass to an external routine, say Excel. But within an SP how that might work I don't know.

You can return cursors however. This is a sample SP using VFP9 testdata.dbc:

*** VFP SP

Procedure SalesmanCustomerProduct(tdStart As Date, tdEnd As Date, tnProductId As Integer)

Local ldMin,ldMax,lnProductMin, lnProductMax

ldMin = Iif( Vartype(tdStart)$'TD', m.tdStart,{})

ldMax = Iif( Vartype(tdEnd)$'TD', m.tdEnd, Date())

If Vartype(m.tnProductId) = 'N' And !Empty(m.tnProductId)

Store m.tnProductId To m.lnProductMin,m.lnProductMax

Else

Store 0 To m.lnProductMin

Store 0x7FFFFFFF To m.lnProductMax

Endif

Select emp.first_name - (' '+emp.last_name) As 'Salesman', ;

st.company As 'Customer', ;

st.prod_name As 'Product', ;

st.Quantity, ;

st.order_date As "orderDate" ;

from employee emp ;

INNER Join ;

( Select ord.emp_id ,cus.cust_id, cus.company, ;

ord.order_date, Val(prd.product_id) As ProductID, ;

prd.prod_name, Od.Quantity ;

FROM customer cus ;

INNER Join orders ord On ord.cust_id == cus.cust_id ;

INNER Join orditems Od On Od.order_id == ord.order_id ;

INNER Join Products prd On prd.product_id == Od.product_id ) st ;

on emp.emp_id == st.emp_id ;

order By 5,1,2,3 ;

WHERE st.order_date Between m.ldMin And m.ldMax And ;

st.ProductID Between m.lnProductMin And m.lnProductMax ;

Into Cursor cusEmpSale

Setresultset('cusEmpSale')

Return Reccount('cusEmpSale')

Endproc

*** C# code (output formatting wouldn't nicely work):

Code Block

using System;
using System.Data;
using System.Data.OleDb;

class sample
{
static void Main(string[] args)
{
// Parse Command line parameters
// StartDate,EndDate,ProductID
// All 3 are optional
// StartDate only means from StartDate to date
// EndDate only means from first date to EndDate
// StartDate, EndDate not specified means all dates
// ProductId not specified means all products
DateTime start,end;
int product;
if ( args.Length > 0 )
DateTime.TryParse(args[0], out start);
else
start = new DateTime(100,1,1); // VFP min date value is Jan 1, 100
if ( args.Length < 2 || DateTime.TryParse(args[1], out end) != true )
end = DateTime.Now;
if ( args.Length > 2 )
Int32.TryParse(args[2], out product);
else
product = 0;

OleDbConnection con = new OleDbConnection(@"Provider=VFPOLEDB;
Data Source=C:\PROGRAM FILES\MICROSOFT VISUAL FOXPRO 9\SAMPLES\DATA\TESTDATA.DBC");
OleDbCommand cmd = new OleDbCommand("SalesmanCustomerProduct",con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("startDate",OleDbType.Date).Value = start;
cmd.Parameters.Add("endDate",OleDbType.Date).Value = end;
cmd.Parameters.Add("productId",OleDbType.Integer).Value = product;
con.Open();
OleDbDataReader rdr = cmd.ExecuteReader();

Console.WriteLine("Listing info from {0:d} to {1:d}. Product Id:{2}",
start,end,product);

Console.WriteLine("{0,10} {1,-15} {2,-20} {3,3} {4,-20}",
"Date","Sales Man","Customer","Qty","Product");
Console.WriteLine("{0,10} {1,-15} {2,-20} {3,3} {4,-20}",
"".PadRight(10,'-'),
"".PadRight(15,'-'),
"".PadRight(20,'-'),
"".PadRight(3,'-'),
"".PadRight(20,'-'));

while (rdr.Read())
{
Console.WriteLine("{0,10:d} {1,-15} {2,-20} {3,3} {4,-20}",
rdr["OrderDate"],
((string)rdr["SalesMan"]).Trim(),
((string)rdr["Customer"]).Trim(),
Convert.ToInt32(rdr["Quantity"]),
((string)rdr["Product"]).Trim());
}
rdr.Close();
con.Close();
}
}

For Update you don't need DataAdapter. You could directly use OleDbCommand. Roughly:

OleDbConnection con = ...

OleDbCommand cmd = new OleDbCommand("Update myTable set field1 = , field2 = where pkField = ",con);

OleDbParameter p1 = new OleDbParameter("field1", OleDbTypeWhatever);

OleDbParameter p2 = new OleDbParameter("field2", OleDbTypeWhatever);

OleDbParameter p3 = new OleDbParameter("field3", OleDbTypeWhatever);

// The order of parameters is important - matched with in the same order

cmd.Parameters.Add(p1);

cmd.Parameters.Add(p2);

cmd.Parameters.Add(p3);

p1.Value = newvalue;

p2.Value = someothervalue;

p3.Value = pkvalue;

con.Open

cmd.ExecuteNonQuery(); // or scalar for a return value

p1.Value = newvalue2;

p2.Value = someothervalue2;

p3.Value = pkvalue2;

cmd.ExecuteNonQuery(); // or scalar for a return value

con.Close

AlexBB

Thank you Cetin. Very much appreciated. I will have to study it, especially the cursor routine. Did you mean I can get the cursor from VFP in .NET That will be great.

I just want to post one of my SPs which combines in some sence INSERT (implicitly though) an UPDATE. It was a very good news for me when I got the result back in .NET and found a record in the VFP table. The things are looking up more and more. Now I am avoiding traps I was not aware about initially which spoiled everything.

The unmolested string meant in the context that I got back in .NET the string in precisely the same form and shape and substance as it was formed in the VFP SP. There were no alterations, it was not truncated, my code read this string, etc.

PROCEDURE addCustom

LPARAMETERS word

SET NEAR OFF

retValue = ""

SET PATH TO "C:\VFP_Projects\Data\Spelling\"

OPEN DATABASE spelling EXCLUSIVE

USE customAdded IN 0 EXCLUSIVE

SET ORDER TO 1

SEEK (word)

IF NOT FOUND ()

APPEND BLANK

UPDATE customAdded SET customAdded.named = word, customAdded.mark = .F. WHERE customAdded.named = ''

retValue = "OK"

ENDIF

USE

CLOSE DATABASES ALL

RETURN retValue

ENDPROC && addCustom

Darren Woodford

Alex, I realise you are quite a way down the road with this project but looking at the background to your question have you considered writing a COM Server in VFP to allow you to run your PRG files from .Net Perhaps there is a reason you are using Stored Procedures that I am not aware of but it might be worth considering.

Regards

Darren

AlexBB

Darren Woodford wrote:

Alex, I realise you are quite a way down the road with this project but looking at the background to your question have you considered writing a COM Server in VFP to allow you to run your PRG files from .Net Perhaps there is a reason you are using Stored Procedures that I am not aware of but it might be worth considering.

Regards

Darren

Thank you Darren. I would like you to elaborate since I've never written a COM server in VFP. I want to say beforehand, however, that after the painful crash of my all VFP applications in Vista (they simply refused to start) I have no more trust in VFP COM objects. One of the reason for this crash, I think is that I incorporated too many of them. It killed it. The apps became unportable.

Using Stored Procedures seems to be safe. It is simple code that talks to VFP tables directly. I understand the SP are located in .dbc file. So they will go with the database wherever I transferred it. This is what I need: to preserve my tables, enhance them and do some computations on the values of the fields.

However, if you think I misinterpreted your suggestion, please let me know.

Thanks.

AlexBB

CetinBasoz wrote:

Alex,

(It wouldn't help me if you correctly wrote "unmolested", I still didn't know what it meant, tried thesaurus and it pointed to unmolested but totally irrelevant entries. Use easier words when talking to me. To you it may sound like bread and butter but I got lost).

*** C# code (output formatting wouldn't nicely work):

Code Block

Cetin, I opened up the database textData.dbc and the stored procedure file is empty. "SalesmanCustomerProduct" is not there.

Thanks.

Darren Woodford

Alex, there are people here far more qualified than me to give you guidance on this. I am still learning myself. However I will tell you what I know to try and get you started.

If you simply add the OLEPUBLIC keyword to the end of a class definition you can make that class available to any COM compliant language. When you build your project you can choose to build it as a COM DLL or a standalone EXE COM Server. From the calling client you use something along the lines of

COMServerReference = CREATEOBJECT("comserver.comclass")

Apparently it is even possible to publish your com servers as Web services but I haven't gone that far myself yet.

I have created a COM server for one of my customers who uses Access but wants to interface with a 3rd party VFP application that we resell. I am still getting to grips with it properly myself but I think it would might be a easier route for you than calling SPs from a DBC via OLEDB. Perhaps Cetin or another more experienced developer has a viewpoint on this

In the VFP9 help file under Using Visual Foxpro > Developing Visual Foxpro Applications there is a section entitled Web Services and Components. I think this would be a good starting point for you. There are also tonnes of web pages on the subject but the VFP help file has enough to get you started.

If you just wanted a fairly straightforward way to execute your PRGs then perhaps a simple COM Server as below might help. One of the main problems is how you will transfer data between C# and VFP COM Server. For this you will need to look at ADO or XML or custom objects.

Simple COM Server to run prgs:

Code Block

DEFINE CLASS RunPRG AS SESSION OLEPUBLIC

FUNCTION RunPRG(prgname)

DO &prgname

ENDFUNC

ENDDEFINE

Regards

Darren

CetinBasoz

Alex,

I didn't say that procedure is in testdata.dbc, did I I am not sure, anyway I meant it uses tables from testdata.dbc so add the sample SP code I provided in testdata.dbc.

Returning Arrays And Cursors to .NET from VFP???

Re: Returning Arrays And Cursors to .NET from VFP???

Re: Returning Arrays And Cursors to .NET from VFP???

Re: Returning Arrays And Cursors to .NET from VFP???

Re: Returning Arrays And Cursors to .NET from VFP???

Re: Returning Arrays And Cursors to .NET from VFP???

Re: Returning Arrays And Cursors to .NET from VFP???

Re: Returning Arrays And Cursors to .NET from VFP???

Re: Returning Arrays And Cursors to .NET from VFP???

Re: Returning Arrays And Cursors to .NET from VFP???

Re: Returning Arrays And Cursors to .NET from VFP???

Re: Returning Arrays And Cursors to .NET from VFP???

Re: Returning Arrays And Cursors to .NET from VFP???

Re: Returning Arrays And Cursors to .NET from VFP???

Re: Returning Arrays And Cursors to .NET from VFP???