To format a date as a varchar with zero-padded month and day, here’s a SQL
select snippet that works for me in Postgres 9.3, but I think it should work in
FrontBase maybe with a little fiddling of syntax such as substring(’00’ ||
‘1234’ from 3 for 2) where starting position is 3, and the number of characters
you want is 2. (I switched from FrontBase to Postgres a couple of years ago but
used to use this technique in FrontBase).
The column “createdstamp” is the only indicated date column below; all the rest
of the output is calculated from it. What you’re looking for is the last column
that is composed of lines 7 thru 10. The snippet below runs in my Postgres
shell (without the line numbers of course). I’ve posted the snippet in fixed
width font, Monaco 12. If it looks ugly by the time it gets posted, open in a
text editor with at least a 158 char wide line length.
1. select
2. createdstamp,
3. cast(extract(year from createdStamp) as varchar) as "year",
4. substr('00' || cast(extract(month from createdStamp) as varchar),
char_length('00' || cast(extract(month from createdStamp) as varchar))-1,2) as
"month",
5. substr('00' || cast(extract(day from createdStamp) as varchar),
char_length('00' || cast(extract(day from createdStamp) as varchar))-1,2) as
"day",
6.
7. cast(extract(year from createdStamp) as varchar)
8. || substr('00' || cast(extract(month from createdStamp) as varchar),
char_length('00' || cast(extract(month from createdStamp) as varchar))-1,2)
9. || substr('00' || cast(extract(day from createdStamp) as varchar),
char_length('00' || cast(extract(day from createdStamp) as varchar))-1,2)
10. as "YYYYMMDD"
11. from myTable;
---------------------------------------------------------
createdstamp year month day YYYYMMDD
---------------------------------------------------------
2003-01-06 09:41:26 2003 01 06 20030106
2015-09-23 11:21:59.182 2015 09 23 20150923
2002-12-02 17:19:51 2002 12 02 20021202
2002-12-03 12:13:31 2002 12 03 20021203
2002-12-14 14:52:55 2002 12 14 20021214
- Marco A. Gonzalez
> On Mar 12, 2016, at 12:14 PM, webobjects-dev-requ...@lists.apple.com wrote:
>
> Send Webobjects-dev mailing list submissions to
> webobjects-dev@lists.apple.com
>
> To subscribe or unsubscribe via the World Wide Web, visit
> https://lists.apple.com/mailman/listinfo/webobjects-dev
> or, via email, send a message with subject or body 'help' to
> webobjects-dev-requ...@lists.apple.com
>
> You can reach the person managing the list at
> webobjects-dev-ow...@lists.apple.com
>
> When replying, please edit your Subject line so it is more specific
> than "Re: Contents of Webobjects-dev digest..."
> Today's Topics:
>
> 1. FrontBase SQL: date formatting, „local variable“? (OC)
> 2. Re: FrontBase SQL: date formatting, „local variable“?
> (Amedeo Mantica)
> 3. Re: FrontBase SQL: date formatting, „local variable“?
> (Chuck Hill)
> 4. Re: FrontBase SQL: date formatting, „local variable“?
> (Chuck Hill)
> 5. Re: FrontBase SQL: date formatting, „local variable“? (OC)
> 6. Re: FrontBase SQL: date formatting, „local variable“?
> (Amedeo Mantica)
> 7. Re: FrontBase SQL: date formatting, „local variable“? (OC)
> 8. Re: FrontBase SQL: date formatting, „local variable“?
> (Samuel Pelletier)
> 9. derived attributes in qualifier? (OC)
> 10. WebObjects and Apache 2.4 on El Capitan (Patrick Middleton)
>
>
>
> From: OC <o...@ocs.cz>
> Subject: FrontBase SQL: date formatting, „local variable“?
> Date: March 11, 2016 at 3:47:15 PM EST
> To: WebObjects Development <webobjects-dev@lists.apple.com>
>
>
> Hello there,
>
> any FrontBase SQL gurus on-line? For a derived attribute, I would need
>
> -- (a) to format a TIMESTAMP as “%Y%n%e”
>
> So far the best I have found is
>
> CAST(EXTRACT(year FROM DATE) AS VARCHAR(4)) ||
> CAST(EXTRACT(month FROM DATE) AS VARCHAR(2)) ||
> CAST(EXTRACT(day FROM DATE) AS VARCHAR(2))
>
> but this does not zero-pad, ie., instead of the desired “20160302” I get
> improper result “201632”.
>
> How to zero-pad the month and day?
>
> -- (b) to define and use a “local variable”
>
> The problem is that the “DATE” above is not a column, but a SELECT
> sub-expression. If I repeat it again and again and again, it does work all
> right, but the SQL is really ugly (and, I suppose, also inefficient). I would
> need something like
>
> VAR mydate=( SELECT C_START_DATE FROM T_TIME_CHUNK WHERE T_TIME_CHUNK.C_UID =
> C_TIME_CHUNK_UID )
> CASE
> WHEN mydate IS NULL THEN 'fallback-value'
> ELSE
> CAST(EXTRACT(year FROM mydate) AS VARCHAR(4)) ||
> CAST(EXTRACT(month FROM mydate) AS VARCHAR(2)) ||
> ...
> END
>
> Can this be done anyhow?
>
> Thanks a big lot,
> OC
>
>
>
>
>
>
>
>
>
>
> From: Amedeo Mantica <amedeomant...@me.com>
> Subject: Re: FrontBase SQL: date formatting, „local variable“?
> Date: March 11, 2016 at 3:52:29 PM EST
> To: OC <o...@ocs.cz>
> Cc: WebObjects Development <webobjects-dev@lists.apple.com>
>
>
> sgvscohsvosvhpveusvvuvssvvubwbwbibebiwbibeubvusv !??
>
> Sent from my iPhone
>
>> On 11 Mar 2016, at 21:47, OC <o...@ocs.cz> wrote:
>>
>> SGVsbG8gdGhlcmUsCgphbnkgRnJvbnRCYXNlIFNRTCBndXJ1cyBvbi1saW5lPyBGb3IgYSBkZXJp
>> dmVkIGF0dHJpYnV0ZSwgSSB3b3VsZCBuZWVkCgotLSAoYSkgdG8gZm9ybWF0IGEgVElNRVNUQU1Q
>> IGFzIOKAnCVZJW4lZeKAnQoKU28gZmFyIHRoZSBiZXN0IEkgaGF2ZSBmb3VuZCBpcwoKQ0FTVChF
>> WFRSQUNUKHllYXIgRlJPTSBEQVRFKSBBUyBWQVJDSEFSKDQpKSB8fApDQVNUKEVYVFJBQ1QobW9u
>> dGggRlJPTSBEQVRFKSBBUyBWQVJDSEFSKDIpKSB8fApDQVNUKEVYVFJBQ1QoZGF5IEZST00gREFU
>> RSkgQVMgVkFSQ0hBUigyKSkKCmJ1dCB0aGlzIGRvZXMgbm90IHplcm8tcGFkLCBpZS4sIGluc3Rl
>> YWQgb2YgdGhlIGRlc2lyZWQg4oCcMjAxNjAzMDLigJ0gSSBnZXQgaW1wcm9wZXIgcmVzdWx0IOKA
>> nDIwMTYzMuKAnS4KCkhvdyB0byB6ZXJvLXBhZCB0aGUgbW9udGggYW5kIGRheT8KCi0tIChiKSB0
>> byBkZWZpbmUgYW5kIHVzZSBhIOKAnGxvY2FsIHZhcmlhYmxl4oCdCgpUaGUgcHJvYmxlbSBpcyB0
>> aGF0IHRoZSDigJxEQVRF4oCdIGFib3ZlIGlzIG5vdCBhIGNvbHVtbiwgYnV0IGEgU0VMRUNUIHN1
>> Yi1leHByZXNzaW9uLiBJZiBJIHJlcGVhdCBpdCBhZ2FpbiBhbmQgYWdhaW4gYW5kIGFnYWluLCBp
>> dCBkb2VzIHdvcmsgYWxsIHJpZ2h0LCBidXQgdGhlIFNRTCBpcyByZWFsbHkgdWdseSAoYW5kLCBJ
>> IHN1cHBvc2UsIGFsc28gaW5lZmZpY2llbnQpLiBJIHdvdWxkIG5lZWQgc29tZXRoaW5nIGxpa2UK
>> ClZBUiBteWRhdGU9KCBTRUxFQ1QgQ19TVEFSVF9EQVRFIEZST00gVF9USU1FX0NIVU5LIFdIRVJF
>> IFRfVElNRV9DSFVOSy5DX1VJRCA9IENfVElNRV9DSFVOS19VSUQgKQpDQVNFCiAgV0hFTiBteWRh
>> dGUgSVMgTlVMTCBUSEVOICdmYWxsYmFjay12YWx1ZScKICBFTFNFCiAgICBDQVNUKEVYVFJBQ1Qo
>> eWVhciBGUk9NIG15ZGF0ZSkgQVMgVkFSQ0hBUig0KSkgfHwKICAgIENBU1QoRVhUUkFDVChtb250
>> aCBGUk9NIG15ZGF0ZSkgQVMgVkFSQ0hBUigyKSkgfHwKICAgIC4uLgpFTkQKCkNhbiB0aGlzIGJl
>> IGRvbmUgYW55aG93PwoKVGhhbmtzIGEgYmlnIGxvdCwKT0MKCgogX19fX19fX19fX19fX19fX19f
>> X19fX19fX19fX19fX19fX19fX19fX19fX19fX18KRG8gbm90IHBvc3QgYWRtaW4gcmVxdWVzdHMg
>> dG8gdGhlIGxpc3QuIFRoZXkgd2lsbCBiZSBpZ25vcmVkLgpXZWJvYmplY3RzLWRldiBtYWlsaW5n
>> IGxpc3QgICAgICAoV2Vib2JqZWN0cy1kZXZAbGlzdHMuYXBwbGUuY29tKQpIZWxwL1Vuc3Vic2Ny
>> aWJlL1VwZGF0ZSB5b3VyIFN1YnNjcmlwdGlvbjoKaHR0cHM6Ly9saXN0cy5hcHBsZS5jb20vbWFp
>> bG1hbi9vcHRpb25zL3dlYm9iamVjdHMtZGV2L2FtZWRlb21hbnRpY2ElNDBtZS5jb20KClRoaXMg
>> ZW1haWwgc2VudCB0byBhbWVkZW9tYW50aWNhQG1lLmNvbQ==
>
>
>
>
>
>
>
>
>
> From: Chuck Hill <ch...@gevityinc.com>
> Subject: Re: FrontBase SQL: date formatting, „local variable“?
> Date: March 11, 2016 at 3:58:35 PM EST
> To: Amedeo Mantica <amedeomant...@me.com>, OC <o...@ocs.cz>
> Cc: WebObjects Development <webobjects-dev@lists.apple.com>
>
>
> The message looked fine to me both on my iPhone and in my mail client.
>
>
>
>
> On 2016-03-11, 12:52 PM,
> "webobjects-dev-bounces+chill=gevityinc....@lists.apple.com on behalf of
> Amedeo Mantica" <webobjects-dev-bounces+chill=gevityinc....@lists.apple.com
> on behalf of amedeomant...@me.com> wrote:
>
>> sgvscohsvosvhpveusvvuvssvvubwbwbibebiwbibeubvusv !??
>>
>> Sent from my iPhone
>>
>>> On 11 Mar 2016, at 21:47, OC <o...@ocs.cz> wrote:
>>>
>>> SGVsbG8gdGhlcmUsCgphbnkgRnJvbnRCYXNlIFNRTCBndXJ1cyBvbi1saW5lPyBGb3IgYSBkZXJp
>>> dmVkIGF0dHJpYnV0ZSwgSSB3b3VsZCBuZWVkCgotLSAoYSkgdG8gZm9ybWF0IGEgVElNRVNUQU1Q
>>> IGFzIOKAnCVZJW4lZeKAnQoKU28gZmFyIHRoZSBiZXN0IEkgaGF2ZSBmb3VuZCBpcwoKQ0FTVChF
>>> WFRSQUNUKHllYXIgRlJPTSBEQVRFKSBBUyBWQVJDSEFSKDQpKSB8fApDQVNUKEVYVFJBQ1QobW9u
>>> dGggRlJPTSBEQVRFKSBBUyBWQVJDSEFSKDIpKSB8fApDQVNUKEVYVFJBQ1QoZGF5IEZST00gREFU
>>> RSkgQVMgVkFSQ0hBUigyKSkKCmJ1dCB0aGlzIGRvZXMgbm90IHplcm8tcGFkLCBpZS4sIGluc3Rl
>>> YWQgb2YgdGhlIGRlc2lyZWQg4oCcMjAxNjAzMDLigJ0gSSBnZXQgaW1wcm9wZXIgcmVzdWx0IOKA
>>> nDIwMTYzMuKAnS4KCkhvdyB0byB6ZXJvLXBhZCB0aGUgbW9udGggYW5kIGRheT8KCi0tIChiKSB0
>>> byBkZWZpbmUgYW5kIHVzZSBhIOKAnGxvY2FsIHZhcmlhYmxl4oCdCgpUaGUgcHJvYmxlbSBpcyB0
>>> aGF0IHRoZSDigJxEQVRF4oCdIGFib3ZlIGlzIG5vdCBhIGNvbHVtbiwgYnV0IGEgU0VMRUNUIHN1
>>> Yi1leHByZXNzaW9uLiBJZiBJIHJlcGVhdCBpdCBhZ2FpbiBhbmQgYWdhaW4gYW5kIGFnYWluLCBp
>>> dCBkb2VzIHdvcmsgYWxsIHJpZ2h0LCBidXQgdGhlIFNRTCBpcyByZWFsbHkgdWdseSAoYW5kLCBJ
>>> IHN1cHBvc2UsIGFsc28gaW5lZmZpY2llbnQpLiBJIHdvdWxkIG5lZWQgc29tZXRoaW5nIGxpa2UK
>>> ClZBUiBteWRhdGU9KCBTRUxFQ1QgQ19TVEFSVF9EQVRFIEZST00gVF9USU1FX0NIVU5LIFdIRVJF
>>> IFRfVElNRV9DSFVOSy5DX1VJRCA9IENfVElNRV9DSFVOS19VSUQgKQpDQVNFCiAgV0hFTiBteWRh
>>> dGUgSVMgTlVMTCBUSEVOICdmYWxsYmFjay12YWx1ZScKICBFTFNFCiAgICBDQVNUKEVYVFJBQ1Qo
>>> eWVhciBGUk9NIG15ZGF0ZSkgQVMgVkFSQ0hBUig0KSkgfHwKICAgIENBU1QoRVhUUkFDVChtb250
>>> aCBGUk9NIG15ZGF0ZSkgQVMgVkFSQ0hBUigyKSkgfHwKICAgIC4uLgpFTkQKCkNhbiB0aGlzIGJl
>>> IGRvbmUgYW55aG93PwoKVGhhbmtzIGEgYmlnIGxvdCwKT0MKCgogX19fX19fX19fX19fX19fX19f
>>> X19fX19fX19fX19fX19fX19fX19fX19fX19fX18KRG8gbm90IHBvc3QgYWRtaW4gcmVxdWVzdHMg
>>> dG8gdGhlIGxpc3QuIFRoZXkgd2lsbCBiZSBpZ25vcmVkLgpXZWJvYmplY3RzLWRldiBtYWlsaW5n
>>> IGxpc3QgICAgICAoV2Vib2JqZWN0cy1kZXZAbGlzdHMuYXBwbGUuY29tKQpIZWxwL1Vuc3Vic2Ny
>>> aWJlL1VwZGF0ZSB5b3VyIFN1YnNjcmlwdGlvbjoKaHR0cHM6Ly9saXN0cy5hcHBsZS5jb20vbWFp
>>> bG1hbi9vcHRpb25zL3dlYm9iamVjdHMtZGV2L2FtZWRlb21hbnRpY2ElNDBtZS5jb20KClRoaXMg
>>> ZW1haWwgc2VudCB0byBhbWVkZW9tYW50aWNhQG1lLmNvbQ==
>>
>> _______________________________________________
>> Do not post admin requests to the list. They will be ignored.
>> Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
>> Help/Unsubscribe/Update your Subscription:
>> https://lists.apple.com/mailman/options/webobjects-dev/chill%40gevityinc.com
>>
>> This email sent to ch...@gevityinc.com
>
>
>
>
>
>
>
>
>
> From: Chuck Hill <ch...@gevityinc.com>
> Subject: Re: FrontBase SQL: date formatting, „local variable“?
> Date: March 11, 2016 at 3:59:53 PM EST
> To: OC <o...@ocs.cz>, WebObjects Development <webobjects-dev@lists.apple.com>
>
>
> It is supposed to be SQL92 compliant. If standard SQL can’t do it, you might
> be out of luck. This is not something you can do client-side?
>
> Chuck
>
>
>
>
>
> On 2016-03-11, 12:47 PM,
> "webobjects-dev-bounces+chill=gevityinc....@lists.apple.com on behalf of OC"
> <webobjects-dev-bounces+chill=gevityinc....@lists.apple.com on behalf of
> o...@ocs.cz> wrote:
>
>> Hello there,
>>
>> any FrontBase SQL gurus on-line? For a derived attribute, I would need
>>
>> -- (a) to format a TIMESTAMP as “%Y%n%e”
>>
>> So far the best I have found is
>>
>> CAST(EXTRACT(year FROM DATE) AS VARCHAR(4)) ||
>> CAST(EXTRACT(month FROM DATE) AS VARCHAR(2)) ||
>> CAST(EXTRACT(day FROM DATE) AS VARCHAR(2))
>>
>> but this does not zero-pad, ie., instead of the desired “20160302” I get
>> improper result “201632”.
>>
>> How to zero-pad the month and day?
>>
>> -- (b) to define and use a “local variable”
>>
>> The problem is that the “DATE” above is not a column, but a SELECT
>> sub-expression. If I repeat it again and again and again, it does work all
>> right, but the SQL is really ugly (and, I suppose, also inefficient). I
>> would need something like
>>
>> VAR mydate=( SELECT C_START_DATE FROM T_TIME_CHUNK WHERE T_TIME_CHUNK.C_UID
>> = C_TIME_CHUNK_UID )
>> CASE
>> WHEN mydate IS NULL THEN 'fallback-value'
>> ELSE
>> CAST(EXTRACT(year FROM mydate) AS VARCHAR(4)) ||
>> CAST(EXTRACT(month FROM mydate) AS VARCHAR(2)) ||
>> ...
>> END
>>
>> Can this be done anyhow?
>>
>> Thanks a big lot,
>> OC
>>
>>
>> _______________________________________________
>> Do not post admin requests to the list. They will be ignored.
>> Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
>> Help/Unsubscribe/Update your Subscription:
>> https://lists.apple.com/mailman/options/webobjects-dev/chill%40gevityinc.com
>>
>> This email sent to ch...@gevityinc.com
>
>
>
>
>
>
>
>
>
> From: OC <o...@ocs.cz>
> Subject: Re: FrontBase SQL: date formatting, „local variable“?
> Date: March 11, 2016 at 4:06:41 PM EST
> To: Chuck Hill <ch...@gevityinc.com>
> Cc: WebObjects Development <webobjects-dev@lists.apple.com>
>
>
> Chuck,
>
> On 11. 3. 2016, at 21:59, Chuck Hill <ch...@gevityinc.com> wrote:
>
>> It is supposed to be SQL92 compliant. If standard SQL can’t do it, you might
>> be out of luck.
>
> I am no SQL guru myself; the google results look like there is a terrible
> mess of standard- and server-specific tools. Eg., there seems to be
> DATE_FORMAT(date,'format') -- which does not work in FrontBase. There seems
> to be LPAD(string,width,padding) -- which does not work there either.
>
> I did not succeed to find any decent SQL92 description; the best I have found
> is
>
> http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
>
> and that one's pretty messy. Anyhow, so far, I haven't been able to find a
> solution there either.
>
> Still, it can be solvable (perhaps even easily)
>
> (a) in standard SQL92 which I don't know well enough
> (b) in some FrontBase-specific extension
>
> which is why I am asking.
>
>> This is not something you can do client-side?
>
> Not easily; I would rather need to implement it as a modelled derived
> attribute.
>
> If it proves impossible, I can mangle the data client-side, but it would get
> pretty messy: those are still the rawrows, no entity code there to help.
>
> Thanks a lot,
> OC
>
>> n 2016-03-11, 12:47 PM,
>> "webobjects-dev-bounces+chill=gevityinc....@lists.apple.com on behalf of OC"
>> <webobjects-dev-bounces+chill=gevityinc....@lists.apple.com on behalf of
>> o...@ocs.cz> wrote:
>>
>>> Hello there,
>>>
>>> any FrontBase SQL gurus on-line? For a derived attribute, I would need
>>>
>>> -- (a) to format a TIMESTAMP as “%Y%n%e”
>>>
>>> So far the best I have found is
>>>
>>> CAST(EXTRACT(year FROM DATE) AS VARCHAR(4)) ||
>>> CAST(EXTRACT(month FROM DATE) AS VARCHAR(2)) ||
>>> CAST(EXTRACT(day FROM DATE) AS VARCHAR(2))
>>>
>>> but this does not zero-pad, ie., instead of the desired “20160302” I get
>>> improper result “201632”.
>>>
>>> How to zero-pad the month and day?
>>>
>>> -- (b) to define and use a “local variable”
>>>
>>> The problem is that the “DATE” above is not a column, but a SELECT
>>> sub-expression. If I repeat it again and again and again, it does work all
>>> right, but the SQL is really ugly (and, I suppose, also inefficient). I
>>> would need something like
>>>
>>> VAR mydate=( SELECT C_START_DATE FROM T_TIME_CHUNK WHERE T_TIME_CHUNK.C_UID
>>> = C_TIME_CHUNK_UID )
>>> CASE
>>> WHEN mydate IS NULL THEN 'fallback-value'
>>> ELSE
>>> CAST(EXTRACT(year FROM mydate) AS VARCHAR(4)) ||
>>> CAST(EXTRACT(month FROM mydate) AS VARCHAR(2)) ||
>>> ...
>>> END
>>>
>>> Can this be done anyhow?
>>>
>>> Thanks a big lot,
>>> OC
>>>
>>>
>>> _______________________________________________
>>> Do not post admin requests to the list. They will be ignored.
>>> Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
>>> Help/Unsubscribe/Update your Subscription:
>>> https://lists.apple.com/mailman/options/webobjects-dev/chill%40gevityinc.com
>>>
>>> This email sent to ch...@gevityinc.com
>
>
>
>
>
>
>
>
>
>
> From: Amedeo Mantica <amedeomant...@me.com>
> Subject: Re: FrontBase SQL: date formatting, „local variable“?
> Date: March 11, 2016 at 4:16:58 PM EST
> To: Chuck Hill <ch...@gevityinc.com>
> Cc: WebObjects Development <webobjects-dev@lists.apple.com>
>
>
> looks like my iphone didn't decoded from base64, weird
>
>> On 11 mar 2016, at 21:58, Chuck Hill <ch...@gevityinc.com> wrote:
>>
>> The message looked fine to me both on my iPhone and in my mail client.
>>
>>
>>
>>
>> On 2016-03-11, 12:52 PM,
>> "webobjects-dev-bounces+chill=gevityinc....@lists.apple.com on behalf of
>> Amedeo Mantica" <webobjects-dev-bounces+chill=gevityinc....@lists.apple.com
>> on behalf of amedeomant...@me.com> wrote:
>>
>>> sgvscohsvosvhpveusvvuvssvvubwbwbibebiwbibeubvusv !??
>>>
>>> Sent from my iPhone
>>>
>>>> On 11 Mar 2016, at 21:47, OC <o...@ocs.cz> wrote:
>>>>
>>>> SGVsbG8gdGhlcmUsCgphbnkgRnJvbnRCYXNlIFNRTCBndXJ1cyBvbi1saW5lPyBGb3IgYSBkZXJp
>>>> dmVkIGF0dHJpYnV0ZSwgSSB3b3VsZCBuZWVkCgotLSAoYSkgdG8gZm9ybWF0IGEgVElNRVNUQU1Q
>>>> IGFzIOKAnCVZJW4lZeKAnQoKU28gZmFyIHRoZSBiZXN0IEkgaGF2ZSBmb3VuZCBpcwoKQ0FTVChF
>>>> WFRSQUNUKHllYXIgRlJPTSBEQVRFKSBBUyBWQVJDSEFSKDQpKSB8fApDQVNUKEVYVFJBQ1QobW9u
>>>> dGggRlJPTSBEQVRFKSBBUyBWQVJDSEFSKDIpKSB8fApDQVNUKEVYVFJBQ1QoZGF5IEZST00gREFU
>>>> RSkgQVMgVkFSQ0hBUigyKSkKCmJ1dCB0aGlzIGRvZXMgbm90IHplcm8tcGFkLCBpZS4sIGluc3Rl
>>>> YWQgb2YgdGhlIGRlc2lyZWQg4oCcMjAxNjAzMDLigJ0gSSBnZXQgaW1wcm9wZXIgcmVzdWx0IOKA
>>>> nDIwMTYzMuKAnS4KCkhvdyB0byB6ZXJvLXBhZCB0aGUgbW9udGggYW5kIGRheT8KCi0tIChiKSB0
>>>> byBkZWZpbmUgYW5kIHVzZSBhIOKAnGxvY2FsIHZhcmlhYmxl4oCdCgpUaGUgcHJvYmxlbSBpcyB0
>>>> aGF0IHRoZSDigJxEQVRF4oCdIGFib3ZlIGlzIG5vdCBhIGNvbHVtbiwgYnV0IGEgU0VMRUNUIHN1
>>>> Yi1leHByZXNzaW9uLiBJZiBJIHJlcGVhdCBpdCBhZ2FpbiBhbmQgYWdhaW4gYW5kIGFnYWluLCBp
>>>> dCBkb2VzIHdvcmsgYWxsIHJpZ2h0LCBidXQgdGhlIFNRTCBpcyByZWFsbHkgdWdseSAoYW5kLCBJ
>>>> IHN1cHBvc2UsIGFsc28gaW5lZmZpY2llbnQpLiBJIHdvdWxkIG5lZWQgc29tZXRoaW5nIGxpa2UK
>>>> ClZBUiBteWRhdGU9KCBTRUxFQ1QgQ19TVEFSVF9EQVRFIEZST00gVF9USU1FX0NIVU5LIFdIRVJF
>>>> IFRfVElNRV9DSFVOSy5DX1VJRCA9IENfVElNRV9DSFVOS19VSUQgKQpDQVNFCiAgV0hFTiBteWRh
>>>> dGUgSVMgTlVMTCBUSEVOICdmYWxsYmFjay12YWx1ZScKICBFTFNFCiAgICBDQVNUKEVYVFJBQ1Qo
>>>> eWVhciBGUk9NIG15ZGF0ZSkgQVMgVkFSQ0hBUig0KSkgfHwKICAgIENBU1QoRVhUUkFDVChtb250
>>>> aCBGUk9NIG15ZGF0ZSkgQVMgVkFSQ0hBUigyKSkgfHwKICAgIC4uLgpFTkQKCkNhbiB0aGlzIGJl
>>>> IGRvbmUgYW55aG93PwoKVGhhbmtzIGEgYmlnIGxvdCwKT0MKCgogX19fX19fX19fX19fX19fX19f
>>>> X19fX19fX19fX19fX19fX19fX19fX19fX19fX18KRG8gbm90IHBvc3QgYWRtaW4gcmVxdWVzdHMg
>>>> dG8gdGhlIGxpc3QuIFRoZXkgd2lsbCBiZSBpZ25vcmVkLgpXZWJvYmplY3RzLWRldiBtYWlsaW5n
>>>> IGxpc3QgICAgICAoV2Vib2JqZWN0cy1kZXZAbGlzdHMuYXBwbGUuY29tKQpIZWxwL1Vuc3Vic2Ny
>>>> aWJlL1VwZGF0ZSB5b3VyIFN1YnNjcmlwdGlvbjoKaHR0cHM6Ly9saXN0cy5hcHBsZS5jb20vbWFp
>>>> bG1hbi9vcHRpb25zL3dlYm9iamVjdHMtZGV2L2FtZWRlb21hbnRpY2ElNDBtZS5jb20KClRoaXMg
>>>> ZW1haWwgc2VudCB0byBhbWVkZW9tYW50aWNhQG1lLmNvbQ==
>>>
>>> _______________________________________________
>>> Do not post admin requests to the list. They will be ignored.
>>> Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
>>> Help/Unsubscribe/Update your Subscription:
>>> https://lists.apple.com/mailman/options/webobjects-dev/chill%40gevityinc.com
>>>
>>> This email sent to ch...@gevityinc.com
>
>
>
>
>
>
>
>
>
>
> From: OC <o...@ocs.cz>
> Subject: Re: FrontBase SQL: date formatting, „local variable“?
> Date: March 11, 2016 at 5:57:34 PM EST
> To: WebObjects Development <webobjects-dev@lists.apple.com>
>
>
> Well...
>
> On 11. 3. 2016, at 22:06, OC <o...@ocs.cz> wrote:
>
>> Still, it can be solvable (perhaps even easily)
>>
>> (a) in standard SQL92 which I don't know well enough
>> (b) in some FrontBase-specific extension
>>
>> which is why I am asking.
>
> ... without better knowledge the SQL used to derive the attribute looks like
> this. Yes, it is _terribly_ ugly, but I can't see how to simplify it whilst
> keeping the functionality. I would appreaciate if anybody sees and advices
> how to simplify this brute, especially if there's a way to get rid of the
> repeated nested SELECTs:
>
> ===
> CASE
> WHEN ( SELECT C_SHORTCUT FROM T_MARKET WHERE T_MARKET.C_UID = C_MARKET_ID )
> IS NULL
> THEN
> 'Market(' ||
> CAST(C_MARKET_ID as VARCHAR(18)) ||
> ')'
> ELSE ( SELECT C_SHORTCUT FROM T_MARKET WHERE T_MARKET.C_UID = C_MARKET_ID )
> END ||
> '-' ||
> CASE
> WHEN ( SELECT C_START_DATE FROM T_TIME_CHUNK WHERE T_TIME_CHUNK.C_UID =
> C_TIME_CHUNK_UID ) IS NULL THEN '0'
> ELSE
> CAST(EXTRACT(year FROM ( SELECT C_START_DATE FROM T_TIME_CHUNK WHERE
> T_TIME_CHUNK.C_UID = C_TIME_CHUNK_UID )) AS VARCHAR(4)) ||
> CASE
> WHEN EXTRACT(month FROM ( SELECT C_START_DATE FROM T_TIME_CHUNK WHERE
> T_TIME_CHUNK.C_UID = C_TIME_CHUNK_UID ))<10 THEN '0'
> ELSE ''
> END ||
> CAST(EXTRACT(month FROM ( SELECT C_START_DATE FROM T_TIME_CHUNK WHERE
> T_TIME_CHUNK.C_UID = C_TIME_CHUNK_UID )) AS VARCHAR(2)) ||
> CASE
> WHEN EXTRACT(day FROM ( SELECT C_START_DATE FROM T_TIME_CHUNK WHERE
> T_TIME_CHUNK.C_UID = C_TIME_CHUNK_UID ))<10 THEN '0'
> ELSE ''
> END ||
> CAST(EXTRACT(day FROM ( SELECT C_START_DATE FROM T_TIME_CHUNK WHERE
> T_TIME_CHUNK.C_UID = C_TIME_CHUNK_UID )) AS VARCHAR(2))
> END ||
> '-' ||
> CAST(C_AUCTION_SEQ as VARCHAR(18))
> ===
>
> Thanks and all the best,
> OC
>
>
>
>
>
>
>
>
>
>
> From: Samuel Pelletier <sam...@samkar.com>
> Subject: Re: FrontBase SQL: date formatting, „local variable“?
> Date: March 11, 2016 at 7:11:56 PM EST
> To: OC <o...@ocs.cz>
> Cc: WebObjects Development <webobjects-dev@lists.apple.com>
>
>
> Hi OC,
>
> The easiest way to get a date a YYYYMMDD is this:
>
> CAST(EXTRACT(year FROM "dateColumn") * 10000 + EXTRACT(month FROM
> "dateColumn") * 100 + EXTRACT(day FROM "dateColumn") as CHAR(8))
>
>
> A more flexible way is to create a function (you can adjust the format):
>
> create function formatDateOC(IN :"date" Timestamp) returns VARCHAR(20)
> begin
> declare "month" VARCHAR(20);
> declare "day" VARCHAR(20);
>
> set :"month" = CAST(EXTRACT(month FROM :"date") as VARCHAR(20));
> set :"day" = CAST(EXTRACT(day FROM :"date") as VARCHAR(20));
>
> if (char_length(:"month") = 1) then set :"month" = '0' || :"month"; END IF;
> if (char_length(:"day") = 1) then set :"day" = '0' || :"month"; END IF;
>
> return CAST(EXTRACT(year FROM :"date") as VARCHAR(4)) || :"month" || :"day";
> end
>
> To test it: values formatDateOC(cast(current_timestamp as timestamp))
>
> Replace the argument by any timestamp value or column name that contains a
> timestamp.
>
> Samuel
>
>
>> Le 11 mars 2016 à 17:57, OC <o...@ocs.cz> a écrit :
>>
>> Well...
>>
>> On 11. 3. 2016, at 22:06, OC <o...@ocs.cz> wrote:
>>
>>> Still, it can be solvable (perhaps even easily)
>>>
>>> (a) in standard SQL92 which I don't know well enough
>>> (b) in some FrontBase-specific extension
>>>
>>> which is why I am asking.
>>
>> ... without better knowledge the SQL used to derive the attribute looks like
>> this. Yes, it is _terribly_ ugly, but I can't see how to simplify it whilst
>> keeping the functionality. I would appreaciate if anybody sees and advices
>> how to simplify this brute, especially if there's a way to get rid of the
>> repeated nested SELECTs:
>>
>> ===
>> CASE
>> WHEN ( SELECT C_SHORTCUT FROM T_MARKET WHERE T_MARKET.C_UID = C_MARKET_ID )
>> IS NULL
>> THEN
>> 'Market(' ||
>> CAST(C_MARKET_ID as VARCHAR(18)) ||
>> ')'
>> ELSE ( SELECT C_SHORTCUT FROM T_MARKET WHERE T_MARKET.C_UID = C_MARKET_ID )
>> END ||
>> '-' ||
>> CASE
>> WHEN ( SELECT C_START_DATE FROM T_TIME_CHUNK WHERE T_TIME_CHUNK.C_UID =
>> C_TIME_CHUNK_UID ) IS NULL THEN '0'
>> ELSE
>> CAST(EXTRACT(year FROM ( SELECT C_START_DATE FROM T_TIME_CHUNK WHERE
>> T_TIME_CHUNK.C_UID = C_TIME_CHUNK_UID )) AS VARCHAR(4)) ||
>> CASE
>> WHEN EXTRACT(month FROM ( SELECT C_START_DATE FROM T_TIME_CHUNK WHERE
>> T_TIME_CHUNK.C_UID = C_TIME_CHUNK_UID ))<10 THEN '0'
>> ELSE ''
>> END ||
>> CAST(EXTRACT(month FROM ( SELECT C_START_DATE FROM T_TIME_CHUNK WHERE
>> T_TIME_CHUNK.C_UID = C_TIME_CHUNK_UID )) AS VARCHAR(2)) ||
>> CASE
>> WHEN EXTRACT(day FROM ( SELECT C_START_DATE FROM T_TIME_CHUNK WHERE
>> T_TIME_CHUNK.C_UID = C_TIME_CHUNK_UID ))<10 THEN '0'
>> ELSE ''
>> END ||
>> CAST(EXTRACT(day FROM ( SELECT C_START_DATE FROM T_TIME_CHUNK WHERE
>> T_TIME_CHUNK.C_UID = C_TIME_CHUNK_UID )) AS VARCHAR(2))
>> END ||
>> '-' ||
>> CAST(C_AUCTION_SEQ as VARCHAR(18))
>> ===
>>
>> Thanks and all the best,
>> OC
>>
>>
>> _______________________________________________
>> Do not post admin requests to the list. They will be ignored.
>> Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
>> Help/Unsubscribe/Update your Subscription:
>> https://lists.apple.com/mailman/options/webobjects-dev/samuel%40samkar.com
>>
>> This email sent to sam...@samkar.com
>
>
>
>
>
>
>
>
>
>
> From: OC <o...@ocs.cz>
> Subject: derived attributes in qualifier?
> Date: March 11, 2016 at 7:23:21 PM EST
> To: WebObjects Development <webobjects-dev@lists.apple.com>
>
>
> Is it normal that one can't use derived attributes in qualifier (when
> fetching), or am I doing something wrong in my model? It seems to me that
> since they are defined SQL-side, they should be able to limit the fetch,
> should they not?
>
> Anyway whenever I try to fetch with a qualifier which contains a derived
> attribute, I get an exception like this:
>
> ===
> Caused by: java.lang.NullPointerException
> at
> com.webobjects.jdbcadaptor.FrontbasePlugIn.internalTypeForExternal(FrontbasePlugIn.java:477)
> at
> com.webobjects.jdbcadaptor.FrontbasePlugIn$FrontbaseExpression.formatValueForAttribute(FrontbasePlugIn.java:1397)
> at
> com.webobjects.eoaccess.EOSQLExpression.sqlStringForValue(EOSQLExpression.java:1970)
> at
> com.webobjects.jdbcadaptor.FrontbasePlugIn$FrontbaseExpression.sqlStringForKeyValueQualifier(FrontbasePlugIn.java:1372)
> at
> com.webobjects.eoaccess.EOQualifierSQLGeneration$_KeyValueQualifierSupport.sqlStringForSQLExpression(EOQualifierSQLGeneration.java:463)
> at
> er.extensions.ERXExtensions$KeyValueQualifierSQLGenerationSupport.sqlStringForSQLExpression(ERXExtensions.java:339)
> at
> com.webobjects.eoaccess.EOQualifierSQLGeneration$Support._sqlStringForSQLExpression(EOQualifierSQLGeneration.java:165)
> at
> com.webobjects.eoaccess.EOSQLExpression.prepareSelectExpressionWithAttributes(EOSQLExpression.java:997)
> at
> com.webobjects.jdbcadaptor.JDBCExpression.prepareSelectExpressionWithAttributes(JDBCExpression.java:146)
> at
> com.webobjects.eoaccess.EOSQLExpressionFactory.selectStatementForAttributes(EOSQLExpressionFactory.java:225)
> at
> com.webobjects.jdbcadaptor.JDBCChannel.selectAttributes(JDBCChannel.java:213)
> at
> com.webobjects.eoaccess.EODatabaseContext._fetchRawRowKeyPaths(EODatabaseContext.java:2968)
> at
> com.webobjects.eoaccess.EODatabaseContext._objectsWithFetchSpecificationEditingContext(EODatabaseContext.java:3036)
> at
> com.webobjects.eoaccess.EODatabaseContext.objectsWithFetchSpecification(EODatabaseContext.java:3195)
> at
> com.webobjects.eocontrol.EOObjectStoreCoordinator.objectsWithFetchSpecification(EOObjectStoreCoordinator.java:488)
> at
> com.webobjects.eocontrol.EOEditingContext.objectsWithFetchSpecification(EOEditingContext.java:4069)
> at
> er.extensions.eof.ERXEC.objectsWithFetchSpecification(ERXEC.java:1308)
> ...
> ===
>
> Thanks a lot,
> OC
>
>
>
>
>
>
>
>
>
>
> From: Patrick Middleton <pmiddle...@onestep.co.uk>
> Subject: WebObjects and Apache 2.4 on El Capitan
> Date: March 12, 2016 at 12:14:19 PM EST
> To: WebObjects Development <webobjects-dev@lists.apple.com>
>
>
> Hi folks,
>
> mod_WebObjects? Since people are still discussing how to build the Apache
> adaptors on contemporary OSX, maybe I should add some more commentary.
>
> If you need an Apache module that isn't supplied prebuilt for you, either as
> part of Apache or as part of what your vendor supplies, always be ready to
> build your own. Never go looking to download what someone else has already
> built. It's too likely that the pre-built one won't work for you.
>
> If you compile something in Java ... you get bytecode, and the class format
> is defined. Your bytecode can be expected to run in somebody else's JVM.
> Also, if your code subclassed a class, and the class you subclassed is
> somehow different, has more member fields, it will all still work just fine.
>
> In C, by comparison ... Apache is implemented in C. It is structures and
> pointers and offsets. How big are short, int and long? How does the
> compiler align these to word boundaries? The size of a structure can be very
> different if compiled with different compilers or different compilation
> options, especially compilation options to do with optimization, even if the
> C source code declaring the structure is the same. The compiler knows the
> addresses of fields within a structure by computing offsets from the
> structure's address; this can cause something in Objective-C 1 called
> 'fragile base class syndrome' where adding an instance variable to a class
> breks all subclasses of that class that have instance variables.... so, avoid
> all this. Build your own!
>
> Apache provides a way round these problems. Apache modules are supposed to
> be built via a tool /usr/sbin/apxs ["APache eXtension System"] which is a
> /usr/bin/perl script to compile and link your source code using the same
> compiler options as was used to build Apache.
>
> From memory, we can start by downloading some ProjectWonder source code, and
> this includes source code for mod_WebObjects, and someone's already added a
> makefile for building for Apache 2.4 (it's more or less the same as building
> for Apache 2.2). I tried this on OSX 10.11 ('El Capitan') mostly to see how
> it would fail.
>
> Firstly, the build fails because the Makefiles specify building the FastCGI
> adaptor and by defautl we're missing some headers. We can fix that in
> make.config by setting
> ADAPTORS = CGI Apache2.4
>
> Secondly, Apache 2.4 was built for El Capitan by cross-compilation on
> Yosemite (10.10). There are some references to this which we need to deal
> with. Reading /usr/sbin/apxs (on OSX) we find it has a variable
> installbuilddir = /usr/share/httpd/build and some configuration variables in
> a file config_vars.mk in that directory.
>
> There are references to
> /Applications/Xcode.app/Contents/Developer/Toolchains/OSX10.11.xctoolchain ,
> you might have that if you are on Yosemite and have the El Capitan SDK
> installed. There are references to
> /Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/MacOSX10.11.Internal.sdk
> . We fix these by making softlinks that refere to what we do have. For me,
> this was (as root):
>
> cd /Applications/Xcode.app/Contents/Developer/Toolchains
> pwd
> ls -l
> ln -s XcodeDefault.xctoolchain OSX10.11.xctoolchain
> ls -l
> cd
> /Applications/Xcode.app/Contents/Developer/Platforms/MacOSX.platform/Developer/SDKs/
> pwd
> ls -l
> ln -s MacOSX10.11.sdk MacOSX10.11.Internal.sdk
> ls -l
>
> I also encountered an error about not being able to find httpd.h. There's
> this in make.config:
> APACHEINCLUDE_DIR = /usr/include/apache2
>
> which doesn't work on modern OSX because Apple appears to have decided not to
> do /usr/include any more. We need to use the platform SDKs. I added some
> stuff to make.config to work out which release of OSX the build was happening
> on, what the SDK path should be, and using the SDK path when setting
> APACHEINCLUDE_DIR.
>
> Next up: there was a linker flag "-macosx_version_min 10.5" which we don't
> want on OSX 10.8 and later. I changed this to be a variable which is empty
> unless on 10.4, 10.5, 10.6 or 10.7.
>
> And that's enough to get the Adaptors project to compile and link. I've
> provided both a patch file of my changes, and a .tar.gz file of the files I
> changed, which some may find easier to read. Submitting a patch file to this
> list is not the approved way of proposing changes to Project Wonder, but I'm
> not expecting my changes to make it in anyway.
>
> [There is another gotcha which no longer applies to building on OSX.
> Nowadays, whatever Apple-supplied C or C++ compiler you ask for, you'll get a
> compiler based on LLVM, which outputs compact omtimised code and ignores
> optimisation flags. The makefiles for the mod_WebObjects adaptors refer to
> gcc and -O2. If somebody built Apache using -Os (and Apple used to do that)
> then there is a risk of structure size mismatch problems.]
>
> -- Patrick
>
>
> <changed.tar.gz><patch.txt>
>
>
>
> _______________________________________________
> Webobjects-dev mailing list
> Webobjects-dev@lists.apple.com
> https://lists.apple.com/mailman/listinfo/webobjects-dev
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
https://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to arch...@mail-archive.com
