Type mismatch error in RB15.03, NexusDB V3.51, and XE5
Hi,
I was attempting to run (print preview) an End User Report with datetime
search criteria using the latest ReportBuilder and NXDB releases in my
XE5 app and I encountered an error:
"NexusDB:: Query execution failed:
Error in statement: Type mismatch ['2012-03-31 00:00:00']
[$3CA0/15520]."
Could the datetime search criteria be missing the "Timestamp" prefix
when generating the SQL to textually represent a datetime?
I have also posted this to the NexusDB newsgroups and am seeing if they
have a solution. While I wait for an _official_ solution I may have
found a workaround/fix for the issue:
daSQL.pas function "FormatParamValue" (lines 6632 to 6796) looks control
how date/time parameters are structured. From line 6696 looks to have
database-specific date/time formatting depending on the DBMS used.
Modifying lines 6713 and 6731 to include "dtParadox, dtOther" appears to
allow the SQL generator to format the search criteria correctly [for use
in an NexusDB database]. Not ideal but it works for the moment.
Opening a report in ReportDesigner, and editing DataSettings, I am
unable to select dtNexusDB as a database type - only dtOther is
available. Also Session Type only has NexusDBSession available. Only
within XE5 under the Properties tab [when ppDesigner1 is selected] does
it list more options.
--
Regards,
Will Owyong
(GMT+10, Australia)
I was attempting to run (print preview) an End User Report with datetime
search criteria using the latest ReportBuilder and NXDB releases in my
XE5 app and I encountered an error:
"NexusDB:: Query execution failed:
Error in statement: Type mismatch ['2012-03-31 00:00:00']
[$3CA0/15520]."
Could the datetime search criteria be missing the "Timestamp" prefix
when generating the SQL to textually represent a datetime?
I have also posted this to the NexusDB newsgroups and am seeing if they
have a solution. While I wait for an _official_ solution I may have
found a workaround/fix for the issue:
daSQL.pas function "FormatParamValue" (lines 6632 to 6796) looks control
how date/time parameters are structured. From line 6696 looks to have
database-specific date/time formatting depending on the DBMS used.
Modifying lines 6713 and 6731 to include "dtParadox, dtOther" appears to
allow the SQL generator to format the search criteria correctly [for use
in an NexusDB database]. Not ideal but it works for the moment.
Opening a report in ReportDesigner, and editing DataSettings, I am
unable to select dtNexusDB as a database type - only dtOther is
available. Also Session Type only has NexusDBSession available. Only
within XE5 under the Properties tab [when ppDesigner1 is selected] does
it list more options.
--
Regards,
Will Owyong
(GMT+10, Australia)
This discussion has been closed.
Comments
Make sure you are using the plug-in installed by ReportBuilder to Public
Documents\RBuilder\XE5\Demos\EndUser Databases\NexusDB. There is a
ReadMe.doc included and a full example for using the end-user components.
For Delphi design-time install the packages. For run-time include daNexusDB
in the 'uses'
When you do the above, the Designer DataSettings SessionType can be set to
NexusDBSession and once you configure that then the DatabaseType to
dtNexusDB. These values are provided by the daNexusDB plug-in.
daSQL contains special code for NexusDB, you can search the unit to find all
occurrences. There is special formatting logic for dtNexusDB database type.
Best regards,
-
Nard Moseley
Digital Metaphors
www.digital-metaphors.com
Best regards,
Nard Moseley
Digital Metaphors
www.digital-metaphors.com
Has the DateTime format changed between Nexus 3 and Nexus 2? Please provide
details of how version Nexus DB version 3 breaks compatibility with version
2.
Best regards,
Nard Moseley
Digital Metaphors
www.digital-metaphors.com
For what its worth the daNexusDB is definitely more up to date than the
one supplied by NexusDB. I use a RB supplied one, slightly modified to
improve performance (blockreadsize set for some queries, NIL passed to
TnxQuery.create()) and SQL type is set to sqSQL2 instead of the sqSQL1.
Other than that its identical and works out of the box.
Hi Nard,
The datetime format is still the same however the NexusDB database was
being handled as dtOther which meant the datetime formatting was being
formatted incorrectly in daSQL.pas.
Comparing the daNexusDB.pas files between the one provided in the latest
NXDBV3 release and latest RBPro15 release there are a few distinct
differences.
RBPro15 daNexusDB.pas (modified 2006-04-26):
- has an additional function ContainsSQLFunctionCall(..)
- has a string array "NexusDBFunctions"
- adds ".ZZZ" to time formatting (GetSearchCriteriaTimeFormat)
- uses a TnxQuery (when NexusDBV2 detected) for collecting views in
GetTableNames
NexusDB V3 daNexusDB.pas (modified 2009-07-03):
- uses nxllMemoryManager and replaces "FreeAndNil" with "nxFreeAndNil"
- defines a constant dtNexusDB = dtOther
- returns time formatting (GetSearchCriteriaTimeFormat) as "HH:NN:SS"
You can probably see my hesitation to use the RB version by the large
gap in the date modified on each daNexusDB.pas file. I am unsure what
the absence of the ContainsSQLFunctionCall(..) function, or the
differences in general, will do to end-user reporting functionality.
Note: both "HH:NN:SS" and "HH:NN:SS.ZZZ" both appear valid when
converting a string timestamp as shown by this example:
select
timestamp '2014-04-01 05:00:00'
,timestamp '2014-04-01 05:00:00.123'
,timestamp '2014-04-01 13:00:00'
,timestamp '2014-04-01 13:00:00.123'
from #dummy;
--
Regards,
Will Owyong
(GMT+10, Australia)
for yonks and I'm pretty sure its more up to date (in functionality
terms) than the NexusDB one. It support "views" for one thing, and Nexus
extensions like CURRENT_TIMESTAMP. I've tweaked it to make it a bit
faster, but essentially it works properly.
.....
Cheers Paul,
I did notice that there was an extra file in the RB folder
(daNexusDBReg.pas). I'm considering doing a merge/sync of the NXDB and
RB daNexusDB.pas files [into the NXDB file]. The missing
"ContainsSQLFunctionCall" function in the NXDB one is a worry, as is the
missing NexusDBFunctions array.
Searching the RB dev guide PDF, it doesn't say what the differences are
between SQL1 and SQL2, but to use SQL2 on more modern databases.
I will give the RB install a go, though the installation instructions
don't seem to apply to the new Win32/Win64 IDE.
Thanks
--
Regards,
Will Owyong
(GMT+10, Australia)
I recommend...
- restoring daSQL.pas back to its original (and correct) state.
- using the Dade NexusDB plugin installed with ReportBuilder, it supports
the dtNexusDB DatabaseType. Using dtOther will produce incorrect SQL
syntax - as you have experienced.
Your issues are caused by using incorrect DADE plugin code. We only have
resources to support the NexusDB plug-in code installed with RB.
Best regards,
-
Nard Moseley
Digital Metaphors
www.digital-metaphors.com
Best regards,
Nard Moseley
Digital Metaphors
www.digital-metaphors.com
Hi Nard,
I made the swap over to the RB DADE yesterday. I will undo the daSQL
changes and see how I go this morning.
Cheers
--
Regards,
Will Owyong
(GMT+10, Australia)
Thanks Paul,
Are your "...NIL passed to TnxQuery.create()) and SQL type is set to
sqSQL2 instead of the sqSQL1..." optimisations only made in
daNexusDB.pas? I can see several references to "TnxQuery.Create(Self)"
scattered throughout. Is it a 'search & replace' job for "sqSQL1" and
"TnxQuery.Create(Self)"?
Perhaps the RB devs will implement your changes (or ask for your
daNexusDB.pas file?) in an upcoming update?
--
Regards,
Will Owyong
(GMT+10, Australia)
setting blockreadsize:
procedure TdanxSession.GetTableNames(const aDatabaseName: string; aList:
TStrings);
var
lDatabase: TnxDatabase;
{$IFDEF NEXUS2}
lQuery: TnxQuery;
{$ENDIF}
begin
{get the database}
lDatabase := TnxDatabase(GetDatabaseForName(aDatabaseName));
{Database must be active to get table names}
lDatabase.Connected := True;
{get list of table names from a table object}
if lDatabase.Connected then
lDatabase.GetTableNames(aList);
{$IFDEF NEXUS2}
{get list of view names from a query object}
if (lDatabase.Connected) and not(lDatabase.ReadOnly) then
begin
{create a temporary NexusDB query}
lQuery := TnxQuery.Create(nil);
{Tomsie change for performance}
lQuery.BlockReadSize := 1024*1024;
try
....... etc
endl
procedure TdanxDataSet.GetFieldNamesForSQL(aList: TStrings; aSQL: TStrings);
var
lQuery : TnxQuery;
begin
aList.Clear;
{create a temporary NexusDB query}
{Tomsie changed Self to nil as its MUCH faster}
lQuery := TnxQuery.Create(nil);
{Tomsie change for performance}
lQuery.BlockReadSize := 1024*1024;
try
....... etc
end;
procedure TdanxDataSet.GetFieldsForSQL(aList: TList; aSQL: TStrings);
var
lQuery : TnxQuery;
lField : TppField;
liIndex : Integer;
begin
aList.Clear;
{create a temporary NexusDB query}
lQuery := TnxQuery.Create(nil);
{Tomsie change for performance}
lQuery.BlockReadSize := 1024*1024;
try
....... etc
end;
I'll add these mods for the next maintenance release. Thanks for the
feedback.
-
Nard Moseley
Digital Metaphors
www.digital-metaphors.com
Best regards,
Nard Moseley
Digital Metaphors
www.digital-metaphors.com
.....
Thanks Paul,
I've now reverted to the RB source and implemented your speed
tweaks...Only problem now is that I'm getting the "type mismatch" error
again on my search criteria (!).
Doing a trace I've discovered that the database type is still coming
back as dtOther instead of dtNexusDB despite setting ppDesigner as
dtNexusDB/SQL2/case insensitive.
Adding a breakpoint to function TdanxSession.GetDatabaseType(...) and
running in debug mode, it is not being called before determining the
[print preview] report search criteria.
The search [for what I've missed] continues...
--
Regards,
Will Owyong
(GMT+10, Australia)
To fix existing reports, open in the Designer and for each Query DataView,
access the Query Designer and then select Ok. This will save the new
DatabaseType as part of the query definition.
Best regards,
-
Nard Moseley
Digital Metaphors
www.digital-metaphors.com
Best regards,
Nard Moseley
Digital Metaphors
www.digital-metaphors.com
Cheers Nard
--
Regards,
Will Owyong
(GMT+10, Australia)
Hi Nard,
I get "Range check error" messages if I try your instructions in the
64bit version of the application.
I was able to do it in the 32bit version of our application however, and
run the report successfully. The report was then able to be accessed in
the 64bit version after that also.
I'm hoping a similar solution will present itself with the internal
(hard coded) RB reports I am getting "range check" errors on.
--
Regards,
Will Owyong
(GMT+10, Australia)
Hi Nard,
Since this issue started we have updated RBPro to 15.04 and NexusDB
V4[.0005]. I've still got this error (see the exception log below).
It seems to be related to how a datetime field is handled if used as a
search item. The 'Print Preview' search window displays datetime fields
with separate date and time components.
I'm not sure what else to do. I've checked data settings and saved the
report as the RB15 version but to no effect. I've also given the End
User Reporting components their own connection/session to prevent
threading issues, to no effect.
I am using the DADE plugin you have supplied (in
Users\Public\PublicDocuments\RBuilder\Delphi XE5\Demos\4. EndUser
databases\NexusDB).
There are no SQL syntax handling differences between NXDBV3 and NXDBV4
as far as I know of.
--- log file below ---
*** Server started ***
Time: [126848172] 15/09/14 2:28:12 PM
ExceptionInfo: nil
Exception: EnxDatabaseError
Message: NexusDB: : Query execution
failed:
Error in statement: Type mismatch ['2006-01-01 00:00:00.000']
[$3CA0/15520]
Filesize: 75331584
Filedate: 2014-09-15 14:27:38
Computer: KMDEV02
User: William
ThreadID: 6768
ProcessID: 4788
Version: 4.0005
AWE Enabled: False
Current Window Caption: Print Preview
Current Window Class: TppPrintPreview
--------------------------------------------------------------
Callstack (Frames):
[007A4824] nxdb.TnxDataset.dsError (Line 9209, "nxdb.pas")
[007B0603] nxdb.TnxStatementDataSet.sdHandleError (Line 15167, "nxdb.pas")
[007B068B] nxdb.TnxStatementDataSet.sdHandleError (Line 15177, "nxdb.pas")
[007AFC37] nxdb.TnxStatementDataSet.dsOpenCursorID (Line 14932, "nxdb.pas")
[007A8272] nxdb.TnxDataset.OpenCursor (Line 10621, "nxdb.pas")
[006FC095] Data.DB.TDataSet.SetActive (Line 12290, "Data.DB.pas")
[007A8DA0] nxdb.TnxDataset.SetActive (Line 10899, "nxdb.pas")
[006FBEDC] Data.DB.TDataSet.Open (Line 12246, "Data.DB.pas")
[00CE3893] ppDB.TppDataPipeline.Open (Line 1945, "ppDB.pas")
[0105293D] daDataView.TdaCustomDataView.SetActive (Line 435,
"daDataView.pas")
[01055F34] daQueryDataView.TdaQueryDataView.SetActive (Line 892,
"daQueryDataView.pas")
[010526DA] daDataView.TdaCustomDataView.ReportBeforePrintEvent (Line
308, "daDataView.pas")
[0105654C] daQueryDataView.TdaQueryDataView.ReportBeforePrintEvent (Line
1099, "daQueryDataView.pas")
[0105251A] daDataView.TdaCustomDataView.EventNotify (Line 239,
"daDataView.pas")
[010554B8] daQueryDataView.TdaQueryDataView.EventNotify (Line 451,
"daQueryDataView.pas")
[00A74164] ppComm.TppCommunicator.SendEventNotify (Line 778, "ppComm.pas")
[00D1DC20] ppClass.TppCustomReport.TriggerCodeModuleBeforePrint (Line
4277, "ppClass.pas")
[00CA0835] ppReport.TppReport.TriggerCodeModuleBeforePrint (Line 2819,
"ppReport.pas")
[004E0BD4] System.Classes.CheckSynchronize (Line 14513,
"System.Classes.pas")
[0065F596] Vcl.Forms.TApplication.Idle (Line 10889, "Vcl.Forms.pas")
[0065E7C7] Vcl.Forms.TApplication.HandleMessage (Line 10318,
"Vcl.Forms.pas")
[00A966DA] ppForms.TppForm.ShowModal (Line 692, "ppForms.pas")
[00C7C377] ppProd.TppProducer.PrintToScreen (Line 1831, "ppProd.pas")
[00C7BF7B] ppProd.TppProducer.Print (Line 1587, "ppProd.pas")
[00C9E099] ppReport.TppReport.Print (Line 1160, "ppReport.pas")
[015295B7] ppRptExp.TppReportExplorer.PrintPreview (Line 2486,
"ppRptExp.pas")
[01520E68] ppExpFrm.TppReportExplorerForm.PrintPreview (Line 661,
"ppExpFrm.pas")
[01520FDF] ppExpFrm.TppReportExplorerForm.ehFilePrintPreviewClick (Line
716, "ppExpFrm.pas")
[0151AEDB] ppRptExpCommon.TppReportsPopupMenu.ehItem_Click (Line 1122,
"ppRptExpCommon.pas")
[00B1FF1C] ppTB2Item.TppTBCustomItem.Click (Line 1490, "ppTB2Item.pas")
[00B1FDA7] ppTB2Item.TppTBCustomItem.ClickWndProc (Line 1440,
"ppTB2Item.pas")
[004E48E8] System.Classes.StdWndProc (Line 17013, "System.Classes.pas")
==============================================================
Time: [126848266] 15/09/14 2:28:12 PM
ExceptionInfo: nil
Exception: EDataError
Message: NexusDB: : Query execution
failed:
Error in statement: Type mismatch ['2006-01-01 00:00:00.000']
[$3CA0/15520]
Filesize: 75331584
Filedate: 2014-09-15 14:27:38
Computer: KMDEV02
User: William
ThreadID: 6768
ProcessID: 4788
Version: 4.0005
AWE Enabled: False
Current Window Caption: Print Preview
Current Window Class: TppPrintPreview
--------------------------------------------------------------
Callstack (Frames):
[00D5E3A5] ppDBPipe.TppDBPipeline.OpenDataSet (Line 1353, "ppDBPipe.pas")
[00CE3893] ppDB.TppDataPipeline.Open (Line 1945, "ppDB.pas")
[0105293D] daDataView.TdaCustomDataView.SetActive (Line 435,
"daDataView.pas")
[01055F34] daQueryDataView.TdaQueryDataView.SetActive (Line 892,
"daQueryDataView.pas")
[010526DA] daDataView.TdaCustomDataView.ReportBeforePrintEvent (Line
308, "daDataView.pas")
[0105654C] daQueryDataView.TdaQueryDataView.ReportBeforePrintEvent (Line
1099, "daQueryDataView.pas")
[0105251A] daDataView.TdaCustomDataView.EventNotify (Line 239,
"daDataView.pas")
[010554B8] daQueryDataView.TdaQueryDataView.EventNotify (Line 451,
"daQueryDataView.pas")
[00A74164] ppComm.TppCommunicator.SendEventNotify (Line 778, "ppComm.pas")
[00D1DC20] ppClass.TppCustomReport.TriggerCodeModuleBeforePrint (Line
4277, "ppClass.pas")
[00CA0835] ppReport.TppReport.TriggerCodeModuleBeforePrint (Line 2819,
"ppReport.pas")
[004E0BD4] System.Classes.CheckSynchronize (Line 14513,
"System.Classes.pas")
[0065F596] Vcl.Forms.TApplication.Idle (Line 10889, "Vcl.Forms.pas")
[0065E7C7] Vcl.Forms.TApplication.HandleMessage (Line 10318,
"Vcl.Forms.pas")
[00A966DA] ppForms.TppForm.ShowModal (Line 692, "ppForms.pas")
[00C7C377] ppProd.TppProducer.PrintToScreen (Line 1831, "ppProd.pas")
[00C7BF7B] ppProd.TppProducer.Print (Line 1587, "ppProd.pas")
[00C9E099] ppReport.TppReport.Print (Line 1160, "ppReport.pas")
[015295B7] ppRptExp.TppReportExplorer.PrintPreview (Line 2486,
"ppRptExp.pas")
[01520E68] ppExpFrm.TppReportExplorerForm.PrintPreview (Line 661,
"ppExpFrm.pas")
[01520FDF] ppExpFrm.TppReportExplorerForm.ehFilePrintPreviewClick (Line
716, "ppExpFrm.pas")
[0151AEDB] ppRptExpCommon.TppReportsPopupMenu.ehItem_Click (Line 1122,
"ppRptExpCommon.pas")
[00B1FF1C] ppTB2Item.TppTBCustomItem.Click (Line 1490, "ppTB2Item.pas")
[00B1FDA7] ppTB2Item.TppTBCustomItem.ClickWndProc (Line 1440,
"ppTB2Item.pas")
[004E48E8] System.Classes.StdWndProc (Line 17013, "System.Classes.pas")
==============================================================
Time: [126848328] 15/09/14 2:28:12 PM
ExceptionInfo: nil
Exception: EDataError
Message: NexusDB: : Query execution
failed:
Error in statement: Type mismatch ['2006-01-01 00:00:00.000']
[$3CA0/15520]
Filesize: 75331584
Filedate: 2014-09-15 14:27:38
Computer: KMDEV02
User: William
ThreadID: 7056
ProcessID: 4788
Version: 4.0005
AWE Enabled: False
--------------------------------------------------------------
Callstack (Frames):
[004E183E] System.Classes.TThread.Synchronize (Line 15165,
"System.Classes.pas")
[004E1948] System.Classes.TThread.Synchronize (Line 15199,
"System.Classes.pas")
[00CA082A] ppReport.TppReport.TriggerCodeModuleBeforePrint (Line 2816,
"ppReport.pas")
[00C96D75] ppEngine.TppEngine.Init (Line 2252, "ppEngine.pas")
[00C93A72] ppEngine.TppEngine.Generate (Line 559, "ppEngine.pas")
[00C93956] ppEngine.TppEngine.RequestPage (Line 515, "ppEngine.pas")
[00C9F637] ppReport.TppReport.RequestPage (Line 2130, "ppReport.pas")
[00A9E08C] ppDevice.TppPublisher.DoOnPageRequest (Line 1326, "ppDevice.pas")
[00A9DC17] ppDevice.TppPublisher.Publish (Line 1133, "ppDevice.pas")
[00A9F13A] ppDevice.TppDevice.MakePageRequest (Line 2138, "ppDevice.pas")
[00AEA2E3] ppThreadedPageCache.TppPageRequestThread.ExecuteReport (Line
646, "ppThreadedPageCache.pas")
[00AEA253] ppThreadedPageCache.TppPageRequestThread.Execute (Line 616,
"ppThreadedPageCache.pas")
[004E0D19] System.Classes.ThreadProc (Line 14561, "System.Classes.pas")
[0040B4D0] System.ThreadWrapper (Line 23406, "System.pas")
==============================================================
Time: [126848390] 15/09/14 2:28:12 PM
ExceptionInfo: nil
Exception: EDataError
Message: NexusDB: : Query execution
failed:
Error in statement: Type mismatch ['2006-01-01 00:00:00.000']
[$3CA0/15520]
Filesize: 75331584
Filedate: 2014-09-15 14:27:38
Computer: KMDEV02
User: William
ThreadID: 6768
ProcessID: 4788
Version: 4.0005
AWE Enabled: False
Current Window Caption: Print Preview
Current Window Class: TppPrintPreview
--------------------------------------------------------------
Callstack (Frames):
[00AE9A94] ppThreadedPageCache.TppThreadedPageCache.ehExceptionTimer
(Line 320, "ppThreadedPageCache.pas")
[005F9F17] Vcl.ExtCtrls.TTimer.Timer (Line 3074, "Vcl.ExtCtrls.pas")
[004E48E8] System.Classes.StdWndProc (Line 17013, "System.Classes.pas")
==============================================================
--
Regards,
Will Owyong
(GMT+10, Australia)
I recommend re-reading this thread thoroughly. I just did and the all the
answers are there.
- for example, see my postApril 4 about fixing your existing reports and
your reply....
- you can trace the code in daSQL.pas, the method TdaSQL.FormatParamValue
has the following code used to format date/datetime/time for NexusDB.if
(DatabaseType in [dtNexusDB, dtElevateDB]) then
begin
case aDataType of
dtDate: Result := 'DATE ' + '''' + FormatDateTime(lsDateFormat,
lDateTime, ppFormatSettings) + '''';
dtDateTime: Result := 'TIMESTAMP ' + '''' +
FormatDateTime(lsDateFormat, lDateTime, ppFormatSettings) + ' ' +
FormatDateTime(lsTimeFormat, lDateTime, ppFormatSettings) + '''';
dtTime: Result := 'TIME ' + '''' + FormatDateTime(lsDateFormat,
lDateTime, ppFormatSettings) + '''';
end;
end;
- If your old reports are not getting into that logic, then you still have
dtOther being used - which was the issue all along. These settings are saved
down with your old report and you need to update them as above.
- From April 4 post...
Best regards,
-
Nard Moseley
Digital Metaphors
www.digital-metaphors.com
Best regards,
Nard Moseley
Digital Metaphors
www.digital-metaphors.com
.....
Hi Nard
I have done some more experimenting with ways to fix the old report and
have managed to get the report working, but in order to do so I had to:
- go to the Data tab
- open each dataset
- remove then re-add all data fields (by toggling the "All fields"
checkbox does this quickly)
- save the report
(check that it worked by print previewing the report)
Simply opening the Query designer and clicking OK was not enough in this
case.
Thanks and regards,
Will Owyong
(GMT+10, Australia)