[CASE OPENED] Date format automatically converted to Alphanumeric in WF 8.2.03
Hi, I have recently migrated to WF 8.2.03 from 8.2.02. The Code which was working in old version is not working on 8.2.03.
When I do SQL Pass through on one of the tables in SQL Server. The Date field is getting converted to alphanumeric and throwing error if I use that date field as one of the Input parameters. I have attached my code and error message below. I couldn't recreate this issue using CAR or other sample file, as this is happening when I do SQL Pass through.
In the old version, the same code was working and when I checked the field definition of the HOLD1 file, the date field has YYMD format. But in the new version, it is showing as A10V.
Code ---- ENGINE SQLMSS SET DEFAULT_CONNECTION TESTSQLSER SQL SQLMSS PREPARE SQLOUT FOR Select Invoicenumber, Invoicedate from Invoices where ClientID = 1000 END
TABLE FILE SQLOUT PRINT * ON TABLE HOLD AS HOLD1 END
TABLE FILE HOLD1 PRINT * WHERE ( Invoicedate GE &InvoiceStart.(|FORMAT=YYMD).InvoiceStart:.QUOTEDSTRING ) AND ( Invoicedate LE &InvoiceEnd.(|FORMAT=YYMD).InvoiceEnd:.QUOTEDSTRING ); END -EXIT
Error Message: -------------
An error has occurred
Detail: 0 NUMBER OF RECORDS IN TABLE= 1923 LINES= 1923 ERROR AT OR NEAR LINE 27 IN PROCEDURE ADHOCRQ FOCEXEC * (FOC015) THE TEST VALUE IS LONGER THAN THE FIELD FORMAT LENGTH: September 02 2018 (FOC009) Request failed validation, not executed.
Thanks, Joshy Webfocus 8.2.03This message has been edited. Last edited by: FP Mod Chuck,
WF 8.2.03
September 06, 2018, 03:26 PM
BabakNYC
In SQL Server Management Studio, can you check the format of Invoicedate in Invoices? The hold file replicates the format of the table.
Is this the exact same database and table or is this pointing to a different instance of SQL Server?
WebFOCUS 8206, Unix, Windows
September 06, 2018, 04:53 PM
Joshy
Hi,
In the SQL Server, the format is DATE. This is exactly same database and table, I am pointing in both the versions.
Thanks.
WF 8.2.03
September 06, 2018, 05:09 PM
BabakNYC
I just tested this on a table that has a DATE field and a DATETIME field. ORD_DT is a DATE field and it's a YYMD in the HOLD file.
ENGINE SQLMSS SET DEFAULT_CONNECTION SQL_BN
SQL SQLMSS PREPARE SQLOUT FOR
SELECT
T1."ORD_DT",
T1."Freight" FROM Orders T1;
END
TABLE FILE SQLOUT
PRINT
*
ON TABLE HOLD AS HOLD1
END
-RUN
?FF HOLD1
This is what I get:
No output was returned
Possible causes:
- Output was directed to a destination such as a file or printer
Detail:
0 NUMBER OF RECORDS IN TABLE= 830 LINES= 830
FILENAME= HOLD1
ORD_DT E01 YYMD
Freight E02 P21.4
WebFOCUS 8206, Unix, Windows
September 06, 2018, 05:45 PM
Joshy
Hi,
In the SQL Server, the Invoice Date is date format.
When I need ?FF HOLD1, I got the below: 0 NUMBER OF RECORDS IN TABLE= 1923 LINES= 1923 FILENAME= HOLD1 Invoicenumber E01 I11 Invoicedate E02 A10V
But in WF 8.2.02, it is showing i as YYMD.
Is there any settings needed in Adapter settings.
Thanks,
WF 8.2.03
September 07, 2018, 08:26 AM
BabakNYC
I know of no specific settings in the adapter that force a SQL Server Date field to show up as an integer.
If you create a synonym for this table, will you see Invoice Date as an I field?
WebFOCUS 8206, Unix, Windows
September 07, 2018, 08:40 AM
Tony A
Using 8.2.03, if I query WF_RETAIL table wf_retail_time then I get the following against the time_date column which is declared as a DATE format -
SQL SQLMSS PREPARE SQLOUT FOR
select time_date
from _wf_retail_time
END
TABLE FILE SQLOUT
BY time_date
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS HOLD1
END
-RUN
?FF HOLD1
----
0 NUMBER OF RECORDS IN TABLE= 6575 LINES= 6575
FILENAME= HOLD1
time_date E01 YYMD
T
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
September 07, 2018, 09:05 AM
BabakNYC
I have a feeling there might be another version of Invoice table in the database. If you look at the TESTSQLSER connection properties what's the default database? Try fully qualifying the select statement's FROM tablename to make sure you're looking at the right version of invoices. Something is different between the two environments and it's not the date format.
WebFOCUS 8206, Unix, Windows
September 07, 2018, 11:27 AM
Joshy
Hi, When I created the adapter, I have specified the default database which has all the data information for the reporting(which includes Invoices table). When I don't specify the default database, when I run the SQL Pass through, it is giving connection failed error.
And also, When I print the Hold file, In WF 8.2.02,it printed the date in yyyy/mm/dd In WF 8.2.03, it printed as yyyy-mm-dd.
I assume thats why Webfocus is understanding it as A10V instead of YYMD.
WF 8.2.03
September 07, 2018, 11:45 AM
BabakNYC
Did you try creating a master file for this table? That'll tell you what WebFOCUS sees when it interrogates the SQL Server table's column format. If you're seeing an A10 field for a Date column, then SQL Server is telling WebFOCUS it's a 10 character field. If you're pointing 8203 and 8202 to the exact same table, I'd expect it to create identical column properties.
You might need to open a case with IB Tech support to figure this one out.
WebFOCUS 8206, Unix, Windows
September 07, 2018, 12:05 PM
Joshy
Yes, I have a master file and it shows the Invoice date as MDYY. This is same in both 8.2.03 and 8.2.02.
It is getting converted only if its through SQL Passthrough.
Thank you for helping me out. I will open a case with IBI.