Foxpro Database Question

kermitdfrog

Hey all,

Would really appreciate any help on two queries.

1) I've a foxpro database I need to query, insert and update from SQL. I'm using a linked server, and can query and insert rows no problem. However I am unable to update rows. Any suggestions?

2) Foxpro not being too fussy and the users being idiots, there is often bad data. More specifically, dates in the date fields have been entered as 01/01/204 and such, which causes errors when I try to return these rows through a query from SQL. Any means to select the datac without the errors, preferably with a NULL or set value where the date is not in the applicable date range acceptable in SQL?

Adam

I'm not experienced with foxpro, but I would imagine if you can query and insert rows you should be able to update too... are you getting sql errors or what happens?

As for bad data, you should(assuming the data is entered from website forms etc.) validate date fields and either make it mandatory to have the date in the correct format or convert the date to the correct format before it goes into the database.

kermitdfrog

Chhers for the reply

fade_to_grey wrote:

I'm not experienced with foxpro, but I would imagine if you can query and insert rows you should be able to update too... are you getting sql errors or what happens?

Getting SQL errors - unfortunately I can't gain access to the machine I've been workign from until the morning to give the exact error, but will do in the A.M.

fade_to_grey wrote:

As for bad data, you should(assuming the data is entered from website forms etc.) validate date fields and either make it mandatory to have the date in the correct format or convert the date to the correct format before it goes into the database.

No, you misunderstand - I have no control over the data input into the foxpro database, I am simply replicating it into the SQL database. When this is queried, either through a linked server or a openrowset, it returns a SQL error because the fields which should correspond to datetime data types contain invalid data

Adam

Ah right ok. Well when running a query on the database you could set the select where the field values correspond to a specific format I imagine, what would your query sql look like?

kermitdfrog

fade_to_grey wrote:

Ah right ok. Well when running a query on the database you could set the select where the field values correspond to a specific format I imagine, what would your query sql look like?

Have tried that to no avail!

I realise it'd easier if I had some examples of my queries and the errors, but for some reason my vpn access is down and it'll have to wait until the morning.

Adam

Well I don't think I can provide much until then, maybe others have some experience in this. I'll have a look tomorrow if you post your sql errors!

kermitdfrog

Thanks, appreciate your time anyway

kermitdfrog

Ok, bit more info....

On the datetime issue, if I was to run a query, say:
select * from LinkedServer...table


..the query runs through the rows until it hits a bad date field, then returns the followng error:
Error converting data type DBTYPE_DBTIMESTAMP to datetime.

If the first 100 rows were ok, but the 101st had a bad date then a TOP 100 * in the query runs ok, but if you run TOP 101 * then you get the error.



Problem 2: If I try to update a table, for example:

update LinkedServer...table
set column1 = ' '
where column2 = ''

..I get the following error:
Could not fetch a row using a bookmark from OLE DB provider 'VFPOLEDB'.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.]
OLE DB error trace [OLE/DB Provider 'VFPOLEDB' IRowsetLocate::GetRowsByBookmark returned 0x80040e21: 4f010000].

Any help would be much appreciated

liamo

How about doing the validation in the SQL Statement.

Something like : Select IIF(Year(DateField) < 1900, NULL, DateField) As MyDate From MyTable

Regards,

Liam

kermitdfrog

Nice idea, but I've tried that (well, using case - there's no IIF in SQL), and it returns the same Error converting data type DBTYPE_DBTIMESTAMP to datetime. error when encountering bad data as it has trouble carrying out the Year() function on an invalid datefield

liamo

I agree that there's no IIF in SQL, however this is processed by VFP via VFPOLEDB so I wonder if it might work .....

I ran into a similar type of problem when I was trying to convert an Oracle DB to SQL Server. Oracle, like VFP in this case, supports much smaller dates than SQL server. We eventually had to do it via the DTS feature in SQL server and write some custom scripts to transform the dates to text and test the resultant text for validity before transforming the text back to a valid date. Sheesh!

Anyway, back to your problem .....

How about creating a view in the VFP DB with the aforementioned IIF test creating a calculated field instead of the actual date field. You should then be able to query the view directly.

Regards,

Liam