0

I am trying to save data from my XPage to MySQL database. I have established a connection and it works (which is demonstrated by the fact that my dynamic view panel is able to fetch data in MySQL tables). But I am unable to submit my data to MySQL. I am following the example database of XPagesJDBC.nsf in extension library.

MySQL connection file mysqlconn.jdbc:

<jdbc>
    <driver>com.mysql.jdbc.Driver</driver>
    <url>jdbc:mysql://***.***.***.***:3306/project_management</url>
    <user>adminuser</user>
    <password>******</password>
</jdbc>

In my XPage I create a data source with xe:jdbcRowSet

<xp:this.data>
    <xe:jdbcRowSet var="jdbcrsProjectMgmt" connectionName="mysqlconn"
        sqlTable="project_details">
    </xe:jdbcRowSet>
</xp:this.data>

Then in my xp:panel I use the data source to save the object. I have two fields of name and description mapped to respective columns in tables in MySQL database.

<xp:panel>
    <xp:this.data>
        <xe:objectData var="row"
            saveObject="#{javascript:jdbcrsProjectMgmt.saveRow(row);}">
            <xe:this.createObject><![CDATA[#{javascript:var id = context.getUrlParameter("id");
if (id != null && !id.trim().equals("")) {
    jdbcrsProjectMgmt.getRow(parseInt(id) - 1);
} else {
    jdbcrsProjectMgmt.newRow(0);
}}]]></xe:this.createObject>
        </xe:objectData>
    </xp:this.data>
    <xp:table border="0" cellpadding="5" cellspacing="0">
        <xp:tr>
            <xp:td>Name</xp:td>
            <xp:td>
                <xp:inputText id="txtName" value="#{row.name}"></xp:inputText>
            </xp:td>
        </xp:tr>
        <xp:tr>
            <xp:td>Description</xp:td>
            <xp:td>
                <xp:inputText id="txtDescription"
                    value="#{row.description}">
                </xp:inputText>
            </xp:td>
        </xp:tr>
        <xp:tr>
            <xp:td colspan="2" align="center">
                <xp:button value="Save" id="btnSave">
                    <xp:eventHandler event="onclick" submit="true"
                        refreshMode="complete" immediate="false" save="false">
                        <xp:this.action>
                            <xp:actionGroup>
                                <xp:saveDocument var="row"></xp:saveDocument>
                                <xp:openPage
                                    name="/viewProject.xsp">
                                </xp:openPage>
                            </xp:actionGroup>
                        </xp:this.action>
                    </xp:eventHandler>
                </xp:button>
            </xp:td>
        </xp:tr>
    </xp:table>
</xp:panel>

When I try to click on Save button data is not saved and no error is shown. But if I change the Button type to Submit (<xp:eventHandler ..... save="true">) it gives me error:

Error saving data source jdbcrsProjectMgmt
javax.sql.rowset.spi.SyncProviderException: Can't call commit when autocommit=true
Can't call commit when autocommit=true

But this time strangely the data gets saved. I can't find this autocommit property and don't know where to set it. How can I solve this? Is this issue with XPages or MySQL?

Naveen
  • 6,786
  • 10
  • 37
  • 85
  • I haven't used the extLib controls for JDBC, but have done this in java. The autocommit defaults to true, and is a property of the connection. See if you can set it to false when you make the connection ("mysqlconn"). Then try using a submit button and see if that works. – Steve Zavocki Mar 28 '13 at 18:00
  • 1
    @SteveZavocki: I set the `autocommit` to `false` in connection URL but it didn't work. I set it in the file mysqlconn.jdbc as `jdbc:mysql://***.***.***.***:3306/project_management?autocommit=false` – Naveen Mar 29 '13 at 11:01
  • Did it give the same message about autocommit=true? When you changed the button to submit, did you leave the save document in there, maybe it is now trying to save it twice, and the second causes a locking exception from mySQL. If so, try just submitting, and not saving and see what happens. – Steve Zavocki Mar 29 '13 at 13:14
  • Yes it gives the same error. There is no Save document on button instead there is save data source of `row` (id of `xe:objectData`). – Naveen Apr 02 '13 at 17:06
  • The reason I said not to save is that I googled the exception and seems to indicate that the exception is caused by a locking error. I am just guessing that the submit triggers a saving of the data source that didn't happen when the button wasn't type=submit. Just for fun try not saving the data source and see what happens. I would be surprised if that works but it worth a try. – Steve Zavocki Apr 03 '13 at 13:53
  • If that doesn't work, I would look into your mySQL locking settings for the database. – Steve Zavocki Apr 03 '13 at 13:54
  • @SteveZavocki: Didn't work! :( – Naveen Apr 04 '13 at 14:11
  • Bummer. I am running out of guesses, but here is one more. In your button, change save="true" and try that with submit="true" and submit="false". – Steve Zavocki Apr 04 '13 at 15:21

0 Answers0