VFP Datetime() & Remote View

[stanlyn]

Hi,

I'm having issues in comparing datetime coming from a remote view. Backend is remote MSSQL. VFP8sp2. Attached is a screenshot showing outputs to the screen, and easy to follow.

The sql data is stored as datetime2(0). When eching the result of ?myUser.ExpireTime I get '2021-02-09 04:58:42' and is later confirmed as character type by echo the result of ?Type('myUser.ExpireTime')

Issuing ?Ctot(myUser.ExpireTime) echos ' / / : : ' and I believe that the issue is somehow here, as I would expect it to have a non empty value.

When evaluating the datetime from the remote view against plain ole datetime(), the results do not change as the screen echoing clearly shown.

What is going on? Any suggesions?

Thanks, Stanley

CREATE SQL VIEW myUser REMOTE CONNECTION DeedroomConn AS SELECT * FROM UserSubscription

Use 'myUser'
Select 'myUser'
Set Filter To
Go Top

Set Hours To 24
Set Century On
Set Date AMERICAN   &&USA

*CLEAR
*Set Filter To  myUser.ExpireTime > Ttoc(Datetime(),3) .;
	*And. myUser.Userid = 'TU0001KY131'

? myUser.ExpireTime
? Ctot(myUser.ExpireTime)
? Type('myUser.ExpireTime')
? Datetime()

? Ttoc(Datetime()) < myUser.ExpireTime
? Ttoc(Datetime(),1) < myUser.ExpireTime
? Ttoc(Datetime(),2) < myUser.ExpireTime
? Ttoc(Datetime(),3) < myUser.ExpireTime

? Ttoc(Datetime()) > '2021-02-09 04:58:01'

 

[Chris Miller]

You're not defining yout remote view detailed, just as SELECT * query. That means VFP and/or ODBC are inferring (guessing) the conversions from SQL Server types to VFP types. I don't know why this results in a character type, but that's your main problem. You see SQL Server/ODBC formats it different from TTOC, the string formats therefore are not comparable. And even if format is the same, the date parts would need to be in the string ordered by significance, ie year,month,day,hour,minute,second. This also depends on MSSQL settings, so your best way out is getting the MSSQL datetime2 type as a VFP datetime for compraison with DATETIME().

If you create the view with the view designer you can select all fields instead of using * and you can then see what data types are, and you can change them. So you can enforce ExpireTime to be put into a T field. So use the view designer for that, and if you then want to still use code to create views on the fly, the view designer also offers a SQL mode and shows you the CREATE SQL VIEW plus DBSETPROP necessary to specify types instead of inferring them.

Chriss

 

[atlopes]

Stanley,

You'll get the datetime value from the server with

EVALUATE("{^" + myUser.ExpireTime + "}")

Also, take note that when using a more recent ODBC driver (from Native Client series onward), a VFP connection will interpret the datetime2 column type correctly as a VFP datetime. Of course, that will bring up problems elsewhere. It's up to you to decide what problems affect you most.

 

[stanlyn]

Hi Atlopes,

Thanks for your help, as the evaluate code works when part of a select statement, however it generates error 2032 ambiguous date/datetime when using the below vfp code. How would I get this to work as well? Also note I am using Sql Native drivers which as you said should work, and I'm seeing datetime2(0) and datetime() are not playing well together, as I tried many variations.

Set Filter To Evaluate("{^" + SqlUserSubscription.ExpireTime + "}") > Datetime() ;
	.And. SqlUserSubscription.Userid = gcUserId

Count For Evaluate("{^" + SqlUserSubscription.ExpireTime + "}") > Datetime() ;
	.And. SqlUserSubscription.Userid = gcUserId To lnCount

 

[stanlyn]

Hi Chriss,

You're not defining yout remote view detailed, just as SELECT * query. That means VFP and/or ODBC are inferring (guessing) the conversions from SQL Server types to VFP types. I don't know why this results in a character type, but that's your main problem.

Yes, I figured it was somewhere in the translations. In a way I can see why it gets confused, but in another way I don't because it knows what the field types are.

