Max query length?

[Ludek]

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]

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]

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]

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]

Hello AlexP,

Errormessage is:

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]

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]

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]

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

[LHSoft]

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]

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]

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]

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]

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]

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]

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