<div dir="ltr"><br><div class="gmail_extra"><br><div class="gmail_quote">On Mon, Sep 15, 2014 at 4:35 PM, Peter Rabbitson <span dir="ltr"><<a href="mailto:rabbit+dbic@rabbit.us" target="_blank">rabbit+dbic@rabbit.us</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><span>On 09/15/2014 04:02 PM, Lasse Makholm wrote:<br>
</span><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><span>
<br>
<br>
On Fri, Sep 12, 2014 at 6:25 AM, Peter Rabbitson <<a href="mailto:rabbit%2Bdbic@rabbit.us" target="_blank">rabbit+dbic@rabbit.us</a><br></span><span>
<mailto:<a href="mailto:rabbit%2Bdbic@rabbit.us" target="_blank">rabbit+dbic@rabbit.us</a>><u></u>> wrote:<br>
<br>
On 09/11/2014 11:39 PM, Lasse Makholm wrote:<br>
<br>
<br>
<br>
On Thu, Sep 11, 2014 at 7:58 PM, Peter Rabbitson<br></span>
<<a href="mailto:rabbit%2Bdbic@rabbit.us" target="_blank">rabbit+dbic@rabbit.us</a> <mailto:<a href="mailto:rabbit%252Bdbic@rabbit.us" target="_blank">rabbit%2Bdbic@rabbit.<u></u>us</a>><br>
<mailto:<a href="mailto:rabbit%2Bdbic@rabbit.us" target="_blank">rabbit+dbic@rabbit.us</a><div><div><br>
<mailto:<a href="mailto:rabbit%252Bdbic@rabbit.us" target="_blank">rabbit%2Bdbic@rabbit.<u></u>us</a>>>__> wrote:<br>
<br>
On 09/11/2014 05:48 PM, Lasse Makholm wrote:<br>
<br>
Hi,<br>
<br>
We're porting a DBIx::Class application from MySQL to<br>
Oracle,<br>
containing<br>
some insert statements with no column specifications by<br>
way of<br>
$rs->create({}). This doesn't seem to work on Oracle.<br>
<br>
DBIx::Class::SQLMaker generates "INSERT INTO table<br>
DEFAULT VALUES<br>
RETURNING id INTO ?" which, as far as I can figure is<br>
not valid<br>
syntax<br>
on Oracle. Neither the is the other common variant for such<br>
statements;<br>
"INSERT INTO table () VALUES ()".<br>
<br>
<br>
This is clearly a bug that we've never tested for<br>
previously. Can<br>
you please confirm that the following works on your Oracle<br>
RDBMS<br>
(yes, the first ()pair is gone):<br>
<br>
INSERT INTO $tablename VALUES(DEFAULT);<br>
<br>
<br>
That only works for a table with only one column. For more,<br>
you'd need<br>
to repeat DEFAULT for each column. Otherwise you get: "SQL Error:<br>
ORA-00947: not enough values".<br>
<br>
<br>
I see.<br>
<br>
<br>
I wonder how/if an explicit DEFAULT interacts with the typical<br>
scenario<br>
of having a before insert trigger pulling IDs from a sequence to<br>
implement auto increment columns...<br>
<br>
I guess, in a sense, that question boils down whether there's any<br>
difference between:<br>
<br>
INSERT INTO t (a, b) VALUES (DEFAULT, 42)<br>
<br>
and:<br>
<br>
INSERT INTO t (b) VALUES (42)<br>
<br>
In any case, I don't know the answer but I suspect we have some<br>
resident<br>
Oracle experts who do... I'll discuss it with them and report<br>
back...<br>
<br>
<br>
Yes please do. What we are looking for basically is the most<br>
"universal" statement we could come up with. Putting it in is just a<br>
SMOP after that.<br>
<br>
<br>
So, to the best of my knowledge and that of those I've talked to, there<br>
is no way insert a row in Oracle without listing at least one column.<br>
<br>
That leaves picking an arbitrary list of columns and supplying the<br>
DEFAULT keyword as value.<br>
<br>
I guess that leaves the question of how many and which columns to<br>
specify in the insert statement. Choosing one arbitrary column seems<br>
weird but including all columns seems overly redundant/wasteful. I'm not<br>
sure which solution is less ugly...<br>
</div></div></blockquote>
<br>
In this case we will be using the first column as declared in add_columns on the result source (they are stored positionally).<br>
<br>
This will have to wait until after the current release (see next email), as the thing never worked in the first place. If you have the tuits to write a proto implementation and chuck it either to the ML or in a github PR - please do!</blockquote><div><br></div><div>First stab:</div><div><a href="https://github.com/dbsrgits/dbix-class/pull/61">https://github.com/dbsrgits/dbix-class/pull/61</a><br></div><div><br></div><div>I initially thought I could fix it in SQLMaker but quickly realised it doesn't know about result sources and thus can't know which column to add to the statement.<br></div><div><br></div><div>Instead, overriding insert() in DBIx::Class::Storage::DBI::Oracle::Generic seems to work nicely.</div><div><br></div><div>I also tweaked t/60core.t to insert a row with no columns..</div><div><br></div><div>$source->create({}) would, ideally, be tested on all storage types, I guess...?<br></div><div><br></div><div>Also, which branch is preferred for submitting new pull requests against? Is it documented somewhere?</div><div><br></div><div>/L</div><div><br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><div><div>Cheers<br>
<br>
______________________________<u></u>_________________<br>
List: <a href="http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class" target="_blank">http://lists.scsys.co.uk/cgi-<u></u>bin/mailman/listinfo/dbix-<u></u>class</a><br>
IRC: <a href="http://irc.perl.org#dbix-class" target="_blank">irc.perl.org#dbix-class</a><br>
SVN: <a href="http://dev.catalyst.perl.org/repos/bast/DBIx-Class/" target="_blank">http://dev.catalyst.perl.org/<u></u>repos/bast/DBIx-Class/</a><br>
Searchable Archive: <a href="http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk" target="_blank">http://www.grokbase.com/group/<u></u>dbix-class@lists.scsys.co.uk</a><br>
</div></div></blockquote></div><br></div></div>