I have a table with dtLargeInt field behind and if I use update sql it throws 
error. I added ftLargeInt in this procedure and now it works.

Function TSqlAnalizer.ReplaceParams(const SQL: string): String;
var
  List: TParams;
  I, DblQuote, Quote: Integer;
  ParamValue: string;
  Param: TParam;
begin
  List := TParams.Create(Nil);
  try
    Result:= List.ParseSQL(SQL, True); // Result:= StrPas(PChar(List.ParseSQL(SQL, True))); {patched by ccy}
    for I:= 0 to List.Count - 1 do
    begin
      Param:= xQuery.ParamByName(List[I].Name);
      if Param <> Nil then
      begin
        case Param.DataType of
          ftBlob:
            ParamValue := #34 + StringToHex(Param.AsString) + #34;
          ftString, ftWideString: { patched by ccy }
            begin
              ParamValue:= Param.Asstring;
              DblQuote:= AnsiPos(#34, ParamValue);
              Quote:= AnsiPos(#39, ParamValue);
              if (Quote > 0) and (DblQuote = 0) then
                ParamValue:= #34 + ParamValue + #34
              else if (DblQuote >= 0) and (Quote = 0) then
                ParamValue:= #39 + ParamValue + #39
              else
                ParamValue:= #39 + ParamValue + #39;
            end;
          ftFloat, ftCurrency, ftBCD, ftAutoInc, ftSmallInt, ftInteger, ftWord, ftFmtBcd, ftLargeInt : { patched by ccy }
            ParamValue:= Param.Asstring;
          ftDate, ftTime, ftDateTime:
            ParamValue:= FloatToStr(Param.AsFloat);
          ftBoolean:
            ParamValue:= xqbase.NBoolean[Param.AsBoolean];
        end;
        Result:= StringReplace(Result, '?', ParamValue, [rfIgnoreCase]);
      end;
    end;
  finally
    List.free;
  end;
end;

What steps will reproduce the problem?
1. Query SQL: SELECT SUM(animals.size) FROM animals
2. Error Syntax: "(Result number of sum): syntax error at line: 1, Column:8 
token: ,."
3. SUM,AVG,MAX not work

What is the expected output? What do you see instead?
Any Thing

What version of the product are you using? On what operating system?
Windows Delphi XE4

Please provide any additional information below.
SUM,AVG,MAX not work - MIN work in my cas with negative result(value -33060 is 
correct result)

Here is an update to latest TXQuery sources.

Some code improvements, bugfixes and better handling of Large Integer (Int64) 
values.

1. pls, convert all *.dfm files in project:
$(DELPHI)\bin\convert.exe -t -i demo\*.dfm
$(DELPHI)\bin\convert.exe -t -i QBuilder\*.dfm
$(DELPHI)\bin\convert.exe -t -i source\*.dfm

for svn visual diff 

2. move all *.dpk from source directory to Packages
- add designtime dcl*.dpk package and redesign runtime xqd*.dpk 

3. fix include or add *.res in distribution package:
  QExprYacc.pas -> {$R Qexpryacc.res}
  xqYacc.pas -> {$R xqyacc.res}

Please tell me what version of txquery can I use for Delphi 7?

What steps will reproduce the problem?
1.  Same Txt Data, one dataset read 3 row, the other dataset read 5 rows
2. use SQL like: 'SELECT * FROM New_dangjuDataSet WHERE StartGanhaoEndGanHao 
not in (SELECT StartGanhaoEndGanHao FROM Old_dangjuDataSet)' ;
3. the more 2 rows can not find

What version of the product are you using? On what operating system?
delphi 2007 + 2.1;

Please provide any additional information below.

What steps will reproduce the problem?
1. Install latest version (prior version did not exist for me)
2. Open .dpk, compile
3. 2 errors  File not found xqyacc.res  and Qexpryacc.res

I am using Delphi 2006  (BDS 2006)

I will try downloading older source files to see if the .res files are there.

Now TxQuery work with Delphi 2010

using latest version in svn http://txquery.googlecode.com/svn/trunk/ 
txquery-read-only

Did not recognize SELECT token, as far as I remember that was the first thing 
you do in SQL. What is happening?

  with slSQL do
    begin
       add('SELECT *');
       add('FROM PB');
       add('LEFT OUTER JOIN PL');
       add('ON( PB.TERMID  = PL.CD_WS )');
       add('AND( PB.SEQNO  = PL.AI_SRC_FILE )');
    end;
      xqry       := TXQuery.Create(nil);
      xdi        := xqry.DataSets.Add;
      xdi.Alias  := 'PB';
      xdi.DataSet := memPOStBOS;
      xdi        := xqry.DataSets.Add;
      xdi.Alias  := 'PL';
      xdi.DataSet := memPOSLOG;
      xqry.SQL.Assign(slSQL);
      xqry.Open;
      { at this point exception is created

ExqueryError: syntax error at line : 1, Column 0, token SELECT

}

      xqry.Close;
      FreeAndNil(xqry);

Hi,

I have been trying to track down this problem for weeks, and believe I finally 
have a way of replicating this issue for demonstration.

I have been experiencing on the vary rare occasion results from TxQuery that 
have not grouped / combined matching records together, where the result gives a 
separate record for every record in the source dataset.

It appears as though the problem has to do with null values in one of the 
fields. This problem does not happen with all result sets, and strangely enough 
- if I add an additional record to the existing result set, the problem 
dissapears (hence it being very difficult to replicate).

However, I have now been able to create a demo application that will show both 
the problem, as well as what the expected result should be (attached).

Steps to replicate:

1) Extract the attached demo project, along with the table (xquery.dbf) to a 
destination

2) Open the project, and change the path of the TicketQAQ.DatabaseName to the 
location of the extracted files. 

