| Author |
Message |
|
| ketralnis |
 Posted: Tue Dec 04, 2007 3:38 am |
 |
|
|
User
Joined: 20 Jul 2007
Posts: 151
Location: San Francisco, CA
|
I have a Postgres table with a timestamp column, like this:
created_on | timestamp without time zone | not null default now()
If I create an instance of this model and let Postgres fill in that
column, like this:
my_model:save(my_model:new_with([{name,"foo"}])
Then it saves, I can look it up with my_model:find_id(Id), etc.
However, if I try to specify the created_on column, like this:
my_model:save(my_model:new_with([{name,"foo"},
{created_on,calendar:universal_time()}])
then erlydb_psql returns an error that looks like this:
{sql_error,
"42804",
"RupdateTargetListEntry L361 Fparse_target.c HYou will need to
rewrite or cast the expression. Mcolumn \"created_on\" is of type
timestamp without time zone but expression is of type bigint"}}
(note that it does this whether I use the format {{_,_,_},{_,_,_}} or
{datetime,{{_,_,_},{_,_,_}}}) This is strange, because
erlydb_field:get_erl_type/1 says:
'timestamp without time zone' -> datetime;
And the docs for erlydb_field:erlydb_type/1 says:
%% @doc Get the field's corresponding Erlang type. Possible values are
%% 'binary', 'integer', 'float', 'date', 'time', and 'datetime'.
%%
%% Date, time and datetime fields have the following forms:
%%
%% ```
%% {date, {Year, Month, Day}}
%% {time, {Hour, Minute, Second}}
%% {datetime, {{Year, Month, Day}, {Hour, Minute, Second}}}
Any ideas on why I can't specify this column?
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "erlyweb" group.
To post to this group, send email to erlyweb@googlegroups.com
To unsubscribe from this group, send email to erlyweb-unsubscribe@googlegroups.com
For more options, visit this group at http://groups.google.com/group/erlyweb?hl=en
-~----------~----~----~----~------~----~------~--~---
Post recived from mailinglist |
|
|
| Back to top |
  
|
| Guest |
| Posted: Wed Dec 05, 2007 5:01 am |
|
|
|
Guest
|
It looks like the ErlSQL serialization that works for MySQL doesn't
work for Postgres. Check out erlsql.erl. It may be required to pass it
an extra 'dialect' parameter that it will use to determine the right
serialization.
Yariv
On Dec 3, 2007 7:37 PM, David King <dking@ketralnis.com> wrote:
>
> I have a Postgres table with a timestamp column, like this:
>
> created_on | timestamp without time zone | not null default now()
>
> If I create an instance of this model and let Postgres fill in that
> column, like this:
>
> my_model:save(my_model:new_with([{name,"foo"}])
>
> Then it saves, I can look it up with my_model:find_id(Id), etc.
>
> However, if I try to specify the created_on column, like this:
>
> my_model:save(my_model:new_with([{name,"foo"},
>
> {created_on,calendar:universal_time()}])
>
> then erlydb_psql returns an error that looks like this:
>
> {sql_error,
> "42804",
> "RupdateTargetListEntry L361 Fparse_target.c HYou will need to
> rewrite or cast the expression. Mcolumn \"created_on\" is of type
> timestamp without time zone but expression is of type bigint"}}
>
> (note that it does this whether I use the format {{_,_,_},{_,_,_}} or
> {datetime,{{_,_,_},{_,_,_}}}) This is strange, because
> erlydb_field:get_erl_type/1 says:
>
> 'timestamp without time zone' -> datetime;
>
> And the docs for erlydb_field:erlydb_type/1 says:
>
> %% @doc Get the field's corresponding Erlang type. Possible values are
> %% 'binary', 'integer', 'float', 'date', 'time', and 'datetime'.
> %%
> %% Date, time and datetime fields have the following forms:
> %%
> %% ```
> %% {date, {Year, Month, Day}}
> %% {time, {Hour, Minute, Second}}
> %% {datetime, {{Year, Month, Day}, {Hour, Minute, Second}}}
>
> Any ideas on why I can't specify this column?
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "erlyweb" group.
To post to this group, send email to erlyweb@googlegroups.com
To unsubscribe from this group, send email to erlyweb-unsubscribe@googlegroups.com
For more options, visit this group at http://groups.google.com/group/erlyweb?hl=en
-~----------~----~----~----~------~----~------~--~---
Post recived from mailinglist |
|
|
| Back to top |
|
| ketralnis |
| Posted: Fri Dec 07, 2007 2:22 am |
|
|
|
User
Joined: 20 Jul 2007
Posts: 151
Location: San Francisco, CA
|
> It looks like the ErlSQL serialization that works for MySQL doesn't
> work for Postgres. Check out erlsql.erl. It may be required to pass it
> an extra 'dialect' parameter that it will use to determine the right
> serialization.
Here's the SQL that's generated:
erlydb_psql:193 INSERT INTO my_model(created_on,name) VALUES
(20071122142510,'foo');
I am fascinated and horrified that MySQL parses 20071122142510 as a
timestamp.
Do you know of an easy fix off of the top of your head, or should I
dive in?
> On Dec 3, 2007 7:37 PM, David King <dking@ketralnis.com> wrote:
>>
>> I have a Postgres table with a timestamp column, like this:
>>
>> created_on | timestamp without time zone | not null default now()
>>
>> If I create an instance of this model and let Postgres fill in that
>> column, like this:
>>
>> my_model:save(my_model:new_with([{name,"foo"}])
>>
>> Then it saves, I can look it up with my_model:find_id(Id), etc.
>>
>> However, if I try to specify the created_on column, like this:
>>
>> my_model:save(my_model:new_with([{name,"foo"},
>>
>> {created_on,calendar:universal_time()}])
>>
>> then erlydb_psql returns an error that looks like this:
>>
>> {sql_error,
>> "42804",
>> "RupdateTargetListEntry L361 Fparse_target.c HYou will need to
>> rewrite or cast the expression. Mcolumn \"created_on\" is of type
>> timestamp without time zone but expression is of type bigint"}}
>>
>> (note that it does this whether I use the format {{_,_,_},{_,_,_}} or
>> {datetime,{{_,_,_},{_,_,_}}}) This is strange, because
>> erlydb_field:get_erl_type/1 says:
>>
>> 'timestamp without time zone' -> datetime;
>>
>> And the docs for erlydb_field:erlydb_type/1 says:
>>
>> %% @doc Get the field's corresponding Erlang type. Possible values
>> are
>> %% 'binary', 'integer', 'float', 'date', 'time', and 'datetime'.
>> %%
>> %% Date, time and datetime fields have the following forms:
>> %%
>> %% ```
>> %% {date, {Year, Month, Day}}
>> %% {time, {Hour, Minute, Second}}
>> %% {datetime, {{Year, Month, Day}, {Hour, Minute, Second}}}
>>
>> Any ideas on why I can't specify this column?
>>
>>>
>>
>
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "erlyweb" group.
To post to this group, send email to erlyweb@googlegroups.com
To unsubscribe from this group, send email to erlyweb-unsubscribe@googlegroups.com
For more options, visit this group at http://groups.google.com/group/erlyweb?hl=en
-~----------~----~----~----~------~----~------~--~---
Post recived from mailinglist |
|
|
| Back to top |
|
| ketralnis |
| Posted: Fri Dec 07, 2007 3:40 am |
|
|
|
User
Joined: 20 Jul 2007
Posts: 151
Location: San Francisco, CA
|
>> It looks like the ErlSQL serialization that works for MySQL doesn't
>> work for Postgres. Check out erlsql.erl. It may be required to pass
>> it
>> an extra 'dialect' parameter that it will use to determine the right
>> serialization.
> Here's the SQL that's generated:
> erlydb_psql:193 INSERT INTO my_model(created_on,name) VALUES
> (20071122142510,'foo');
> I am fascinated and horrified that MySQL parses 20071122142510 as a
> timestamp.
> Do you know of an easy fix off of the top of your head, or should I
> dive in?
So I dove in, the fix is below. After the patch, the SQL looks like:
erlydb_psql:193 INSERT INTO my_model(created_on,name) VALUES
('2007-11-09 04:31:38','my_name')
According to <http://dev.mysql.com/doc/refman/5.0/en/datetime.html>
and <http://www.postgresql.org/docs/8.0/interactive/datatype-datetime.html
>, the time format should work on Postgres and MySQL, but can
somebody please test it on MySQL and make sure that it works there?
Index: src/erlsql/erlsql.erl
===================================================================
--- src/erlsql/erlsql.erl (revision 215)
+++ src/erlsql/erlsql.erl (working copy)
@@ -45,6 +45,8 @@
unsafe_sql/2,
encode/1]).
+-compile(export_all).
+
-define(L(Obj), io:format("LOG ~w ~p\n", [?LINE, Obj])).
%% @doc Generate an iolist (a tree of strings and/or binaries)
@@ -128,16 +130,19 @@
Res;
encode({datetime, Val}, AsBinary) ->
encode(Val, AsBinary);
-encode({{Year, Month, Day}, {Hour, Minute, Second}}, false) ->
- Res = two_digits([Year, Month, Day, Hour, Minute, Second]),
- lists:flatten(Res);
-encode({TimeType, Val}, AsBinary)
- when TimeType == 'date';
- TimeType == 'time' ->
- encode(Val, AsBinary);
-encode({Time1, Time2, Time3}, false) ->
- Res = two_digits([Time1, Time2, Time3]),
- lists:flatten(Res);
+encode({{Year,Month,Day}, {Hour,Minute,Second}}, false) ->
+ [Year1,Month1,Day1,Hour1,Minute1,Second1] =
+ lists:map(fun two_digits/1,[Year, Month, Day, Hour, Minute,
Second]),
+ lists:flatten(io_lib:format("'~s-~s-~s ~s:~s:~s'",
+
[Year1,Month1,Day1,Hour1,Minute1,Second1]));
+encode({date, {Year, Day, Month}}, false) ->
+ [Year1,Month1,Day1] =
+ lists:map(fun two_digits/1,[Year, Month, Day]),
+ lists:flatten(io_lib:format("'~s-~s-~s'",[Year1,Month1,Day1]));
+encode({time, {Hour, Minute, Second}}, false) ->
+ [Hour1,Minute1,Second1] =
+ lists:map(fun two_digits/1,[Hour, Minute, Second]),
+ lists:flatten(io_lib:format("'~s:~s:~s'",[Hour1,Minute1,Second1]));
encode(Val, _AsBinary) ->
{error, {unrecognized_value, {Val}}}.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "erlyweb" group.
To post to this group, send email to erlyweb@googlegroups.com
To unsubscribe from this group, send email to erlyweb-unsubscribe@googlegroups.com
For more options, visit this group at http://groups.google.com/group/erlyweb?hl=en
-~----------~----~----~----~------~----~------~--~---
Post recived from mailinglist |
|
|
| Back to top |
|
| Guest |
| Posted: Fri Dec 07, 2007 8:48 am |
|
|
|
Guest
|
Thanks... I'll test it and let you know if it works.
Yariv
On Dec 6, 2007 7:28 PM, David King <dking@ketralnis.com> wrote:
>
> >> It looks like the ErlSQL serialization that works for MySQL doesn't
> >> work for Postgres. Check out erlsql.erl. It may be required to pass
> >> it
> >> an extra 'dialect' parameter that it will use to determine the right
> >> serialization.
> > Here's the SQL that's generated:
> > erlydb_psql:193 INSERT INTO my_model(created_on,name) VALUES
> > (20071122142510,'foo');
> > I am fascinated and horrified that MySQL parses 20071122142510 as a
> > timestamp.
> > Do you know of an easy fix off of the top of your head, or should I
> > dive in?
>
> So I dove in, the fix is below. After the patch, the SQL looks like:
>
> erlydb_psql:193 INSERT INTO my_model(created_on,name) VALUES
> ('2007-11-09 04:31:38','my_name')
>
> According to <http://dev.mysql.com/doc/refman/5.0/en/datetime.html>
> and <http://www.postgresql.org/docs/8.0/interactive/datatype-datetime.html
> >, the time format should work on Postgres and MySQL, but can
> somebody please test it on MySQL and make sure that it works there?
>
>
>
>
>
> Index: src/erlsql/erlsql.erl
> ===================================================================
> --- src/erlsql/erlsql.erl (revision 215)
> +++ src/erlsql/erlsql.erl (working copy)
> @@ -45,6 +45,8 @@
> unsafe_sql/2,
> encode/1]).
>
> +-compile(export_all).
> +
> -define(L(Obj), io:format("LOG ~w ~p\n", [?LINE, Obj])).
>
> %% @doc Generate an iolist (a tree of strings and/or binaries)
> @@ -128,16 +130,19 @@
> Res;
> encode({datetime, Val}, AsBinary) ->
> encode(Val, AsBinary);
> -encode({{Year, Month, Day}, {Hour, Minute, Second}}, false) ->
> - Res = two_digits([Year, Month, Day, Hour, Minute, Second]),
> - lists:flatten(Res);
> -encode({TimeType, Val}, AsBinary)
> - when TimeType == 'date';
> - TimeType == 'time' ->
> - encode(Val, AsBinary);
> -encode({Time1, Time2, Time3}, false) ->
> - Res = two_digits([Time1, Time2, Time3]),
> - lists:flatten(Res);
> +encode({{Year,Month,Day}, {Hour,Minute,Second}}, false) ->
> + [Year1,Month1,Day1,Hour1,Minute1,Second1] =
> + lists:map(fun two_digits/1,[Year, Month, Day, Hour, Minute,
> Second]),
> + lists:flatten(io_lib:format("'~s-~s-~s ~s:~s:~s'",
> +
> [Year1,Month1,Day1,Hour1,Minute1,Second1]));
> +encode({date, {Year, Day, Month}}, false) ->
> + [Year1,Month1,Day1] =
> + lists:map(fun two_digits/1,[Year, Month, Day]),
> + lists:flatten(io_lib:format("'~s-~s-~s'",[Year1,Month1,Day1]));
> +encode({time, {Hour, Minute, Second}}, false) ->
> + [Hour1,Minute1,Second1] =
> + lists:map(fun two_digits/1,[Hour, Minute, Second]),
> + lists:flatten(io_lib:format("'~s:~s:~s'",[Hour1,Minute1,Second1]));
> encode(Val, _AsBinary) ->
> {error, {unrecognized_value, {Val}}}.
>
>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "erlyweb" group.
To post to this group, send email to erlyweb@googlegroups.com
To unsubscribe from this group, send email to erlyweb-unsubscribe@googlegroups.com
For more options, visit this group at http://groups.google.com/group/erlyweb?hl=en
-~----------~----~----~----~------~----~------~--~---
Post recived from mailinglist |
|
|
| Back to top |
|
|
|
All times are GMT
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot attach files in this forum
You cannot download files in this forum
|
|
|
Digg It
Del.icio.us
Reddit
Facebook
Stumble Upon
Technorati