You see SQL Server/ODBC formats it different from TTOC, the string formats therefore are not comparable. And even if format is the same, the date parts would need to be in the string ordered by significance, ie year,month,day,hour,minute,second. This also depends on MSSQL settings, so your best way out is getting the MSSQL datetime2 type as a VFP datetime for comparison with DATETIME().

I noticed the different formats the two had, which is why I started playing with the 'set date to USA' and others. I also saw that there were none that matches what sql was returning in the 'yyyy-mm-dd hh:mm:ss format. As you can see I need to do date math on it in both a select statement as well as in a vfp command such as 'count for'.

If you create the view with the view designer you can select all fields instead of using * and you can then see what data types are, and you can change them. So you can enforce ExpireTime to be put into a T field. So use the view designer for that, and if you then want to still use code to create views on the fly, the view designer also offers a SQL mode and shows you the CREATE SQL VIEW plus DBSETPROP necessary to specify types instead of inferring them.

Hacking the view, is a good idea. I was trying to build small blocks of code to do this sql stuff, as it seems that nothing is simple (requiring paragraphs) compared working on vfp native tables (requiring a line). So, by explicitly naming the field, it causes an explicit conversion such as making it a T field where the date and time are separated with a 'T' char?

Also while reviewing the view's code, I looked for and never saw anything about the connection string. I could not find it. When running the view, it connects and browses the data. Where is the connection string stored in the view?

Thanks, Stanley

 

[atlopes]

Stanley,

To find out which data are triggering the error, you may run something like this:

SELECT SqlUserSubscription
SCAN
	TRY
		= EVALUATE("{^" + SqlUserSubscription.ExpireTime + "}")
	CATCH
		? RECNO(), "{^" + SqlUserSubscription.ExpireTime + "}"
	ENDTRY
ENDSCAN

Although I can't be sure if using views would change this (I would suppose it wouldn't), Native Clients and later ODBC drivers return datetime2 and datetime MSSQL columns as VFP datetime values, while the SQL Server driver returns a C(19) ISO 8601 representation of time for a datetime2 column, but VFP datetime for an MSSQL datetime column. If you're getting characters from a datetime2, double-check if you're not connecting with an SQL Server driver.

 

[atlopes]

Stanley,

I overlooked this:

Also while reviewing the view's code, I looked for and never saw anything about the connection string. I could not find it. When running the view, it connects and browses the data. Where is the connection string stored in the view?

The connection is stored and defined in the database and it's named DeedroomConn.

 

[Chris Miller]

In the view definition code you have to look into the DBSEZPROP lines for the single fields. To get them you need to select fields, not *, you can select all fields in the view designer, which creates a view with explicit field names instead of * and then has a DBSETPROP() for each fields type.

Chriss

 

[stanlyn]

Hi,

The connection is stored and defined in the database and it's named DeedroomConn.

Yes, I know as I created it. What I was trying to understand is, how does browsing a remote view know to open a specific connection if it is not stored in the view itself? I looked for it and see none...

I looked again after reading Chriss's post and opening the SQL for the view I do not see DeedroomConn anywhere, so how does that view know what connection to open. The field properties are all that I see.

Thanks,
Stanley

 

[Chris Miller]

You don't have to worry about that, it knows its connection by the CREATE VIEW CONNECTION clause with the VFP DBC name of the connection or a DSN name. And the connection is a separate DBC object. By default connections are used shared, so no need to worry you use up many CALs.

Only change the type of the ExpireTime and save the view again.

You only define the view once, then just have the usual one-liner: USE viewname. As if it was a table. You can also put a view into a data environment of a form and then set it to nodataonload, in Form init set parameter values and then Requery('viewname') to load the data.

Chriss

 

[atlopes]

Stanley,

opening the SQL for the view I do not see DeedroomConn anywhere, so how does that view know what connection to open. The field properties are all that I see.

You can fetch the name of the connection that a View uses by querying a database property:

? DBGETPROP("myUser", "VIEW", "ConnectName")