3) Run the project. If you click on the RUN USING BDE button, you will see a 
result set with many records - where it does not group as expected.

4) Click on the Remove Contract from XQUERY button, and then the RUN USING BDE 
again - and the resultset will be only 3 records - as I expect it to be.

This problem occurs not only with the BDE, but also with another database 
(DBISAM). It took me some time to be able to replicate the issue using the BDE 
to submit an example project.

This problem is currently plaguing my software, so I'm excited to finally 
hopefully finding a solution. Any help will be greatly appreciated!

I am using Delphi 2007 just incase this is isolated to pre-XE software.

Best Regards

Adam.

I edit TxQuery2 for Support Delphi XE Now.

Happy New Year 2011...

anucha(Nhoom)

xQuery give a wrong value for TLargeIntField in delphi 7.

Please see attached file.
Thanks.

What steps will reproduce the problem?
This will only have probnlems when working with Unicode versions of delphi 
(D2009 and newer)

1. Open the Demo app of TxQuery or create a new project add a txquery, 
TSyntaxHighlighter and a TRichEdit and Bind them
2. if using the demo app, select any of the staments (JOIN, ect) or Add A large 
SQL statement like:
'SELECT * FROM Customer c, Orders o, Items i, 
Parts p WHERE (c.CustNo = o.CustNo) And 
(o.OrderNo = i.OrderNo) And (i.PartNo = p.PartNo) 
And c.CustNo > 1300 AND c.CustNo < 2000;  '

3. The statement will look truncated

What is the expected output? What do you see instead?

The statement should look the same as the input sql, only colored, but instead 
it looks truncated.

What version of the product are you using? On what operating system?
latest TXQuery 2.08 SVN, windows 7, Delphi XE Architect

The solutions is in two steps:

1) In Internal procedure strToRichEdit of TSyntaxHighlighter.Execute method
-------------------------------
Procedure strToRichEdit( Const S: String );
  Var
    //aMem: TMemoryStream;  {commented by fduenas}
    aMem: TStringStream;  {changed by fduenas}
    SelStart: Integer;
  Begin
    //aMem := TMemoryStream.Create; {commented by fduenas}
    aMem := TStringStream.Create( s ); {patched by fduenas} //Using a TStringStream avoids casting the var 'S' to AnsiString 
    FChanging := True;
    SelStart := 0; //Basri
    Try
      //aMem.Write( Pointer( S )^, Length( S ) ); 
      //aMem.Write( Pointer( AnsiString(S) )^, Length( AnsiString(S) )  ); {commented by fduenas}
      aMem.Position := 0;
      If FEditor.Focused Then
        SelStart := FEditor.SelStart;
      //LockWindowUpdate( FEditor.Handle );
      fEditor.OnChange := nil;
       FEditor.OnSelectionChange := nil;
      FEditor.Lines.BeginUpdate;
      FEditor.Lines.LoadFromStream( aMem );
      FEditor.Lines.EndUpdate;
      feditor.OnChange := MyOnChange;
      FEditor.OnSelectionChange := MyOnSelectionChange;
      If FEditor.Focused Then
        FEditor.SelStart := SelStart;
      //LockWindowUpdate( 0 );
    Finally
      aMem.Free;
      FChanging := False;
    End;
  End;
