update sql table with foxpro with remote view, connectivtiy error

[JICGreg]

I often update sql tables from foxpro using a remote view. The sql programmer takes over after that -- so fox is just used to get data into sql.

I'm trying to do this with a new sql table and I am running into problems. I used the foxpro upsizing wizard to get the table created in sql.

I have an existing odbc connection that I use for other tables and I have created my remote view -- I can browse the sql table via this remote view.

I then follow my "normal" process to update a table, but I'm getting this error:

Connectivity error: [MICROSOFT][odbc SQL Server Driver] Numeric value out of range.

Below is my code -- any insights as to why I'm getting this error would be really appreciated.



PROCEDURE upload_data_to_sql
CLOSE TABLES all

SELECT g
USE n:\research\trading\transaction_data\trading
DELETE ALL
PACK

SELECT c
USE n:\research\trading\transaction_data\&manager_allocation_name
SCAN
SCATTER memvar
SELECT g
APPEND blank
GATHER memvar
ENDSCAN

STORE "n:\research\trading\transaction_data\transaction_data" to dbasename
STORE "trading" TO foxpro_source_table
STORE "trading" TO sql_source_table
STORE "n:\research\trading\transaction_data\" to directory
STORE "view_trading" TO table_view

STORE "delete FROM " + sql_source_table TO table_select

lnConn = SQLCONNECT("JicReturns",.T.)
nret=SQLEXEC(lnConn,table_select)
IF nRet = 1
CLEAR
@10,10 say "success"
ELSE
@10,10 say "failure"
fdsa
endif
SQLDISCONNECT(lnConn)


CLOSE TABLES all
OPEN DATABASE &dbasename
USE &table_view

STORE directory + foxpro_source_table + ".dbf" TO file_name
APPEND FROM &file_name
TABLEUPDATE(.T.)

endproc

 

[JRB-Bldr]

Have you tried to run the SQL Command directly within the SQL Server Maintenance utility?

dbasename = "n:\research\trading\transaction_data\transaction_data"
foxpro_source_table = "trading"
directory = "n:\research\trading\transaction_data\"
table_view = "view_trading"
sql_source_table = "trading"

* --- Define SQL Query Command String ---
table_select = "delete FROM " + sql_source_table

[b]SET STEP ON[/b] <=== Added For VFP Debugging

lnConn = SQLCONNECT("JicReturns",.T.)
nret=SQLEXEC(lnConn,table_select)

When the code does its Debug Break at the SET STEP ON, copy the SQL Command (table_select) and then paste it into a New SQL Query window within the SQL Server Maintenance Utility to confirm its syntax.

Looking at the command, it appears as though you are missing either a '*' (asterix) to Delete everything or a WHERE clause.

Good Luck,
JRB-Bldr

 

[Dan Freeman]

There's a lot about your code that could be fixed. (Don't hard-code workareas. Why DELETE ALL/PACK when ZAP would do it in one command? Why scan/scatter when you could do it with one SQL-INSERT command?)

But the overriding question I'm wondering about is why you're asking about a view when you're sending SQL pass through.

I wonder what else is being left out of the question.

Based on the error message you're reporting, I'd bet the upsizing wizard created a numeric of the wrong type on the back end and your actual data is overflowing it but since the command you're sending is a DELETE it seems you're not updating either.

Can you clarify?

 

[Olaf Doschke]

From what Dan said and from the error being a numeric overflow, I guess the problem is just with the final TABLUPDATE(.T.):

OPEN DATABASE &dbasename
USE &table_view

STORE directory + foxpro_source_table + ".dbf" TO file_name
APPEND FROM &file_name
TABLEUPDATE(.T.)

so the problem is in the data you append from &file_name and the numeric range of the sql server prohibits to send over some data.

The problem is not seen in your code, it's about the data. If you have eg an N(3,1) field in VFP you can store values like 1.1, but also 999, because mainly you have 3 places. In SQL Server only values from -9.9 to 9.9 would be allowed. There's a mismatch in definitions. Easiest solution would be to change some numeric field that could cause this on the SQL Server side with float or double float type fields.

Bye, Olaf.

 

[Olaf Doschke]

And by the way: Even simple SQL syntax errors or type errors or any other problem with a query you run starts with "Connectivity Error", but isn't necessarily a connectivity problem, LAN or connection handle lost or such, but simply a problem with the query itself or the data. The detail message about a value out of range couldn't occur, if no values would arrive at the server side, could it?

Bye, Olaf.

 

[JICGreg]

Thank you all for your responses. I will go through the sql table and check the data structure as well as my foxpro data to try and locate the problem data. I'll post any additional questions.

Greg