Max query length?

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Ludek
Posts: 296
Joined: Thu 12 Oct 2006 09:34

Max query length?

Post by Ludek » Thu 06 Mar 2008 16:24

Hi, just a simple question:
I need to build queries like

Code: Select all

  select column from table where key in (1, 2, 3, 4, 6, 7, 8...)
Is there a limitation in the length (character count) of the TMSQuery.SQL property, and if so, then what is the maximum length?

Thanks much, Ludek.

Antaeus
Posts: 2098
Joined: Tue 14 Feb 2006 10:14

Post by Antaeus » Fri 07 Mar 2008 12:48

The TMSQuery.SQL property is implemented through the TStringList class. So there are only two limitations on query size in TMSQuery:
1) 2GB per string in TStringList;
2) system resources.

LHSoft
Posts: 104
Joined: Sat 18 Aug 2012 08:33

Re: Max query length?

Post by LHSoft » Tue 27 Aug 2013 11:49

This seems not to be right for me.
I have the following Query in a TMSTable:
select Kundennummer, Kundenkarte, Kategorie, Titel, Vorname, Name, Zusatz, Strasse, PLZ, Ort, Landkreis, Bundesland, Land, Telefon, Telefon2, Email, Geburtstag, Tag, Monat, Suchbegriff, weiblich, AutoAnrede, BriefAnrede, Rabatt, Festrabatt, Valuta1, Valuta2, Gutschrift, GutschriftBefristet, AblaufGutschriftBefristet, GutschriftGeburtstag, AblaufGutschriftGeburtstag, GutschriftBonus, AblaufGutschriftBonus, Bank, BLZ, Kontoinhaber, Kontonummer, IBAN, BIC, Briefe, Newsletter, Gutscheinbriefe, Bonusbriefe, Geburtstagsbriefe, gesperrt, Stichwort1, Stichwort2, Stichwort3, Stichwort4, FilKZs, Erfassung, letzteBearbeitung, letzterOnlineAbgleich, letzterKassenAbgleich, Kennzeichen, KennzeichenOLS from dbo.MoStmKun
where Landkreis like 'Müh%' and Land='Deutschland'
This seems to be one sign to long and causes an OLEDBError.
If I reduce size by removing one space or the bold h it works.

So, is this a bug?

best regards
Hans

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Max query length?

Post by AlexP » Wed 28 Aug 2013 07:52

Hello,

The maximum size of the SQL property was specified in the previous post. Please provide the error occurring when executing your query.

P.S. The TMSTable is not designed for execution of queries, there is a TMSQuery component for this

LHSoft
Posts: 104
Joined: Sat 18 Aug 2012 08:33

Re: Max query length?

Post by LHSoft » Thu 29 Aug 2013 23:00

Hello AlexP,

Errormessage is:
Image
Translated:
following error occurs:
error within an ole db-event consisting of several steps.
Check each oledb-status value if exist.
Data have not been processed.


I use TMSTable.SQL for notifications, not for execute, but if declaring an TMSQuery instead it is exactlly the same behavior.

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Max query length?

Post by AlexP » Fri 30 Aug 2013 08:57

Hello,

We cannot reproduce the problem. Please send a small application demonstrating the problem, as well as the script for creating and filling in the DB objects.

LHSoft
Posts: 104
Joined: Sat 18 Aug 2012 08:33

Re: Max query length?

Post by LHSoft » Sun 01 Sep 2013 14:07

Hello AlexP,

I have sent TestProject.zip via my account support request, including a database to attach. It is exactly the Database I have described before.

best regards
Hans

AndreyZ

Re: Max query length?

Post by AndreyZ » Tue 03 Sep 2013 09:16

Thank you for the information. We have fixed this problem. This fix will be included in the next SDAC build.

LHSoft
Posts: 104
Joined: Sat 18 Aug 2012 08:33

Re: Max query length?