------------------------------------------------------------------
2) at Execute's body change the line:
inputStream.WriteBuffer( Pointer( S )^, Length( S ) ); 

To
  inputStream.WriteBuffer( Pointer( S )^, Length( S )*SizeOf(Char) );

This will look like:
------------------------------------------------------------------
Begin
  Reslt := '';
  RtfHeader := '';
{$IFDEF XQDEMO}
  If Not IsDelphiRunning Then
  Begin
    ShowAbout;
    Raise Exception.Create( SDelphiIsNotRunning );
  End;
{$ENDIF}

  If Not Assigned( FEditor ) Or ( csDestroying In ComponentState ) Then
    Exit;
  s := FEditor.Text + ' ';
  inputStream := TMemoryStream.Create;
  //inputStream.WriteBuffer( Pointer( S )^, Length( S ) ); {commented by fduenas}
  inputStream.WriteBuffer( Pointer( S )^, Length( S )*SizeOf(Char) ); {patched by fduenas}
  inputStream.Seek( 0, 0 );
  outputStream := TMemoryStream.create;
---------------------------------------------------------------------

I'm attaching patched SyntaxHi.pas for a full replacement

regards

Xq_D2010.bpl was not found.

What steps will reproduce the problem?
1.use format function with stringfield some like
SELEC format(" %s ",[strfld]) from table 
and the result is wrong values.

Queries with where clause like "Field1 = Field2" (fields from same dataser) do 
not work.

Bug fix in xQuery.pas :

Procedure TSqlAnalizer.DoJoinInWhere;

  Procedure RecursiveJoin(Start: Integer);
  begin
  ... 
  End;

Begin
  { recursively joining }
  if FTableList.Count > 0 then // change to 0 (was 1) from eLion
    RecursiveJoin(-1); // was 0. Have to start from -1 for queries like select ... where Field1 = Field2
End;


eLion

Hi, can anybody tellme how can i use the name Float as a field name in a query, 
because i had table with a field called FLOAT but when a try to use this field 
in a query an excexption is raised.. somebody can help me?....

Hi here is a update, it fixes 2007 compilation, I could make more tests because 
I don't have any other copilers. Please chek them for any issue.

Regards

Subprocedure CheckBuffer in procedure ReadLn (unit QLexLib) doesn't take into 
account SizeOf(Char) when checking buffer size and allocating buffer. Existing 
procedure should be changed to

  procedure CheckBuffer;
  begin
    repeat
      //we need to take into account size of char - we are increasing
      //position in stream by SizeOf(char) and not by a byte
      if (i * SizeOf(Char)) >= (BufSize - SizeOf(Char)) then
      //(- SizeOf(Char) is needed if BufSize is odd number and
      //GetMem works in chunks of 1 byte
      begin
        BufSize := max (BufSize * 2, 256);
        ReallocMem (Buf, BufSize);
      end;
    until (i * SizeOf(Char)) < (BufSize - SizeOf(Char));
  end;

I have the following SQL that is getting hung up on a field that contains a 
dash in its name.

SELECT TableMain.Holw-serm-ID FROM TableImport INNER JOIN TableMain ON 
TableImport.[Holw-serm-ID]=TableMain.[Holw-serm-ID] WHERE 
TableMain.do_not_update IS NULL

