5p1d3r


We are wanting to create new records in a FoxPro database from c#.

The database in question does not have autoinc, but has a 'sequence' table instead.

The 'sequence' table has two columns 'file' and 'next_val'.

To get the next primary key for the CUST table, simply:

SELECT next_val FROM sequence WHERE file = 'CUST';

UPDATE sequence SET next_val = next_val + 1 WHERE file = 'CUST';

PROBLEM;

We cannot seem to lock the row to prevent contention and stop two users getting the same next_val;

How do we apply a pessimistic lock to rows in the sequence table




Re: Locking VFP rows from C#

Fox Me Up


ive never mixed c# and dbf files but couldnt you just lock a file before righting to it and then unlock it once your done





Re: Locking VFP rows from C#

5p1d3r

What do you mean by 'lock a file'

Are you suggesting that we open it for writing, query it and then close it

You can't do that because the file lock would prevent vfoledb from accessing it.






Re: Locking VFP rows from C#

Naomi Nosonovsky

I think you're using the wrong methodology and thus now have problems. If your tables are part of a database, then you just need a stored procedure in the VFP database to create new ID. This methodology is well known and it's documented on Craig's Bernston website http://www.craigberntson.com/Articles/kb006.htm.

If your tables are free tables, it would complicate the matter a little bit.





Re: Locking VFP rows from C#

5p1d3r

Thanks for that.

How do we tell what version of VFP the tables are in

How do we determine if they are "free tables" (there is no .dbc file, only .dbf, .cdx, .fpt)

How do we list existing stored procedures

We have Visual Studio 6.0 and using this we can open the tables.





Re: Locking VFP rows from C#

CetinBasoz

You have several options.

-Use Mode attribute of connection string. You can set it to Deny Read,Deny Write or Share Exclusive. You'd be then updating with a file level lock.

-Using VS6.0 (it contains VFP6.0) create a DBC and edit its stored procedures (in command:

create database myDatabase

modify procedures

)

-Try a trick (not sure if would work, didn't try before from .Net) and add rlock() to your where clause.

-Use transaction

-If none works using StringBuilder write a script instead of a stored procedure, call the script as if it were a stored procedure (search here for the samples posted previously many times).





Re: Locking VFP rows from C#

5p1d3r

Found the holy grail: http://www.west-wind.com/presentations/VFPDOTNETiNTEROP/VFPDOTNETINTEROP.HTM

Thanks everyone for your help!





Re: Locking VFP rows from C#

Naomi Nosonovsky

Thanks for sharing the link.

AFAIK your tables are free tables since there are no DBC.





Re: Locking VFP rows from C#

5p1d3r

FOLLOW UP: I've created a DBC and setup a stored procedure based on http://www.craigberntson.com/Articles/kb006.htm

Now trying to test SP through Command window in VFP 6.0

ERROR: File 'c:\program files\microsoft visual studio\vfp98\sequence.dbf' does not exist.

Why is it looking here

I've created my DBC on M:\BD and I certainly do not want to put "M:\BD" into my stored procedure.

HOME() function does not seem to provide a database location path...

Is there a 'whereever the database is' variable rather than 'whereever the exe is'

FUNCTION SequenceGet
LPARAMETERS TableName

LOCAL lnArea, llOpened, iReturn

lnArea = SELECT()
llOpened = .F.

IF !USED("Sequence")
USE Sequence IN 0 SHARED
llOpened = .T.
ENDIF
SELECT Sequence
SET ORDER TO TAG File

DO WHILE !FLOCK()
ENDDO

SEEK UPPER(TableName)
IF FOUND()
iReturn = Next_Val
REPLACE Next_Val WITH Next_Val + 1
ELSE
INSERT INTO Sequence (File, Next_Val) VALUES (TableName, 2)
iReturn = 1
ENDIF

FLUSH
UNLOCK

IF llOpened
USE IN Sequence
ENDIF

SELECT (lnArea)

RETURN iReturn

ENDFUNC





Re: Locking VFP rows from C#

Naomi Nosonovsky

Try to prefix Sequence with the database name, e.g. MyDBC!Sequence at the USE command.





Re: Locking VFP rows from C#

5p1d3r

Tried that.

Looks like the stored procedure is working fine from c#, but I was trying to come to grips with the VFP development environment.

For anyone who is interested, here is the function I use to call the VFP stored procedure from C# :

private int SequenceGet(string Connection, string TableName)

{

int RowId = 0;

OleDbConnection con = new OleDbConnection(Connection);

string sql = string.Format("SequenceGet('{0}')", TableName);

OleDbCommand cmd = new OleDbCommand(sql, con);

try

{

con.Open();

RowId = Convert.ToInt32(cmd.ExecuteScalar());

}

catch (Exception ex)

{

RowId = -1;

}

finally

{

con.Close();

con.Dispose();

cmd.Dispose();

}

return RowId;

}





Re: Locking VFP rows from C#

Naomi Nosonovsky

Thanks.

To call it from VFP6 you probably need once you switched to the directory with the database.

OPEN DATABASE myDatabase
sequenceget('MyTableName')