ZParam: Set value without changing datatype.

[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

[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.

[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

[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?

[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?

[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?

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 :(

[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'

[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