I have tried putting the field quoted in [, ", and ` but makes no difference.

I get an ExQueryError with 'Field serm was not found'.

It seems the field name Holw-serm-ID is getting broken into TableMain.Holw, 
serm and ID.

What version of the product are you using? On what operating system?
3.0.1

What steps will reproduce the problem?
1. TxQuery 2.4.7
2. Delphi 6
3. compile Xqd6.dpk

What is the expected output? What do you see instead?

Expected:
Successful compile.

Instead:
[Error] xquery.pas(1146): Undeclared identifier: 'ftWideMemo'


What version of the product are you using? On what operating system?
Delphi 6 Pro update 4
TxQuery v2.4.7
Win7 x64 Ultimate / WinXP x86

Please provide any additional information below.

Clearly this version was modified (as a stated goal) for unicode support. Has 
support for older compilers been abandoned? The ftWideMemo was introduced in 
Delphi 2006 so everything prior to that should be broken even though there ARE 
packages for them. I hope that this is just an oversite and not a choice.

Thanks for keeping this project alive.

TxQuery sort string fields in ascii not in ansi sequence.
To crrect that SortCompare_S function in xqbase (line 2165) must be 
replaced by:
  Function SortCompare_S( Recno: Integer; Const Value: String ): Integer;
  Begin
    SetRecno( Recno );
    Result := AnsiCompareStr(FFields[Idx].AsString, Value);
    if IsDesc then Result := - Result;
  End;

Best regards
Branko

Please Support ios/android

Hi Guys, I have Uploaded a new modified  version. this make a lot changes to 
the sources. mainly for the puprose to add full unicode support of TXQuery, at 
least for Unicode IDEs (2009 an up).

I have a added full support for Unicode in TXQuery, now you can also add 
unicode chars to the SQL script, an the parse will manage it gracefully.

so you can add something like:

SELECT field1, field2, field3, ('รหัสสินค้า'+'  => 
'+field3) as UnicodeField3
FROM
 table1
WHERE field3 = 'ěščřžýáíé';

--------------------------------------------------------------------
--NOTES Regarging NON Unicoe IDEs:
I have also made a lot of changes to add uniode support to NON Unicode IDES 
(delphi 2007 and older) via the use WideStrings. I cannot validate this changes 
because for now I don't have an IDE installed for those compilers.

Please feel free to test them, they should work.

In the x_flag.inc there is a compiler definition 

{.$DEFINE XQ_USE_WIDESTRINGS}

This is for the NON unicode compilers, Please enable it by removing the '.', 
This will force the compiler to use WideStrings instead of AnsiStrings. 

When this define is disabled, the widestrings are still managed but only via 
the data contained in the fields, the SQL script will be ANSI so, you cannot 
put Unicode Chars in the SQL script, But any Unicode data contained in your 
Datasets should be managed correctly.

I have fully tested this in Delphi XE, and should work correctly in Unicode 
compilers (D2009 and up).

Some TXQuery Events would change if you force the use of WideStrings, so I 
recommend to do the corrections, By default Those AnsiString Fields will be 
changed to WideString and the TStringList will be changed to TWideStringList.

The changes were made to these events:
OnUDFCheckEvent
OnUDFSolveEvent
OnIndexNeededForEvent
OnSetRangeEvent
OnSetFilterEvent
OnCreateIndexEvent
OnDropTableEvent
OnDropIndexEvent
OnSyntaxErrorEvent
OnResolveDatasetEvent
OnQueryFieldNameEvent
OnSetUserRangeEvent

If you use in your code one or more of these events, You will have to copy the 
code you PUT there, Remove the event and recreate it, then Paste again the code 
you used in that event. This change have to be done only when you FORCE the use 
of WideStrings with Delphi 2007 and older compilers.

For further changes you can ADD a compiler condition in the method definition 
of the event, so it wont be affected when you migrate to a newer IDE (Unicode) 
or remove the forced use of WideStrings, 

Example:

procedure XQuery1ResolveDataset(Sender: TObject;
      const Filename: {$IFDEF XQ_USE_WIDESTRINGS}WideString{$ELSE}String{$ENDIF};
      var ATableName: {$IFDEF XQ_USE_WIDESTRINGS}WideString{$ELSE}String{$ENDIF};
      var Dataset: TDataSet);

procedure XQuery1CreateIndex(Sender: TObject; Unique, Descending: Boolean; 
const TableName,
      IndexName: {$IFDEF XQ_USE_WIDESTRINGS}WideString{$ELSE}String{$ENDIF};
      ColumnExprList: {$IFDEF XQ_USE_WIDESTRINGS}TWideStringList{$ELSE}TStringList{$ENDIF});

procedure XQuery1IndexNeededFor(Sender: TObject; DataSet: TDataSet;
      const FieldNames: {$IFDEF XQ_USE_WIDESTRINGS}WideString{$ELSE}String{$ENDIF}; ActivateIndex, IsJoining: Boolean;
      var Accept: Boolean);

Don't forget to Include the 'XQ_Flag.inc' file at the begining of your .pas 
form unit and in any unit where you use the XQ_USE_WIDESTRINGS definition, so 
the compiler definition can be evaluated correctly

This will make you code adaptable to any Delphi version.
REMEMBER This is only needed when working with delphi 2007 and older compilers
-------------------------------------------------------------------

Regards and have fun

When a query has a where statement such as

where (field is not null)

It now raises an error "Expresion is not of Boolean type .!"

This used to work in earlier versions, but now fails.

Tested on D2007 Windows 7 Pro x64.

I am upgrading an application from C Builder 2007 to C Builder XE.

I am using the latest version of TxQuery 2.x that supports XE.

I have SQL statements with multiple JOINS that have worked with TxQuery 1.86 
for YEARS and now cause EAccessError / crashes in the latest version.

The data sets attached to the TxQuery are TkbmMemTables. An example SQL 
statement that is failing is below:

SELECT EventSchedule.*,
       EventLocations.Location,
       EventResources.Name AS Resource,
       EventRevenueCodes.Description AS RevenueCode,
       EventScope.Description AS Scope,
       EventStatus.Status
FROM   EventSchedule
INNER  JOIN EventLocations
   ON  EventLocations.RecordID    = EventSchedule.LocationID
INNER  JOIN EventResources
   ON  EventResources.RecordID    = EventSchedule.ResourceID
INNER  JOIN EventStatus                                                                 
   ON  EventStatus.RecordID       = EventSchedule.StatusID
INNER  JOIN EventScope
   ON  EventScope.RecordID        = EventSchedule.ScopeID
LEFT   OUTER JOIN EventRevenueCodes
   ON  EventRevenueCodes.RecordID = EventSchedule.RevenueCodeID
ORDER BY LateDeparture,DepartTime,Resource

Has anyone else had this problem and a solution?

Best Regards,

Shane

When a Txquery has a null value (or potentially an empty string too) in one of 
the transform or group by fields, it breaks with the error Recno Out of Range. 

Experienced Delphi 2007 using TxQuery 2.0 and 2.7.7

What steps will reproduce the problem?
1. Start the application as it is now
2. Look at the richedit contents

You'll get error messages that the line cannot be inserted into the 
richedit too.

What is the expected output? What do you see instead?
I expected syntax highlighted text. I see raw richedit code with blocks 
instead.

What version of the product are you using? On what operating system?
I'm using version 2.0 (r7).

Please provide any additional information below.

As a quick fix, modify function strToRichEdit() in SyntaxHi.pas on line 266 
from:
      aMem.Write( Pointer(S)^, Length(S) );
to:
      aMem.Write( Pointer( AnsiString(S) )^, Length( AnsiString(S) ) );

when i query data ie.
select a.col1, a.col2 as "รหัสสินค้า"
from table a
when active txquery with d2010 error a.[รหัสสินค้า] 
then run d7 not error 

I have posted updated sources here

Some news:

Added ttlargeInt data type support
Added ttWidestring data type support
Made TxQuery (TP yacc lex parser) Thread safe. (to be tested)

Please make your tests and let me know any suggestion.
I have include the latest fixes uploaded in the SVN (r28)

TxQuery ver 3.01.

TxQuery.Refresh does not work - AV after Refresh.

TIA and best regards
Branko

i used 
update PICBYQDL p,DBBYQDL db Set p.YgDL=db.ygdl, p.WgDL=db.wgdl,p.glys=db.glys  
where p.Name=db.NAME;


update PICBYQDL p set p.YgDL=(select db.ygdl from DBBYQDL db where 
p.Name=db.NAME)


both error,   i search chm help file but no answer,how can i do?

Hi this is at least the updated sources since I started working on TXQuery.

- fixed display value errors when using Number fields in functions that uses 
normally String Fields. Ex: LeftStr, Copy, Length, etc.

-- Added some Extra String Functions: LeftStr, RightStr, MidStr, Copy, Substr.

- Fixed issue with Format() function. Some values where displayed wrongly, not 
displayed or truncated

- Fixed Issues with CAST() function, at least for String and Float values 
casted to string.

- Small fixes

I'm trying to install TxQuery on Delphi XE2, but I'm getting error while 
installing package dclXq_D2011.bpl:

"Registration procedure, Xqreg.Register in package 
C:\De16_ut\xQuery\Packages\D2011\bin\dclXq_D2011.bpl raised Exception class 
EAccessViolation: Access violation at address 5003C397 in module 'rtl160.bpl'. 
Read of address FFFFFFC8."

Changes that I did prior to installation:

in file xq_glag.inc I added section for XE2 version of Delphi 
...
{$IFDEF VER230}      // Delphi 16
{$DEFINE LEVEL4}
{$DEFINE LEVEL5}
{$DEFINE LEVEL6}
{$DEFINE LEVEL7}
{$DEFINE DELPHI7}
{$DEFINE DELPHI}
{$ENDIF}
...

This is not an issue is only a question how can i parse the expression without 
execute the query, i just only need to know if the query can be executed?

I'm glad to post updated sources for TxQuery as of 2012-09-13
this version include the following:

- Fully thread safe operations.
- Reworked Parser and lexer (Yacc/Lex) to work thread safe and to use an 
individual copy of TFormatsettings record, (avaliable from delphi 7 and up) to 
use thread safe formatting functions like StrToInt, FormatFloat, StrTodate, 
DatetoStr, etc.

- Addded 'ActiveStoredUsage' property to determine if the Stored value of the 
property 'Active' will be used when loading the component at design time and at 
runtime. this prevents the Txquery to automatically open when loadign a project 
at design time or to auto open when running the app and that previously that 
txquery has the 'Active' porperty set to True.

- Added 'FormatSettings' property to configure the Locale setttings, those will 
be used with the parser and the SQl functions.

- The DateFormat and DateSeparator (recently added) properties will be soon 
removed, because now the Formatsettings.Parser.ShortDateFormat and 
Formatsettings.Parser.DateSeparator properties will do the work, so please 
migrate your values to that properties.

My default the Locale options that the prasers uses to detect date values and 
float values that are specified directly inside an sql script are:

Formatsettings.Parser.ShortDateFormat Formatsettings.Parser.DateSeparator
Formatsettings.Parser.ThousandSeparator
Formatsettings.Parser.DecimalSeparator

The default values for those properties are specified in the file 
QFormatSettings.pas at the 'ResourceStrings' section, you can change them 
whenever you want. Just make sure that the 'DateSeparator' Char have to be the 
same the you use in the 'ShortDateFormat' string to separate the Moths,days and 
years, ex: for a ShortStringFormat='yyyy-mm-dd', the 'DateSeparator' must be 
'-'.

- Fixed some memory leaks.
- Improvements in the source code execution flow
- Improved the function 'Round', it now acccepts a second para to determine the 
number of decimals to round. Ex: Round(12345.6789,2) will result in 12345.68

- Fixed some TestCases
- Fixed some issues with the Demo app.

I have only tested them in delphi XE and should wpork with delphi 7 and newer

Please make your tests and have fun

Regards
Francisco Armando Dueñas Rodriguez

I got a error message :

'1989/05/01' is not valid date.

but on version 2.7 it is OK. 

winxp
delphi 2006

Hi I have been working a little bit on TxQuery from the latest sources on this 
repository and applying some changes and fixes already posted here in this 
issue section.

I have reworked the Managment and transition from pansiChar and PChar (Ansi to 
unicod versions). and fixes some unsolved issues like the Issue #9 (TlargeInt 
field work field value) this is related to PChar management and memory 
operations. I have made some test and at leats in my place all is working 
perfectly, 

Some issues solved:
1) Previously the length of each source string field has the double of length + 
2 as a txquery field, now it uses the same length as the original Source Field
2) fixed Issue #9 when combining string fields, integer fields and float 
fields, it display strange values.
3) Change some ANSIXX function to unicode Functions like AnsiCompareXX to 
CompareXX, AnsiPos to Pos.
4) All clases in XQuery.pas including TFileReusltset works now with Unicode and 
Pchar

