Not a table problem extracting decimal fields

[MikeMV]

Hello,

I am using VPF 9.0 SP2 trying to extract data from an MS SQL Express 2008. I am getting an error of "is not a table" when I try to append from an offline view. I tried deleting and re-creating everything, but I am still having the problem. I can do an "use vbom" to open the offline view and I can browse, but when I try to append from it I get the error.

The only thing different for me in this table is that the quantity and quantity per fields are decimal(38,20) When I try to browse the offline view I get an error of DataType property for field 'Quantity' is invalid. I can get around this by using the following prior to browsing:

DBSETPROP('vbom.quantity_per','Field','DataType','N(1)')
DBSETPROP('vbom.quantity','Field','DataType','N(1)')

If I eliminate extracting those 2 decimal fields the problem does not show up.

I will greatly appreciate any feedback you can provide.

Mike.



OPEN DATABASE MondoluzInvFeed
SET DATABASE TO MondoluzInvFeed
*
DELETE VIEW vbom
CREATE SQL VIEW vbom;
REMOTE CONNECTION MondoluzData;
AS select;
[Mondoluz$Production BOM Line].[Production BOM No_],;
[Mondoluz$Production BOM Line].[No_],;
[Mondoluz$Production BOM Line].[Quantity],;
[Mondoluz$Production BOM Line].[Quantity per];
FROM [Mondoluz$Production BOM Line];
ORDER BY [Mondoluz$Production BOM Line].[Production BOM No_],[Mondoluz$Production BOM Line].No_
*
CREATEOFFLINE ("vbom")
*
USE bom
*
DELETE ALL
PACK
REINDEX
*
APPEND FROM vbom
REINDEX
*
CLEAR
CLEAR EVENTS
CLOSE ALL
CLOSE DATABASES ALL

 

[JRB-Bldr]

My first question is - why are you doing this with a Remote View?

Why don't you use SQL Pass-thru to acquire your SQL Server results?

Over time I have transitioned from VFP Remote Views into SQL Server data tables to SQL Pass-thru because it seemed to be much more reliable.

If you did it this way you could get back a Cursor containing the data.

OPEN DATABASE MondoluzInvFeed
SET DATABASE TO MondoluzInvFeed

cSQLCommand = <build your T-SQL command string>

nHandle = SQLConnect('MondoluzData')
nRet = SQLEXEC(nHandle,cSQLCommand,'Result')
IF nRet = 1
   * --- Successful execution of SQL Command ---
   SELECT Results
   <do whatever>
ELSE
   * --- Execution of SQL Command NOT Successful ---
   <do whatever>
ENDIF

=SQLDISCONNECT(0)

NOTE - if you do use this approach test your SQL Command string independently of VFP in the SQL Server Management query window to ensure that the command's syntax is properly formed and that it will return what you expect.

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

Decimal(38,20) surely is a decimal precision foxpro does not support. "Is not a table" is surprising anyway. I would assume VFP converts to something like float, but actually you have a problem with the field type incompatibility. Do you really need such huge numbers with such high precision in Sql Server?

Try
DBSETPROP('vbom.quantity_per','Field','DataType','B')
DBSETPROP('vbom.quantity','Field','DataType','B')

And then query into the view.

You don't need Createoffline, though, just to get a dbf file, you can APPEND FROM DBF("aliasname") with cursors or view cursors, with any alias, even if not stored on disc.

So you could USE vbom instead of CREATEOFFLINE("vbom") and then finally APPEND FROM DBF("vbom") instead of APPEND FROM vbom.

But the DataType issue is not optional, you need to adress this either in VFP or on the SQL Server side.

Bye, Olaf.

 

[MikeMV]

Thanks a lot for your help guys.

Olaf,

Your suggestion of using "DataType','B' worked, it eliminated the "not a table" error.

Unfortunately I am stuck with the SQL table design, this is part of a Microsoft Dynamics NAV ERP system and I can't change it.

Thanks also for the tip to eliminate the CREATEOFFLINE step.

 

[Olaf Doschke]

Ok, fine. "B" stands for float in VFP, so in case you want a fixed decimals number you could also use N(x,y), but surely not N(1), or you could use CONVERT or CAST in the SQL Select to convert or cast the N(38,20) type to something better fitting your needs. In any case you could significant places in the worst case or unsignifican places in the east worst case. With CONVERT you could also convert to a char to get the exact precision of the data at least as a string, but that's of course not handy, if you need to compute with these numbers.

A reference to CAST and CONVERT in MS SQL Server:

http://msdn.microsoft.com/en-us/library/ms187928.aspx

With these T-SQL functions you can also apply jrbbldrs suggestion to use SQL Passthrough, that is SQLEXEC(), otherwise you get the same type problem as with a view, as vfp then guesses and obvioulsy guesses wrong, what type to use on the vfp cursor.

Using CAST or CONVERT obviously has more options to handle the numbers as needed. Float has a high range of valid numbers of course, but has it's precision limits, if that matters.

Bye, Olaf.

 

[Mike Lewis]

Olaf, you said: "B stands for float in VFP". Just to be completely accurate:

In VFP, float is not floating-point. It is not an alternative to double. It is exactly the same as numeric (type N), which is like SQL Server's Decimal.

VFP allows you to define a numeric field and refer to it as floating point, even though it isn't floating point. This is done for compatibility with dBASE IV, for which a floating point data type was announced but never implemented.

Of course, this does not invalidate the good advice you gave to Mcampos, all of which is perfectly correct. Data type B does indeed give floating point in the true sense of the word, even though it's not called that in the VFP documentation. I just thought I'd point this out, in the expectation of confusing anyone who needs to be.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

>I just thought I'd point this out, in the expectation of confusing
anyone who needs to be.

he he.

Isn't that my job?

You're right about float, but I was wrong about "B", in reference to the help topic on CREATE TABLE, B really means double indeed, and is really floating point, while F is for float and indeed identical to N, really storing values as strings.

Bye, Olaf.

 

[Mike Lewis]

Olaf,

... in reference to the help topic on CREATE TABLE, B really means double indeed, and is really floating point, while F is for float and indeed identical to N, really storing values as strings.

Exactly.

Isn't that my job?

Well, let's call it a team effort.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[MikeMV]

Thanks again for all your help and very informative reply.

We put together lighting fixtures, our inventory never has decimals, no one wants to buy half a lamp ;~)

 

[JRB-Bldr]

If your Inventory never has decimals, then a good part of this thread was never really needed - other than as a general opportunity to learn more.

If you don't have decimal values in your 2 fields
[Mondoluz$Production BOM Line].[Quantity per]
[Mondoluz$Production BOM Line].[Quantity]
then there was no need to maintain the table structure in your VFP application to accommodate the non-existent decimal values.

You could have just modified your query syntax to return only the Integer part of the original table's field values
Such as:
CAST([Mondoluz$Production BOM Line].[Quantity per] AS Int) AS Qty_per

Good Luck,
JRB-Bldr