Post by LHSoft » Tue 03 Sep 2013 09:34

Hello,

so I am waiting for a few fixes now I want to ask you, if you can tell me when the next build will be available.

Best regards
Hans

PS:
In this demoprogram you can see another strange behavior:
in edit.pas I have remed (//) the events afteropen and beforeclose.
If you activate this lines you get another OLEDB Error or Errors that are very strange (index exeeds maximum or field xy not available) when opening the table which has set Mastersource.
When I use a query instead with mastersource and execute, there are only less or no errormessages.

AndreyZ

Re: Max query length?

Post by AndreyZ » Tue 03 Sep 2013 10:14

when the next build will be available
We plan to release SDAC this month.
If you activate this lines you get another OLEDB Error or Errors
I have uncommented these lines but there were no errors after this. Please send me your test project that demonstrates these errors to andreyz*devart*com .

LHSoft
Posts: 104
Joined: Sat 18 Aug 2012 08:33

Re: Max query length?

Post by LHSoft » Tue 03 Sep 2013 13:00

Hello AndreyZ,

I have uncommented the 2 lines too in your original zip.
Then I open edit.pas and close it again. success.
Open edit.pas a second time 2 errors occur:
Query must return exactly one result set - use execute.
list index exceeds maximum (32).
then, after opening 3rd or 4th times:
invalid pointer
TbStmKunMemos: field 'VersandAdressen' not found
doing so with a query setting Mastersource to DSStmKun and SQL to

Code: Select all

select Kundennummer, VersandAdressen, Infos, History from MoStmKun
and write .execute in "AfterOpen", there are no errors

best regards
Hans

AndreyZ

Re: Max query length?

Post by AndreyZ » Tue 03 Sep 2013 15:14

I still cannot reproduce the problem. Please specify the following:
- the exact version of SDAC. You can learn it from the About sheet of TMSConnection Editor;
- the exact version of your IDE;
- the exact version of SQL Server server and client. You can learn it from the Info sheet of TMSConnection Editor.

LHSoft
Posts: 104
Joined: Sat 18 Aug 2012 08:33

Re: Max query length?

Post by LHSoft » Wed 04 Sep 2013 00:06

SDAC 6.7.14
IDE DELPHI XE2 Update4 Hotfix1 16.0.4504.48759
Microsoft SQL Server: 10.50.4000
Microsoft SQL Server Native Client 10.0: 10.50.4000.0

Do you have timer enabled?

Is this the exact codeof procedure CN:

Code: Select all

 TbStmKun.SQL.Clear;
  for i := 0 to TbStmKun.Fields.Count - 1 do
    if i < TbStmKun.Fields.Count - 1 then
      SQLFields := SQLFields + TbStmKun.Fields[i].FieldName + ',' //without ending space
    else
      SQLFields := SQLFields + TbStmKun.Fields[i].FieldName;
  TbStmKun.SQL.Add('select ' + SQLFields + ' from dbo.MoStmKun');
  TbStmKun.SQL.Add('WHERE Landkreis like ''Mühldorf%'' and Land=''Deutschland''');
  TbStmKun.LockMode := lmPessimistic;
  TbStmKun.ChangeNotification := DBM.StmKunCN;
see Flash http://www.lhsoft.de/testproject.html

LHSoft
Posts: 104
Joined: Sat 18 Aug 2012 08:33

Re: Max query length?

Post by LHSoft » Wed 04 Sep 2013 01:36

First result:

if not assigning TMSNotification to maintable TbStmKun in procedure CN and not setting ReflectChangeNotify to true in TbStmKunBeforeOpen, the error does not appear.

Maybe, you do not get the error meanwhile cause you changed somthing with notifications?

AndreyZ

Re: Max query length?

Post by AndreyZ » Wed 04 Sep 2013 06:39

Thank you for the information. I have reproduced and fixed this problem as well. This fix will be included in the next SDAC build.

Post Reply