You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@cocoon.apache.org by Sheraz Sharif <sh...@m3b.net> on 2003/02/07 12:17:15 UTC
DatabaseAddAction and unique or primary keys
Hello all,
I have been working on a website for a while. Here is my problem - I
can easily verify user input through forms and return any error messages
through the xsp-formval tags. After validation, the input is passed to
the database. However, I get an SQL exception :
ProcessingException: Could not add record: java.sql.SQLException: ERROR:
Cannot insert a duplicate key into unique index category_name_key
This is happening because I am attempting to insert a value into a key
column where that value already exists. I am looking for a solution
where cocoon will connect to the database and attempt to retreive the
key value before the insert. If it does not exist, it will insert it,
otherwise it will fail and I can notify the user of the error. Have I
been verbose enough? If no solution exists, I am thinking I can either
extend or rewrite the DatabaseAddAction class to do this. Or should I
abandon logicsheets all together and move to xforms? Thanks.
Sheraz
--
sheraz@m3b.net
m3b Consulting
www.m3b.net
832.573.0937
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faq/index.html>
To unsubscribe, e-mail: <co...@xml.apache.org>
For additional commands, e-mail: <co...@xml.apache.org>
Re: [SUMMARY] DatabaseAddAction and unique or primary keys
Posted by Sheraz Sharif <li...@m3b.net>.
Sorry the message was double posted..but good news!
Antoino, thanks for pushing me in that direction. I did not think the
modular DatabaseAddAction would handle unique keys, but it does (I had
to hunt through the source to find this out) I updated my descriptor
files to be compatible with modular DB actions. This meant moving
unique keys out of the <key> tags and into the <value> tags. Duplicate
keys are handled gracefully. My next goal is to return a notification
to the xsp page telling the user about the duplicate. But I will save
that for another thread. Cheers to all for their help!
Sheraz
On Fri, 2003-02-07 at 18:17, Antonio Gallardo wrote:
> Hi,
>
> Today morning I told you about the MODULAR Database Actions, also I
> pointed you to:
>
> http://wiki.cocoondev.org/Wiki.jsp?page=ModularDatabaseActions
>
> There you can find ALL the info about how to do what you mean. Also I can
> work with sequence (you told before you use PostgreSQL 7.2).
>
> Sheraz Sharif dijo:
> > Hello all,
> >
> > I have been working on a website for a while. Here is my problem - I
> > can easily verify user input through forms and return any error messages
> > through the xsp-formval tags. After validation, the input is passed to
> > the database. However, I get an SQL exception :
> >
> > ProcessingException: Could not add record: java.sql.SQLException: ERROR:
> > Cannot insert a duplicate key into unique index category_name_key
> >
> > This is happening because I am attempting to insert a value into a key
> > column where that value already exists. I am looking for a solution
> > where cocoon will connect to the database and attempt to retreive the
> > key value before the insert. If it does not exist, it will insert it,
> > otherwise it will fail and I can notify the user of the error.
>
> I think you can do a select before and check for the result of the select.
> Sorry, I use XSP, but maybe the example will work:
>
> <esql:execute-query>
> <esql:query>
> SELECT the_key
> FROM the_table
> WHERE the_key=<esql:parameter type="int"><xsp-request:get-parameter
> name="mykey_id" default=""/></esql:parameter>
> </esql:query>
> <esql:no-results>
> <!-- The key does not exist, we are going to insert a new record. -->
> <esql:execute-query>
> <esql:query>
> INSERT INTO the_table(the_key, the_data1, the_data2) ......
> </esql:query>
> <esql:results/>
> </esql:execute-query>
> <esql:no-results>
> <esql:results>
> <!-- Here the_key value exist! ==> SHOW YOUR ERROR AS YOU WANT! -->
> </esql:results>
> </esql:execute-query>
>
> Best Regards,
>
> Antonio Gallardo
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faq/index.html>
To unsubscribe, e-mail: <co...@xml.apache.org>
For additional commands, e-mail: <co...@xml.apache.org>
Re: DatabaseAddAction and unique or primary keys
Posted by Antonio Gallardo <ag...@agsoftware.dnsalias.com>.
Hi,
Today morning I told you about the MODULAR Database Actions, also I
pointed you to:
http://wiki.cocoondev.org/Wiki.jsp?page=ModularDatabaseActions
There you can find ALL the info about how to do what you mean. Also I can
work with sequence (you told before you use PostgreSQL 7.2).
Sheraz Sharif dijo:
> Hello all,
>
> I have been working on a website for a while. Here is my problem - I
> can easily verify user input through forms and return any error messages
> through the xsp-formval tags. After validation, the input is passed to
> the database. However, I get an SQL exception :
>
> ProcessingException: Could not add record: java.sql.SQLException: ERROR:
> Cannot insert a duplicate key into unique index category_name_key
>
> This is happening because I am attempting to insert a value into a key
> column where that value already exists. I am looking for a solution
> where cocoon will connect to the database and attempt to retreive the
> key value before the insert. If it does not exist, it will insert it,
> otherwise it will fail and I can notify the user of the error.
I think you can do a select before and check for the result of the select.
Sorry, I use XSP, but maybe the example will work:
<esql:execute-query>
<esql:query>
SELECT the_key
FROM the_table
WHERE the_key=<esql:parameter type="int"><xsp-request:get-parameter
name="mykey_id" default=""/></esql:parameter>
</esql:query>
<esql:no-results>
<!-- The key does not exist, we are going to insert a new record. -->
<esql:execute-query>
<esql:query>
INSERT INTO the_table(the_key, the_data1, the_data2) ......
</esql:query>
<esql:results/>
</esql:execute-query>
<esql:no-results>
<esql:results>
<!-- Here the_key value exist! ==> SHOW YOUR ERROR AS YOU WANT! -->
</esql:results>
</esql:execute-query>
Best Regards,
Antonio Gallardo
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faq/index.html>
To unsubscribe, e-mail: <co...@xml.apache.org>
For additional commands, e-mail: <co...@xml.apache.org>