Some patches are based on CY's and applied to ther classes, all the changes are 
marked by my nickname 'fduenas'

There still some work to do like converting all code in XqBase,pas from 
managing PansiChar to PChar (unicode versions).

Hope this fixes can be applied to the main trunk, I will email CY with the full 
changed files so he can review them.

The sources are based on Latest Release 2.1 r16 and applied some fixes posted 
here after that update.

Any other issue please contact me at [email protected].
Regards and happy programming

Does anyone have a RadStudio XE set of packages? I am trying to use the Rad 
Studio 2010 packages and I am getting compiler errors.

Happy New Year!

Shane

Calculated fields on TxQuery component do not seem to work. Setting the value 
of the calculated field (datatype string) in the CalcFields event does not 
update the value of the calc field.

We have added the fixes made by cychau to fix the delphi XE 3 issues

Please be free to test it.

there are no new stuff, just a mantainance release.

- fixed some compiler warnings and Package issues (2010 and below)
- fixed compilation issue in delphi 2010 and delphi 2007, for users that have 
reported them.

- Applied Delpi Xe 3 patch by cychau, i have not made tests because I don't 
have Delphi XE 3 :(, but I trust in him :)

We plan to add a feature for JOINS optimization, to be able to use indexes.But 
this is a long term feature because of schedule problems (I have a life!! :) )

