AlexBB


If not mastering counter intuitive VFP object manipulation was not hard enough in the past until I finally got a handle of it, now trying to do it from OleDb .NET code is like moving through heavy water--it is 10 times more difficult.

I thought I reached the promised land when I finally made PACK work but my joy was premature. Now INSERT gives me the same trouble.

I really prefer to do REPLACE (as well as other commands) by writing a SP where the commands are located and storing them permanently at the DBs. That would be my preferred choice. t would allow me to reuse some of my old code which contains thousands such commands. I hoped that I would be able to write a simple SP and call some of my old .prg files from it. This is what Cetin has said should work. I now kind of doubt this although I haven't tried it yet.

OK, this is one SP that gives me exceptions. I commented out all commands that do not work starting with the first one:

PROCEDURE replaceMemo ( numericid, memoed )

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

OPEN DATABASE comeAndGet

USE categories IN 0

* SET ORDER TO numeric_id

* SEEK m.numericid

* IF FOUND()

* REPLACE memo1 WITH m.memoed FOR numeric_id = m.numericid

* ENDIF

ENDPROC

It is always: "Attempt to read/write protected memory" exception.

I tried to uncomment some of the statement one at a time sequentially and in various combinations and the result was the same.

My next attempt (and my next preferred choice) is Cetin/Naomi's execScript idea. It always gives me "Syntax Error" exception even in C# code at runtime. Again, many various combinations have been tried to no avail.

private void saveMemo( string dataTableName, string numeric_id, string memo )

{

string procedure = @"packTablesInComeAndGet( 'C:\VFP_Projects\Data\ComeAndGetDatBase\', '"+numeric_id+"', '"+memo+"' ) "+

" Procedure packTablesInComeAndGet ( tcPath, numericid, memoq ) "+

" Use ( Addbs(m.tcPath )+'categories') Exclusive "+

" REPLACE memo1 WITH memoq FOR numeric_id = numericid IN categories"+

" Endproc ";

OleDbConnectionStringBuilder scb = new OleDbConnectionStringBuilder ( );

scb.Provider = "VFPOLEDB";

scb.DataSource = "C:\\Temp";

string connString = scb.ToString ( );

using (OleDbConnection conn2 = new OleDbConnection ( connString ))

{

conn2.Open ( );

OleDbCommand cmdm2 = new OleDbCommand ( );

cmdm2.Connection = conn2;

cmdm2.CommandType = CommandType.StoredProcedure;

cmdm2.CommandText = "Execscript";

cmdm2.Parameters.Add ( "p", OleDbType.Char ).Value = procedure;

try

{

cmdm2.ExecuteNonQuery ( ); // INSERT'ed

Console.WriteLine ( "Done Alex." );

}

catch (Exception ex)

{

Console.WriteLine ( "2nd SB (REPLACE): \r\n{0}, \r\n{1}", ex.Message, ex.StackTrace );

}

}

} // saveMemo

I am almost certain there are no Syntax Errors in that code but you never know.

My next variant was:

private void saveMemo2( string dataTableName, string numeric_id, string memo )

{

// the connection string here is different. It connects directly to the DB in question

StringBuilder sb = new StringBuilder ( );

sb.Append ( "REPLACE memo1 WITH '" + memo + "' " );

sb.Append ( " WHILE numeric_id = '" + numeric_id + "' IN 'categories' " );

using (OleDbConnection conn2 = new OleDbConnection ( Globals.connStr ))

{

conn2.Open ( );

OleDbCommand cmdm2 = new OleDbCommand ( );

cmdm2.Connection = conn2;

cmdm2.CommandType = CommandType.Text;

cmdm2.CommandText = sb.ToString ( );

cmdm2.ExecuteNonQuery ( );

} // end of using conn2

} // saveMemo

Here I also got a Syntax Error exception. This code obviously a very poor choice since the delimiter ' (single quote mark) can and eventually will appear as a part of the memo text itself. I just wanted to prove the principle. It did not work either.

