Page 1 of 1
ZParam: Set value without changing datatype.
Posted: 29.04.2021, 14:25
by MJFShark
Hey all!
How do you set the value of a ZParam without changing its datatype? Basically I'm wondering if I set the datatype of a ZParam to a date, then how can I set its value using a string while keeping it a datetype? I thought that I could do something like:
MyParam.SQLType := stDate;
MyParam.Value := '2021-04-29'; // Seems to change the datatype to string.
The source documentation talks about "locked" parameter types, so my question may be "How to lock a parameter's datatype" OR am I thinking about this incorrectly and I shouldn't be trying to force a particular datatype in this way? Thanks for any insights!
-Mark
Re: ZParam: Set value without changing datatype.
Posted: 29.04.2021, 19:59
by aehimself
See
r7507, this was changed just recently. I *think* it's supposed to work this way but I got no confirmation so this commit can be wrong.
What you want can be achieved by setting the Value first and the type next.
Re: ZParam: Set value without changing datatype.
Posted: 29.04.2021, 22:04
by MJFShark
I did see that one and changed it (yesterday I think.)
Does setting the value and then the datatype actually do a conversion? I don't see that in my tests.
I see that InternalSetAsXXXX looks like it's capable of doing conversions, but CheckDataIndex sets FSQLDataType to the variant type and then no conversion occurs. I *think* there's something wrong somewhere in there. It's a bit hard to follow as I'm not sure how FSQLType and FSQLDataType relate, and the SQLType property is often masked by local vars and arguments of the same name (which I always worry about.)
I'll do more research and see what I can find.
-Mark
Re: ZParam: Set value without changing datatype.
Posted: 30.04.2021, 05:44
by EgonHugeist
Hi,
@aehimself
what was the reason for your pull request? I don't get it. Usually users are setting the param-Type to lock this type. Your patch did change the log vice versa. Can you explain it?
@Mark
i did change that again:
https://sourceforge.net/p/zeoslib/code-0/7518/. It should also handle
https://sourceforge.net/p/zeoslib/tickets/480/ i bit better. Mark iiuyc then do you expect an implizit converstion from String to TDate, is that correct?
Re: ZParam: Set value without changing datatype.
Posted: 30.04.2021, 08:45
by marsupilami
I didn't mention it in the commit but aehimselfs pull request is related to thi:
viewtopic.php?p=170902#p170902
Honestly, I think that setting a parameter by using .Value should change the paramtype to whatever data type is in the Variant? Why would we not do it like that?
Re: ZParam: Set value without changing datatype.
Posted: 30.04.2021, 10:13
by aehimself
EgonHugeist wrote: ↑30.04.2021, 05:44what was the reason for your pull request? I don't get it. Usually users are setting the param-Type to lock this type. Your patch did change the log vice versa. Can you explain it?
As Jan mentioned, an other topic brought the "unknown" type to light. I was unsure about the correct behavior, but typically it's faster to issue a pull request and in case it causes havoc to roll it back than to have an open discussion.
I saw your change this morning, this was the other way it could have been solved. Shame I didn't choose this path :(
Re: ZParam: Set value without changing datatype.
Posted: 30.04.2021, 11:30
by eversun
Quite possible I have a near problem. I have stored procedure with input parameter of type STRING, but it's internally handled for passing different types (i.e. bigint, float, etc). If I make a call
select * from MYPROC(:VAL)
with Query.ParamByName('VAL').AsLargeInt := 999;
then on Query.Open I got error 'Invalid token'
Re: ZParam: Set value without changing datatype.
Posted: 30.04.2021, 12:47
by MJFShark
[update - moved the ReadFormatSettings stuff to a new post]
The new change is good! I just modified the date/time/timestamp conversions to use the nice new ReadFormatSettings. I'm using FConnSettings, but I'm not sure if it should be using a different level. This code was copied from ZDbcCache.pas and while doing this I found a number of cases where ReadFormatSettings.DateFormat is used instead of either TimeFormat or DateTime format. I can do a pull request with the fix, but I'm not around today.
Code: Select all
// Add TS: TZTimeStamp; to vars btw
stDate: if not ZSysUtils.TryUniToDate(P, L, FConSettings^.ReadFormatSettings.DateFormat, PZDate(DataAddr)^) then
if ZSysUtils.TryUniToTimeStamp(P, L, FConSettings^.ReadFormatSettings.DateTimeFormat, TS{%H-}) then begin
PZDate(DataAddr)^.Year := TS.Year;
PZDate(DataAddr)^.Month := TS.Month;
PZDate(DataAddr)^.Day := TS.Day;
PZDate(DataAddr)^.IsNegative := TS.IsNegative;
end;
stTime: if not ZSysUtils.TryUniToTime(P, L, FConSettings^.ReadFormatSettings.TimeFormat, PZTime(DataAddr)^) then
if ZSysUtils.TryUniToTimeStamp(P, L, FConSettings^.ReadFormatSettings.DateTimeFormat, TS) then begin
PZTime(DataAddr)^ := PZTime(@TS.Hour)^;
PZTime(DataAddr)^.IsNegative := False;
end;
stTimestamp: ZSysUtils.TryUniToTimeStamp(P, L, FConSettings^.ReadFormatSettings.DateTimeFormat, PZTimeStamp(DataAddr)^);
-Mark