REGARDS

these are master-detail codes:
those relations fields only B2_DESC = :B2_DESC dose work.
otheres B2_SPEC = :B2_SPEC and  B3_GRD = :B3_GRD and B3_SZE = :B3_SZE do not 
work.
xQuery := TxQuery.Create(nil);
 with xQuery do
  begin
   DataSource := MasterSrc;
   AddDataSet(C3, 'C3');
   cSQL   := ' select * from C3 ';
   cWhere := ' where  B2_DESC = :B2_DESC and B2_SPEC = :B2_SPEC and         B3_GRD = :B3_GRD and B3_SZE = :B3_SZE ';
   cOrder := ' Order by B2_DESC, B2_SPEC, B3_GRD, B3_SZE ';
   cSQL   := cSQL + cWhere + cOrder ;
   SQL.SetText(PChar(cSQL));
   Open;
end;

delphi 2006 winxp.

Does anyone use indexes with xquery?
On the help says it should work with joins, but the indexneeded event is not 
fired.

I have checked the code and it looks like the JOIN part is missing or deleted.

the code says: 
    { Check WHERE clause sections that can be optimized by using indexes
      JOINing has precedence over WHERE statement
      WHERE statement has precedence over an ORDER BY }

however, there is only 2 IFs above that, for the WHERE and the ORDER BY