This is one of the strings I recovered out of Visual Studio 2005 Error Window:

"REPLACE memo1 WITH '{\\rtf1\\ansi\\ansicpg1252\\deff0\\deflang1033{\\fonttbl{\\f0\\fnil\\fcharset0 Microsoft Sans Serif;}}\r\n\\viewkind4\\uc1\\pard\\f0\\fs24 bvcvc\\par\r\n}\r\n' WHILE numeric_id = 'C000149' IN 'categories' "

The above string is OleDbCommand.CommandText content.

I also tried FOR instead of WHILE. Actually the numeric_id field is the primary key and it does not matter. There should be only one record.

I think most of it RTF formatting. I typed in a few characters mindlessly. I think they are the ones highlighted.

This is where I stand. I you want to get rid of me, please help me resolve my few remaining problems and I will be out of here, hopefully for goodSmile

If no way around the problem is found then my default option would be to use the following sequence of commands: DELETE the sought for record (it has worked), PACK the Table (works), INSERT a similar record (it has worked as a stand alone operation) with a new MEMO field but all the other field contents saved and reloaded.

That may not work either. I noticed that OleDb does not seem to like when I do DELETE, PACK and INSERT immediately after that. It wants the app to be closed first but I haven't studied it in detail.

Please help.





Re: REPLACE command help wanted

Dave M.


This looks line a CDX problem to me , maybe network security on it.

Delete it and recreate. Check network security .







Re: REPLACE command help wanted

AlexBB

Dave M. wrote:

This looks line a CDX problem to me , maybe network security on it.

Delete it and recreate. Check network security .

All operations are local. No network is involved. What makes you think it is a network security issue

If I do DELETE and INSERT a new record it will mess my "natural order" of records. It is very inconvenient in many respects.

I am wondering if someone could try my codes on their computers. It can be done from VB or C#, perhaps J# whatever is available. If they work for someone it will give me a totally new perspective.







Re: REPLACE command help wanted

Dave M.

I meant delete the CDX.. and recreate it.

I wouldent replay on "natural order" of records.. its better to add an counter record to know the order it was inserter . then you can keep the counter fileds as is when doing inserts and deletes.






Re: REPLACE command help wanted

AlexBB

Dave M. wrote:

I meant delete the CDX.. and recreate it.

I wouldent replay on "natural order" of records.. its better to add an counter record to know the order it was inserter . then you can keep the counter fileds as is when doing inserts and deletes.

Stick around. You may be giving a life saving piece of information but I am not sure yet. Do you mean APPUSER.CDX Why could you not be more specific And why should it matter The VFP project is not instantiated while OleDb is active.

Give me the full name of the file you want me to delete. There are hundreds of CDX files in my VFP directories. And I am not so VFP smart as you can expect.

Thanks.

PS. The natural order of records is a matter of convenience. Debug and adjustemts are frequent. You open a table in VFP Object Explorer and you want to see the records in the order received. It is annoying to type "set order to" every time.






Re: REPLACE command help wanted

Dave M.

I ment categories.cdx

The index file assocated with the table you are useing.

If you are geting an error on the "set order to" to me that indicates a problem the index.

So if you cant open the index of the index is bad , you can't seek properly.

ps i know next to nothing about .net .

>>
It is annoying to type "set order to" every time
i find it more annoying to have to worry about the order.. simple things like "copy to" or "select into" become big problems. But to each there own.






Re: REPLACE command help wanted

CetinBasoz

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.

"This is a literal. In other words anything between quotes is a literal and can't exceed 255 characters"

string procedure = @"this is a string variable in C#. Could be megabytes long. ie: You may try to read a book";

// ...

cmdm2.Parameters.Add ( "p", OleDbType.Char ).Value = procedure; // this one is a parameter

When you pass 'memo' within quotes you are passing a literal which may be over 255 and cause a syntax error. I hope you get it now.

"I hoped that I would be able to write a simple SP and call some of my old .prg files from it. This is what Cetin has said should work."

