I have a Visual Basic application that uses the Visual FoxPro Driver to manage .DBF files (tables) created by Clipper. These tables are "free tables" and thus cannot have a Primary Key assigned to them. Can someone provide the code to create a Candidate Key? Thanks.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Code to Create a Candidate Key? 1
- Thread starter Sware
- Start date
Olaf Doschke
Programmer
Hi Sware,
There is a problem, because what you send to the ODBC or OLEDB driver is...
...and that does not work.
You need to have a single line of code to be executed, so do:
Bye, Olaf.
There is a problem, because what you send to the ODBC or OLEDB driver is...
Code:
Execscript(SELECT 0
USE C:\Program Files\Microsoft Visual Studio\VB98\GHM_V70.DBF IN 0 EXCLUSIVE
INDEX ON GS_ID TAG GS_ID CANDIDATE)...and that does not work.
You need to have a single line of code to be executed, so do:
Code:
lcStr = "'SELECT 0' + chr(13)+chr(10)+" & _
"'USE " & App.Path & "\GHM_V70.DBF IN 0 EXCLUSIVE'+ chr(13)+chr(10)+" & _
"'INDEX ON GS_ID TAG GS_ID CANDIDATE'"
conn1.Execute "EXECSCRIPT(" & lcStr & ")"Bye, Olaf.
Mike Lewis
Programmer
Another thought ...
I wonder if it's really necessary to create this candidate key after all.
SWare, the only reason you need the key is so that your RecordSet object can use it in its WHERE clause, in place of all the separate fields that you had originally. Given you have a unique identifier for the record (I assume that's what GS_ID is), you should be able to simply tell your RecordSet that this is the primary key. (We still don't know how to do that, but that's a separate issue.)
In other words, you fool the RecordSet into thinking that GS_ID is a primary key -- even if it isn't. The RecordSet will go ahead and generate the correct WHERE clause, which the OLE DB driver should have no dificulty in interpreting.
The fact that this is not really the primary key shouldn't matter. There are however two caveats:
- GS_ID has really got to be unique. If it isn't, you'll be updating multiple records.
- The update will take longer, as Foxpro will have to look at each record in turn until if finds the one that needs updating.
Understand, I'm not completely sure about any of the above. But it must be worth a try.
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
My Visual FoxPro site: www.ml-consult.co.uk
I wonder if it's really necessary to create this candidate key after all.
SWare, the only reason you need the key is so that your RecordSet object can use it in its WHERE clause, in place of all the separate fields that you had originally. Given you have a unique identifier for the record (I assume that's what GS_ID is), you should be able to simply tell your RecordSet that this is the primary key. (We still don't know how to do that, but that's a separate issue.)
In other words, you fool the RecordSet into thinking that GS_ID is a primary key -- even if it isn't. The RecordSet will go ahead and generate the correct WHERE clause, which the OLE DB driver should have no dificulty in interpreting.
The fact that this is not really the primary key shouldn't matter. There are however two caveats:
- GS_ID has really got to be unique. If it isn't, you'll be updating multiple records.
- The update will take longer, as Foxpro will have to look at each record in turn until if finds the one that needs updating.
Understand, I'm not completely sure about any of the above. But it must be worth a try.
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
My Visual FoxPro site: www.ml-consult.co.uk
- Thread starter
- #23
No go on two fronts.
First I used Olaf's new code with the VFPOLEDB connection. I got the following error on the conn1.Execute statement:
-2147467259(80004005): Syntax Error
Even though I believe someone mentioned that EXECSCRIPT was for OLEDB only, I also tried Olaf's new code on the ODBC connection. It failed on the conn1.Execute statement:
-2147217911(80040e09): Syntax Error or Access Violation
On the second front, I constructed a SQL statement to try an Update without using rs1.Update, as follows:
UpdSql = "UPDATE GHM_V70.DBF SET GS_ID = PA(0), PLAY_OVER =
PA(1), PLAY_STAT = PA(2), PLAY_DUES = PA(3), .....
...... (for rest of the 87 fields to PA(86)
WHERE GS_ID = PA(0)
conn1.Execute UpdSql
Note that I changed the previous PlyrArray name to PA to reduce the number of bytes in the UpdSql string. I got the following error on the conn1.execute statement:
-2147217865(80040e37): pa.prg does not exist
Note the correspondence of "pa.prg" with the name of the PA array used in the UpdSql statement. I patterned the Sql statement after the content of the VFP Trace output of the failed rs1.Update statement from previous testing. Obviously, this may be incorrect.
By the way, I also tried the original rs1.Update statement with the shorter PA array name. I still got the "Statement Too Long" error.
I guess there's a third front - Mike's idea of fooling the recordset into thinking that GS_ID is the Primary field. Nobody has commented on how to do that.
First I used Olaf's new code with the VFPOLEDB connection. I got the following error on the conn1.Execute statement:
-2147467259(80004005): Syntax Error
Even though I believe someone mentioned that EXECSCRIPT was for OLEDB only, I also tried Olaf's new code on the ODBC connection. It failed on the conn1.Execute statement:
-2147217911(80040e09): Syntax Error or Access Violation
On the second front, I constructed a SQL statement to try an Update without using rs1.Update, as follows:
UpdSql = "UPDATE GHM_V70.DBF SET GS_ID = PA(0), PLAY_OVER =
PA(1), PLAY_STAT = PA(2), PLAY_DUES = PA(3), .....
...... (for rest of the 87 fields to PA(86)
WHERE GS_ID = PA(0)
conn1.Execute UpdSql
Note that I changed the previous PlyrArray name to PA to reduce the number of bytes in the UpdSql string. I got the following error on the conn1.execute statement:
-2147217865(80040e37): pa.prg does not exist
Note the correspondence of "pa.prg" with the name of the PA array used in the UpdSql statement. I patterned the Sql statement after the content of the VFP Trace output of the failed rs1.Update statement from previous testing. Obviously, this may be incorrect.
By the way, I also tried the original rs1.Update statement with the shorter PA array name. I still got the "Statement Too Long" error.
I guess there's a third front - Mike's idea of fooling the recordset into thinking that GS_ID is the Primary field. Nobody has commented on how to do that.
Mike Lewis
Programmer
That's right. It's no good sending a subscripted array element to Foxpro. The array exists in your VB code, so the Foxpro driver has no way of knowing about it. You need to substitute the actual value from the array.
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
My Visual FoxPro site: www.ml-consult.co.uk
Mike Lewis
Programmer
Sware,
OK, I've been digging into my old notes on ADO. The following might help.
By default, when you send the UPDATE, ADO will include all the changed fields in the WHERE clause. You already know that. But it seems there is a setting that tells it to put only the primary key in the WHERE clause, and nothing else.
To illustrate using your original code:
For i = 0 To 86
If rs1.Fields(i) <> PlyrArray(i) Then
rs1.Fields(i) = PlyrArray(i)
End If
Next i
rs1.Properties("Update Criteria").Value = adCriteriaKey
rs1.Update
That might just solve the problem. Note that we haven't yet told it what the primary key is. I'm betting that you don't need to do that, because FoxPro already knows it (or thinks it does).
Perhaps you could just try the above, and let us know the result. If it looks like you still need to specify the primary key, we can go further with my plan for fooling it.
If you get a syntax error saying it doesn't recognise adCriteriaKey, put 0 in there instead.
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
My Visual FoxPro site: www.ml-consult.co.uk
OK, I've been digging into my old notes on ADO. The following might help.
By default, when you send the UPDATE, ADO will include all the changed fields in the WHERE clause. You already know that. But it seems there is a setting that tells it to put only the primary key in the WHERE clause, and nothing else.
To illustrate using your original code:
For i = 0 To 86
If rs1.Fields(i) <> PlyrArray(i) Then
rs1.Fields(i) = PlyrArray(i)
End If
Next i
rs1.Properties("Update Criteria").Value = adCriteriaKey
rs1.Update
That might just solve the problem. Note that we haven't yet told it what the primary key is. I'm betting that you don't need to do that, because FoxPro already knows it (or thinks it does).
Perhaps you could just try the above, and let us know the result. If it looks like you still need to specify the primary key, we can go further with my plan for fooling it.
If you get a syntax error saying it doesn't recognise adCriteriaKey, put 0 in there instead.
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
My Visual FoxPro site: www.ml-consult.co.uk
- Thread starter
- #26
- Thread starter
- #27
Okay, back to the Update statement approach. I created 87 individual string variables (A0$ through A86$), filled them from the PA array, and used them in the construction of the UpdSql statement. The last part of that is as follows:
UpdSql = UpdSql + "WHERE GS_ID = A0$"
I get the following error on the conn1.Execute UpdSql statement:
-2147467259(80004005): Missing Operand
The beat goes on!
UpdSql = UpdSql + "WHERE GS_ID = A0$"
I get the following error on the conn1.Execute UpdSql statement:
-2147467259(80004005): Missing Operand
The beat goes on!
Olaf Doschke
Programmer
Hi Sware,
I still oversaw one thing: You have spaces in the path to the dbf. Put the name in []:
Bye, Olaf.
I still oversaw one thing: You have spaces in the path to the dbf. Put the name in []:
Code:
lcStr = "'SELECT 0' + chr(13)+chr(10)+" & _
"'USE [COLOR=black yellow][[/color]" & App.Path & "\GHM_V70.DBF[COLOR=black yellow]][/color] IN 0 EXCLUSIVE'+ chr(13)+chr(10)+" & _
"'INDEX ON GS_ID TAG GS_ID CANDIDATE'"
conn1.Execute "EXECSCRIPT(" & lcStr & ")"Bye, Olaf.
Olaf Doschke
Programmer
Sware,
"fooling" the recordset about a field being the primary key: I did not find something about that, but an AdoDB.Recordset has an Index-Property, which would allow you to set order to that index. Simply by:
This will only work, after you have that index defined. It will also work with normal indices, not just candidate/primary.
Bye, Olaf.
"fooling" the recordset about a field being the primary key: I did not find something about that, but an AdoDB.Recordset has an Index-Property, which would allow you to set order to that index. Simply by:
Code:
Dim rs as AdoDB.Recordset
'... retrieve data to the rs
rs.index="GS_ID"This will only work, after you have that index defined. It will also work with normal indices, not just candidate/primary.
Bye, Olaf.
- Thread starter
- #30
Hi Olaf,
That doesn't do it. It did resolve the previous syntax error but I still get the Multi-Step error with the OLEDB connnection on executed.. BTW, I did Debug.Print and the following is what results in the part of the statement where you suggested the [ ] characters:
'USE C:\Program Files\Microsoft Visual Studio\VB98\GHM_V70.DBF
I have to go out of the office for awhile. Perhaps I'll stop at a church on the way back!!!
That doesn't do it. It did resolve the previous syntax error but I still get the Multi-Step error with the OLEDB connnection on executed.. BTW, I did Debug.Print and the following is what results in the part of the statement where you suggested the [ ] characters:
'USE C:\Program Files\Microsoft Visual Studio\VB98\GHM_V70.DBF
I have to go out of the office for awhile. Perhaps I'll stop at a church on the way back!!!
- Thread starter
- #31
Olaf,
Now I am going to stop at a church on the way back!
With the rs1.Index = "GS_ID" statement i get the following error with both the VFPOLEDB and ODBC connections:
"Current provider does not support the necessary interface for Index functionality"
Now I am going to stop at a church on the way back!
With the rs1.Index = "GS_ID" statement i get the following error with both the VFPOLEDB and ODBC connections:
"Current provider does not support the necessary interface for Index functionality"
Which VFPOleDB do you have? Get the latest from
- Thread starter
- #33
Let's try a different approach.
Create a file in notepad called CreateIndex.prg
Put there (note, that we would have to hard code the path now)
use [mylongpathTable] in 0 exclusive alias test
select test
index on MyField tag MyTag candidate
In the connection try:
conn1.Execute "EXECSCRIPT(filetostr(fullapth to my file))"
I need to check, if filetostr is supported by OleDB. I bet it isn't otherwise it's a simple way of hajacking.
Create a file in notepad called CreateIndex.prg
Put there (note, that we would have to hard code the path now)
use [mylongpathTable] in 0 exclusive alias test
select test
index on MyField tag MyTag candidate
In the connection try:
conn1.Execute "EXECSCRIPT(filetostr(fullapth to my file))"
I need to check, if filetostr is supported by OleDB. I bet it isn't otherwise it's a simple way of hajacking.
It is not listed as unsupported here so it may be a working approach.
- Thread starter
- #37
WITH the single quotes inside the double quotes -- get through the Execute statement for EXECSCRIPT without error but get Multi-Step error on rs1.Update.
WITHOUT the single quotes inside the double quotes -- get the following error on the Execute statement for EXECSCRIPT: Function name missing.
WITHOUT the single quotes inside the double quotes -- get the following error on the Execute statement for EXECSCRIPT: Function name missing.
- Thread starter
- #38
- Thread starter
- #40
- Status
Similar threads
