foxpro SQL functions

[kevclark64]

I've been checking out the new foxpro SQL functions (SqlConnect, SqlExec) and I must say that I'm quite impressed. I have noticed two issues, however, in how the data is converted from SQL into DBF format. First, fields which are only date and not datetime are all converted to datetime. Secondly, fields which are numeric with a specific number of decimal places (such as N(10,2) for overall length and decimals) all seem to be converted into currency. I'm working with Postgres data, so I don't know if this is the same for other SQL backends.

[robert]

Kevin,
I need an example of this.

Robert

[kevclark64]

I've attached 2 DBF files of a SqlExec of a video item table. One is directly from Foxpro and the other is a SqlExec and then "copy to [filename]" from XSharp. Between these two files you can see 3 main differences: 1) date fields become datetime fields, 2) fields which are .NULL. in Foxpro are empty in XSharp, 3) field names longer than 10 chars are retained in Foxpro but truncated in XSharp. (I mentioned in my last post the issue of numeric fields turned into currency fields, which this table does not have, but I will try to post later on.)

The attachment sqlexecexamples.zip is no longer available

The field definition of this table in Postgres is:

movieortv character(1) NOT NULL DEFAULT 'M'::bpchar,
videoid serial NOT NULL,
videoname text NOT NULL DEFAULT 'Movie or TV Show Name'::text,
videotypes text,
videoyear integer,
comment text,
imdbid character(10) NOT NULL,
rating character(10),
netflixid character(20),
amazonid character(20),
deleted boolean DEFAULT false,
createdate date NOT NULL DEFAULT ('now'::text)::date,
posterurl text,
usccbrating character(10),
screenitreview text,
decentfilmsreview text,
cnsreview text,
dontdisplay boolean NOT NULL DEFAULT false,
filtersummary text,
disneyid character(50)

[robert]

Kevin,
I see this:

1) For this I need to know which ODBC driver. you used to fetch the data: by the time we get to see the Sql result set the driver and the Ado.Net ODBC provider have already done some translations from SQL type to the appropriate .Net type, and (unfortunately) there is no DATE time in the framework. Can you mail me the ODBC driver and maybe also a small Postgres sample database with this table. It has been a while since I worked with Postgres.
2) there is no NULL_FLAGS field in the DBF created by X#. There should have been. I think there is a problem in the code behind COPY TO. I'll look into this.
3) The FoxPro DBF file also has truncated names. FoxPro stores the longer field names in the DBC AFAIK. Was this file part of a DBC on your machine ? We are retrieving the longer names too, but when creating the file in your COPY TO command we have to truncate the names to 10 characters.

Robert

[robert]

Kevin,
I have identified why the NULL_FLAGS were not set properly when exporting the SqlExec() data to a DBF. This is fixed now and will be included in the next build.
For the Date -> DateTime conversion I really need more info...

Robert

[kevclark64]

Robert, thanks for the null update. Regarding the date -> datetime conversion, let me do a little more testing on that just to be sure what's going on.

Kevin