Datetime paramtere and Union all problem

[aehimself]

To be honest with you, I don't know. The last time I used Zeos as a downloaded zip was about 6 years ago :) Maybe @Jan can give an accurate answer here.

As for 8, it is already considered stable, the official release is mainly delayed because of documentation. I used it in production since the early 7.3 days and never met a major roadblock.
I advise to check it out.

[marsupilami]

Wow... this means that in the actual ZeosLib Stable 7.2.14 it already contains the patch ? If yes, then I will install into Lazarus.

No - it doesn't. I only update the downloads area from time to time.

Btw... Can You tell me please that a stable version of ZeosLib 8.x when migth be expected ?

There are no major changes to 8.0 expected anymore. As aehimself said - it is mor of a time problem. Just check it out or download a snapshot from SVN.

Best regards,

Jan

[zoltan]

Is this the last version of Zeos 8.0 :

https://github.com/marsupilami79/zeoslib

Sorry, I never downloaded from here, I always used the ZIP file

Many Thanks for Your help!
Zoltan

[aehimself]

Zoltan,

Yes, and no. The official version control of Zeos is SubVersion. This is where all the commits go and being tested. GitHub is secondary, like an alternate for stubborn people (like me) who refuse to learn SubVersion and still want to contribute :) Everything from SubVersion is automatically synced to GitHub once per day.

So to answer your question, yes it is the most recent version. But it's possible that there are other commits in SubVersion, which weren't synced yet.

[zoltan]

Ok, understood... I will test the SubVersion download and return with the result

Meantime I found another problem with Oracle interface but first I will test with the version 8 from SVN.

Thank You very much for Yorur time and help!
Zoltttan

[marsupilami]

Meantime I found another problem with Oracle interface but first I will test with the version 8 from SVN.

Please also let us know about problems on Oracle in Zeos 7.2. This currently is the stable version annd I would like to at least know about problems.

[zoltan]

Ok... then here it is, I can reproduce it anytime. Lets have the following SQL:

--------------------------------------------------------------------------------------------------------------------------------------------------
{ 1} ZO1F.SQLQuery1.SQL.Add(' select SubStr(b.bemerkung,1,50) as bemerkung,');
{ 2} ZO1F.SQLQuery1.SQL.Add(' t.tnre, SubStr(t.bez,1,25) as Megnevezes,');
{ 3} ZO1F.SQLQuery1.SQL.Add(' To_Char(b.mge) as darab, (b.mge * b.ekmittel) as DEPWert,');
{ 4} ZO1F.SQLQuery1.SQL.Add(' TK.UPE AS UPE,');
{ 5} ZO1F.SQLQuery1.SQL.Add(' '+#39+' '+#39+' as something,');
{ 6} ZO1F.SQLQuery1.SQL.Add(' (b.mge*tk.upe) as upewert,');
{ 7} ZO1F.SQLQuery1.SQL.Add(' t.gew as suly,');
{ 8} ZO1F.SQLQuery1.SQL.Add(' b.lagerort, To_Char(b.na_dat) as Idopont,');
{ 9} ZO1F.SQLQuery1.SQL.Add(' nvl(to_char((select sum(bst) from tup where tup.tnr=t.tnr)),0) as Keszlet,');
{ 9} //ZO1F.SQLQuery1.SQL.Add(' to_char((select sum(bst) from tup where tup.tnr=t.tnr)) as Keszlet,');
{10} ZO1F.SQLQuery1.SQL.Add(' G.GITPH AS GITPH, G.BEZ AS GITPHBEZ,');
{11} ZO1F.SQLQuery1.SQL.Add(' to_char(T.LINR) AS LIEFKOD,');
{12} ZO1F.SQLQuery1.SQL.Add(' GPT.NAME AS LIEFNEV');
{13} ZO1F.SQLQuery1.SQL.Add(' from bstkorr b, tst t, GITPH G, GPT, tstkalk tk');
{14} ZO1F.SQLQuery1.SQL.Add(' where SubStr(b.na_dat,1,10) >= '+#39+'2021-03-01'+#39);
{15} ZO1F.SQLQuery1.SQL.Add(' and SubStr(b.na_dat,1,10) <= '+#39+'2021-03-31'+#39);
{16} ZO1F.SQLQuery1.SQL.Add(' and b.bstkorrcd = 3');
{17} ZO1F.SQLQuery1.SQL.Add(' and t.tnr = b.tnr');
{18} ZO1F.SQLQuery1.SQL.Add(' AND T.GITPH=G.GITPH');
{19} ZO1F.SQLQuery1.SQL.Add(' AND T.LINR=GPT.GPTNR');
{20} ZO1F.SQLQuery1.SQL.Add(' AND B.TNR=TK.TNR');
{21} ZO1F.SQLQuery1.SQL.Add(' AND trunc(B.na_dat) > trunc(TK.ABDAT)');
{22} ZO1F.SQLQuery1.SQL.Add(' AND trunc(B.na_dat)<=trunc(TK.BISDAT)');
{23} ZO1F.SQLQuery1.SQL.Add(' order by lagerort');
--------------------------------------------------------------------------------------------------------------------------------------------------

In line 5 we have : -->> #39+' '+#39+' as something <<--
This will cause an Invalid pointer operation / RunError (204) in Lazarus, in unit ZDbcCache, line 3794:

InternalSetPAnsiChar(Data, Value, Len^)

If I change line 5 in the above SQL to -->> #39+'--'+#39+' as something <<-- then it will run perfectly.

We often use this method to insert columns for printed list, eg. check lists in the warehouse, but usually we put "-" or "_".
Usually we dont use spaces, this was modified meantime accidentally to spaces, so we got the problem immediately.

I dont know if You know know about this issue.

Thanks for Your help!
Zoltan

[marsupilami]

Sooo - something like this should give the error too?

Code: Select all

Query.Sql.Text := 'select ' + #39 + ' ' + #39 +' as x from dual';

[aehimself]

Just a small sidenote - adding your query line by line is extremely inefficient, as Zeos is going to parse for parameters after each and every one. Either wrap it around in Query.SQL.BeginUpdate & EndUpdate or simply use SQL.Text as Jan proposed.

[zoltan]

@marsupilami: I have tried this little SQL, even a little bit more, but I could not produce the error. But in my test program I can reproduce...
This I dont understand...

@aehimself: like to write the sql in this form, because this way I can see everything well. This sql is one of the simpliest ones we use, so I want to
see all the displayed rows, especially with sub-select or case and the where conditions.

[aehimself]

I mean, you construct your SQL queries as you wish :) The possibility is there so feel free to use your method.
I strongly advise though that in this case, put it inbetween .SQL.BeginUpdate and .SQL.EndUpdate in a Try..Finally block to pause the parameter parsing.

P.s.: In my personal opinion this code makes the SQL easier to read, especially if it's a longer, more complex one:

Code: Select all

 ZQuery.SQL.Text :=
   'SELECT' + sLineBreak +
   ' *' + sLineBreak +
   'FROM' + sLineBreak +
   ' MyTable' + sLineBreak +
   'WHERE' + sLineBreak +
   ' 1 = 1';

[zoltan]

Sorry to bother You with this, I have never used it, but what the usefullness of BeginUpdate / EndUpdate procedures ?
I always used Open / Close or ExecSQL. They were totally enough for me. I never had speed problems or any other
problems this way.
Nowadays I was thinking about not using :xxxx style parameters, but inserting their values into the SQL string.

[marsupilami]

Sorry to bother You with this, I have never used it, but what the usefullness of BeginUpdate / EndUpdate procedures ?

Zeos parses the SQL statement for parameters whenever you change the SQL property. So on each call to TZQuery.SQL.Add Zeos will parse the SQL statement. This obviously costs time to do work which is not necessary if your SQL is not finished yet. If you call BeginUpdate, Zeos will stop parsing SQL on every change but will only parse it when you call EndUpdate.
If you don't have performance problems because of this, it might be a good idea to simply not call beginupdate and endupdate at all. But if you ever run into problems where Zeos SQL parsing takes too long, this is the way to go.

Nowadays I was thinking about not using :xxxx style parameters, but inserting their values into the SQL string.

There are two reasons not to do this:

1. It will make things slower instead of faster. SQL servers need time for parsing queries. If you use parameters, chances are that your query might be in the query cache and only your parameters differ from another query. Maybe a query you already did. By inserting parameters into the SQL, you make sure that queries almost never match and the SQL server will not have a chance to use its cache of compiled queries.
2. Also it is a security problem. Passing values as parameters makes sure, they don't interfere with the SQL code in any way. Doing this kind of things yourself makes you susceptible to SQL injection attacks. Imagine the following:
   • You have an edit for entering the name of somebody.
   • Somebody writes code like this:

     SQL.Query.Text := 'insert into people (name) values (''' + NameEdt.Text + ''')'

   • If Edit.Text has the value >x'; drop table users;--< bad things might happen in your database.
   • There is an xkcd comic about this

So - basically parameters keep your server fast and keep you from having security issues with moms

[marsupilami]

@marsupilami: I have tried this little SQL, even a little bit more, but I could not produce the error. But in my test program I can reproduce...
This I dont understand...

Well - I don't understand this too. Could you please tro to make a small sample application that can help us in debugging the issue? Like a script for creating a table, inserting data, if necessary and a small program that generates the error?

Best regards,

Jan

[zoltan]

Thank You Jan very much for the BeginUpdate / EndUpdate explanation! Until now I never had time problems, but now I will pay attention.

I heard about SQL injection attacks, but luckily until never met such problem, hopefully I never will

Tested that little sql : 'select ' ' from dual'. Just by itself it runs with no problems. But if I include any other tables, like

SQL.Add('select t.tnre, ');
SQL.Add(' '+#39+' '+#39+' as valami');
SQL.Add('from tst t');
SQL.Add('where tnre like '+#39+'115600%'+#39);

Immediately has the error. My program is very simple: on a form : ZConnection, ZQuery, Datasource and a DBGrid, all linked together.
Yeah, and a ZSQLMonitor. The generated log is the following:

------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2021-12-15 14:33:12 cat: Connect, proto: oracle-9i, msg: CONNECT TO "ETN03DB.prod.hu" AS USER "etndbhurep"
2021-12-15 14:33:12 cat: Execute, proto: oracle-9i, msg: SET TRANSACTION ISOLATION LEVEL DEFAULT
2021-12-15 14:33:14 cat: Prepare, proto: oracle-9i, msg: Statement 1 :
select t.tnre,
' ' as valami
from tst t
where tnre like '115600%'

2021-12-15 14:33:14 cat: Execute prepared, proto: oracle-9i, msg: Statement 1
2021-12-15 14:33:14 cat: Prepare, proto: oracle-9i, msg: Statement 2 : SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, DATA_DEFAULT, COLUMN_ID FROM SYS.ALL_TAB_COLUMNS Where TABLE_NAME = 'TST' order by COLUMN_ID
2021-12-15 14:33:14 cat: Execute prepared, proto: oracle-9i, msg: Statement 2
2021-12-15 14:33:18 cat: Prepare, proto: oracle-9i, msg: Statement 3 : SELECT SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA') FROM DUAL
2021-12-15 14:33:18 cat: Execute prepared, proto: oracle-9i, msg: Statement 3
------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Thank You for Your help!
Zoltan