Reread my message about it. I didn't say it SHOULD work but it MIGHT work. I remember I added a series of warnings there.

"My next attempt (and my next preferred choice) is Cetin/Naomi's execScript idea. "

Reread those messages again too. ExecScript idea was because of the code you were trying to use. However here you don't need it at all. Simply use SQL DDL commands.

Check your previous threads and you would see the standard way of sending commands (remember the thread you said the standard way of OleDbCommand building and where I didn't agree and showed the "standard" waySmile

PS: I never understood the purpose of your "open database" from within an SP. Probably you know something that I don't know.





Re: REPLACE command help wanted

AlexBB

Dave M. wrote:
I ment categories.cdx

The index file assocated with the table you are useing.

If you are geting an error on the "set order to" to me that indicates a problem the index.

So if you cant open the index of the index is bad , you can't seek properly.

ps i know next to nothing about .net .

>>
It is annoying to type "set order to" every time
i find it more annoying to have to worry about the order.. simple things like "copy to" or "select into" become big problems. But to each there own.

Oh, Dave, it is a nonstarter. I won't even try this. It actually occured to me overnight as frequently happens that you meant that. If I had a bad index file I would not have been able to open this table in the Explorer. I do it all the time.

Aside from that I have the same problem with multiple tables. It is unlikely the indeces of so many of them have become corrupted. So it is being dismissed. But thanks for suggestion.






Re: REPLACE command help wanted

AlexBB

CetinBasoz wrote:

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.

"This is a literal. In other words anything between quotes is a literal and can't exceed 255 characters"

string procedure = @"this is a string variable in C#. Could be megabytes long. ie: You may try to read a book";

// ...

cmdm2.Parameters.Add ( "p", OleDbType.Char ).Value = procedure; // this one is a parameter

When you pass 'memo' within quotes you are passing a literal which may be over 255 and cause a syntax error. I hope you get it now.

"I hoped that I would be able to write a simple SP and call some of my old .prg files from it. This is what Cetin has said should work."

Reread my message about it. I didn't say it SHOULD work but it MIGHT work. I remember I added a series of warnings there.

"My next attempt (and my next preferred choice) is Cetin/Naomi's execScript idea. "

Reread those messages again too. ExecScript idea was because of the code you were trying to use. However here you don't need it at all. Simply use SQL DDL commands.

Check your previous threads and you would see the standard way of sending commands (remember the thread you said the standard way of OleDbCommand building and where I didn't agree and showed the "standard" way

PS: I never understood the purpose of your "open database" from within an SP. Probably you know something that I don't know.

What makes you think I cannot drive a Ferrari. Give me a Ferrari and I will show you how to do itSmile

255 chars limitation is nice to know. I hope I won't need to retain this information in my memory for a long time. 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.

"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: "

Cetin, before issuing strong statements like this, measure the length of the string I was trying to pass as a parameter. I won't do it for you. After you checked the number of characters I suggest you should come up with a more sensible explanation.

Also that string which is enclosed in single quotes in my previous post ( '{\\rtf1\\ansi\\ansicpg1252\\deff0\\deflang1033{\\fonttbl{\\f0\\fnil\\fcharset0 Microsoft Sans Serif;}}\r\n\\viewkind4\\uc1\\pard\\f0\\fs24 bvcvc\\par\r\n}\r\n' ) is meant as a parameter to a MEMO field which has no length limitation. It is a string only on this side of .NET and as we all know there is no limit on string in there.

"cmdm2.Parameters.Add ( "p", OleDbType.Char ).Value = procedure; // this one is a parameter"

OK, you want to take the moral high ground and I will allow you to pretend that you believe that I do not understand that the procedure name here is a parameter.

However, I tend to think you may not understand that the Rtf field I am passing to the SP is also a parameter (to the SP which in turn is a parameter in your call to ExecScript). If you think that it should be added to the list of parameters as the second OleDbParameter, just say so.

I would prefer if you just wrote a correct form of REPLACE command format for OleDb that should work in your judgment instead of lecturing me with all these diatribes.

Cetin, reread your own posts: Nothing you've posted so far directly relates to this situation. However, I will look into what you've called DDL.

As they say in Encarta: "Did You Mean DDE "

"I never understood the purpose of your "open database" from within an SP. Probably you know something that I don't know.

Apparently I do, and why in the world have you become so sarcastic What's wrong with opening a DB from inside the SP There are so many DBs around, how in the world will Fox know which DB to look into Actually, this SP is located in a totally separate DB: dummy/blank DB (blank.dbc) and I open a TOTALLY DIFFERENT DB in it Understand Did you notice the previous statement:

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






Re: REPLACE command help wanted

AlexBB

CetinBasoz wrote:

Alex,

Simply use SQL DDL commands.

If you meant DDE than this quote from Marcia's:

"DDE is ancient technology and you should not be using it - you should be using automation." might help.

Folks, Cetin may not be around for a few hours since he is in a different time zone. What in the hell did he mean by DDL I cannot find it anywhere

What is SQL DDL It is a mystery to me. Sounds almost like: "Delete and recreate CDX".






Re: REPLACE command help wanted

Dave M.

DDL =Data Definition Language






Re: REPLACE command help wanted

AlexBB

CetinBasoz wrote:

Cetin, I want to make a point on a couple of statements I highlighted.

"Reread my message about it. I didn't say it SHOULD work but it MIGHT work. I remember I added a series of warnings there."

I think you are under an illusion (very far removed from reality) that your English is so perfect that every your sentence is worth of gold. I want to quote two of them I found in your previous post on the subgect:

"Separate prgs working right from within VFP or as SPs doesn't mean that they would work when called from VFPOLEDB too. It should be fully supported by VFPOLEDB."

You have two totally contradictory statements in here. In the first one you do not deny that they would work. You are saying that one should not count on it and get ready for surprises. In the second you claim that they are fully supported by VFPOLEDB. "Should be" from the mouth of an expert is understood that they ARE supported by OLEDB. I challenge anyone to understand what he meant Are they supported by OleDb or not Where is the "MIGHT" word he mentions

Again, are they going to work if called from VFPOLEDB.1 or not Answer it to me

The same thing or near can be said of most of your posts. It is not to say that they are totally not valuable. When you post a code sample which you have stored from some of your previous projects or whatnot a lot of meaning can be frequently extracted, however to say: go back and read my posts and find an answer to the REPLACE command debacle is just to give me a run around. There is nothing in your posts that is helpful in solving it.






Re: REPLACE command help wanted

Dave M.

>>Folks, Cetin may not be around for a few hours since he is in a different time zone. What in the hell did he mean by >>DDL I cannot find it anywhere

Did you try Google

http://www.google.com/search hl=en&q=ddl






Re: REPLACE command help wanted

AlexBB

Dave M. wrote:

DDL =Data Definition Language

Thanks, Dave. It makes it a little bit more clear. So, he wants me to use SQL command REPLACE directly from within .NET with CommandText = SQL and ExecuteNonQuery after that and no stored procedures involved. But he contradicts himself by saying that I cannot use long strings to pass as a memo. So this is a circuitous argument. Aside I have tried almost all conceivable variants and always got "Attempt to read/write protected memory" or "Syntax Error" exception. I am still trying at the rate of about 15 variants per hour.






Re: REPLACE command help wanted

AlexBB

Dave M. wrote:

>>Folks, Cetin may not be around for a few hours since he is in a different time zone. What in the hell did he mean by >>DDL I cannot find it anywhere

Did you try Google

http://www.google.com/search hl=en&q=ddl

I don't google. I hate the omnipresent beast. I use Fox Help instead. I could not find it in there. It is a Fox forum. Why should I google For what reason I am sure I can find three hundred acronyms like this under DDL: Department of Defense Laboratory for instance.