If anyone have any experience with, i appreciate.
I using the original 1.86, but have also checked the updates from the group, 
they are the same.
I am using with titan for btrieve, with big files, so a index is very needed.

Thanks guys, and congratulations for the excellent work on xquery

Lucio

Hi I have updated sources of txquery. 
- Fixed some JOIN bugs (tested with two tables with more than 50,000  records.
- Ported almost all units to manage PChar instead of PAnsiChar
- fixed some memory leaks
- fixed a possible AV error when working with JOINS and joining with string 
fields.
- tested only in Delphi XE, but it shoudl work on Delphi 2007 and lowr versions

- I have included QBuilder in the 'all' sources

Please make some tests, the demo app work good in all the query examples

This latest update include:
- some bug fixes
- removed some potential memory leaks regarding type conversion (some 
ExException and EqException were created but never raised, so they where never 
freed)
- Fixed very old issue when the parser gave an error, the dataset associated to 
txquery where never enabled or their states restored.


Regards

I noticed 2 bugs in xQuery.pas
Here are the fixes.

1) in procedure TSqlAnalizer.DoGroupBy, a query without results should have no 
'Group By' results

  if ResultSet.RecordCount=0 Then
  Begin
    ResultSetHasRecords:= False;
//  ResultSet.Insert(); // changed by eLion. Queries without results should 
have no GroupBy results
  End Else
    ResultSetHasRecords:= True;

