Devart Forums

Posted: **Thu 06 Mar 2008 16:24**

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

  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.

Posted: **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.

Posted: **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

Posted: **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

Posted: **Thu 29 Aug 2013 23:00**

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.

Posted: **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.

Posted: **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

Posted: **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.

Posted: **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.

Posted: **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 .

Posted: **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

Posted: **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.

Posted: **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

Posted: **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?

Posted: **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.

Devart Forums

Max query length?

Max query length?

Re: Max query length?

Re: Max query length?

Re: Max query length?

Re: Max query length?

Re: Max query length?

Re: Max query length?

Re: Max query length?

Re: Max query length?

Re: Max query length?

Re: Max query length?

Re: Max query length?

Re: Max query length?

Re: Max query length?