and further:

    // all but pivots are marked with negative number
    if vSortList.Count > 0 then begin // changed by eLion
      vSortList.Recno := 1;
      vSortList.SourceRecno := -vSortList.SourceRecno;
    end;

2) The patch in function TSqlAnalizer.ReplaceParams is wrong (at least for 
D2007: for D2010, this should be tested.

//  Result := List.ParseSQL(SQL, True); // Result:= 
StrPas(PChar(List.ParseSQL(SQL, True))); {patched by ccy} <- wrong patch
//  above patch would replace parameter condition like ':ElementNr' by 
'1'#0'mentNr' which is wrong: it should be '1'
    Result:= PWideChar(List.ParseSQL(SQL, True)); // changed by eLion

Hope this will help.

eLion

Hi I have uploaded I new version and updated of txquery.
this include some fixes like:

- Complete review for TWideStringField and TLargeIntField support
- Code improvements regarding field testing
- When using a TRANSFORM..PIVOT, if the trasnform has more than one aggregate 
expression:
ex:

TRANSFORM SUM(AmountPaid), COUNT(*), AVG(AMOUNTPAID) SELECT CUSTNO 
FROM ORDERS GROUP BY CUSTNO 
PIVOT FormatDateTime("yyyy", SALEDATE) IN 
(SELECT DISTINCT EXTRACT(YEAR FROM LASTINVOICEDATE) FROM
CUSTOMER ORDER BY 1);    

The query resturned fields 1998, 19981, 19982, 2001, 20111, 20012,etc
For each aggregate field, not it will return: SUM_OF_1998, COUNT_OF_1998, 
AVG_OF_1998, SUM_OF_2000, COUNT_OF_2000, AVG_OF_2000,
etc.

--Fixed issues with Calculated Fields
The only requirement is that TxQuery data fields (fkdata) must be already 
defined before query execution and the Calculated fields (fkcalculated) must be 
put at the end of the TXQuery dataset field list (after all data fields).

- Added Testing for LargeInt fields to DUnit testing Framework
- Added Testing for Calculated  fields to DUnit testing Framework
- added some new custom functions like 'Concat' and 'Concat_ws'.

I have only tested it in delphi XE, but whould run on delphi 7 or newer.

* Run the Demo app and the Testing DUnit framework to try to test issues

Have fun and regards

For Unicode versions of Delphi, ftGUID fields truncate data when the source 
field is a TGUID field. (Effectively a string field).

I've found a problem where for field types of ftGUID results will be 
incomplete. (Values are clipped).

A work around to the problem is to ensure that the source datasets have no 
TGUID fields. (Use TStringFields instead, or use CAST in queries) 

however the fix appears to be to search and replace all instances of

in [ftString, 

to

in [ftString, ftGUID, 

within the xquery.pas file

Which appears to resolve the issue as well. Can this be updated in the next 
release please?

Hi I have made a little improvements in TXQuery.

The change is regarding Issue 20.

When using a query like:

select distinct adr_id as id from amr where
                 (adr_id NOT IN (select adr_id from amr where mer_id = 5))

The default behaviour is like:

select distinct adr_id as id from amr where
                 (adr_id NOT IN ANY (select adr_id from amr where mer_id = 5))

So this causes each record of 'amr' to be included in the resultset.

Using a query with the 'ALL':
select distinct adr_id as id from amr where
                 (adr_id NOT IN ALL (select adr_id from amr where mer_id = 5))

will do the work as expected.

So I changed the behaviour to, by default use the 'ALL' instead of 'ANY' if 
those keywords are not specified when using the 'NOT IN' clause.

so:

select distinct adr_id as id from amr where
                 (adr_id NOT IN (select adr_id from amr where mer_id = 5))

will be treated the same as:

select distinct adr_id as id from amr where
                 (adr_id NOT IN ALL (select adr_id from amr where mer_id = 5))


this change only affects when using the 'NOT IN'

As always updated sources are uploaded here

Please give some feedback

What steps will reproduce the problem?
1. Do a query with two tables with 10000 record or more the second table copy 
is copy of first.
2.select all fields of both tables,in where clause set a something like that 
table1.field  = table2.field  when field is a char field[20]   
3. use TDBF as your dbf manager.

the query just crash, how can i solve the problem???