stored procedure 'auto_pk_for_table' not found

Question

I don't know why I received the error :

org.apache.cayenne.CayenneRuntimeException: [v.4.0.M5 Feb 24 2017 07:47:55] Commit Exception
[...]
Caused by: java.sql.SQLException: Procédure stockée 'auto_pk_for_table' introuvable.
[...]

I'm using Cayenne :

<dependency>
    <groupId>org.apache.cayenne</groupId>
    <artifactId>cayenne-server</artifactId>
    <version>4.0.M5</version>
</dependency>

and JDTS for sql server :

<dependency>
    <groupId>net.sourceforge.jtds</groupId>
    <artifactId>jtds</artifactId>
    <version>1.3.1</version>
</dependency>

The connexion is ok :

avr. 10, 2017 2:36:30 PM org.apache.cayenne.datasource.DriverDataSource getConnection
INFOS: +++ Connecting: SUCCESS.

I'm trying to create a new user (I'm starting by bascis!) so my code is : (I cut a little bit, it's too long:!)

public abstract class _UserInfo extends CayenneDataObject {

    public static final String ADDRESS_PROPERTY = "address";

    public void setAddress(String address) {
        writeProperty(ADDRESS_PROPERTY, address);
    }
    public String getAddress() {
        return (String)readProperty(ADDRESS_PROPERTY);
    }
}

public class UserInfo extends _UserInfo implements Serializable {
    private static final long serialVersionUID = 1L;

    public String address;

    public String getAdress() {
        return address;
    }

    public void setAddress(String address) {
        super.setAddress(address);
    }

//I have the hashcode and equals too
}

Then, I used vaadin to create my form :

public class UserAddView extends CustomComponent implements View {
    private static final long serialVersionUID = 1L;

    private TextField address;
    private Button save;

    public static final String USERVIEW = "user";

    public boolean checkValidation() {
        if (!checkTextFieldValid(address))
            return false;
        return true;
    }

    public boolean checkTextFieldValid(TextField element) {
        if (element == null || element.isEmpty()) {
            Notification.show(
                    "You should register a " + element.getDescription(),
                    Type.WARNING_MESSAGE);
            return false;
        }
        return true;
    }

    public UserAddView() {
        VerticalLayout mainLayout = new VerticalLayout();
        mainLayout.setSizeFull();
        setCompositionRoot(mainLayout);

        final VerticalLayout vlayout = new VerticalLayout();

        address = new TextField("Address:");
        address.setDescription("Address");
        vlayout.addComponent(address);

        save = new Button("Save");
        vlayout.addComponent(save);

        mainLayout.addComponent(new HeaderMenu());
        mainLayout.addComponent(vlayout);
        addListeners();
    }

    private void addListeners() {
        save.addClickListener(new ClickListener() {
            private static final long serialVersionUID = 1L;

            @Override
            public void buttonClick(ClickEvent event) {
                if (checkValidation() == true) {
                    ServerRuntime cayenneRuntime = ServerRuntime.builder()
                            .addConfig("cayenne-myapplication.xml").build();
                    ObjectContext context = cayenneRuntime.newContext();
                    UserInfo user = context.newObject(UserInfo.class);

                    user.setAddress(address.getValue());

                    user.getObjectContext().commitChanges();

                    Notification.show(
                            "Has been saved, We will send you your password by email. Your user login is: "
                                    + email.getValue(), Type.TRAY_NOTIFICATION);
                    getUI().getNavigator().navigateTo(HomepageView.MAINVIEW);
                }
            }
        });
    }

    @Override
    public void enter(ViewChangeEvent event) {
        // TODO Auto-generated method stub

    }
}

EDIT, add information : In my user object, I have a userid (primary key), in cayenne I wrote it as primary key too and in smallint. This error seems to be link... https://cayenne.apache.org/docs/3.1/api/org/apache/cayenne/dba/sybase/SybasePkGenerator.html

Why are you using the SybasePKGenerator, when you tagged the question with sql-server? — André Schild, Apr 10 '17 at 13:10
(I'm pretty new on cayenne and vaadin : I probably make a big mistake in my code). I don't know where I use SybasePKGenerator,Is it because I put PK in cayenne? i don't think so but my database is on sql server. — Bob, Apr 10 '17 at 13:16
SQLServerPkgenerator extends from SybasePKGenerator, so this is not an indication of a problem. I will address the actual problem in a reply below. — andrus_a, Apr 10 '17 at 18:36

score 1 · Accepted Answer · answered Apr 10 '17 at 18:49

1

The error happens when you insert a new object. For each new object Cayenne needs to generate a value of the primary key. There are various strategies to do this. The default strategy depends on the DB that you are using. For SQLServer (and for Sybase, as you've discovered :)) that strategy is to use a special stored procedure.

To create this stored procedure (and other supporting DB objects), go to CayenneModeler, open your project, and select "Tools > Generate Database Schema". In "SQL Options" tab, uncheck all checkboxes except for "Create Primary Key Support". The SQL you will see in the window below the checkboxes is what you need to run on SQL server. Either do it from Cayenne modeler or copy/paste to your favorite DB management tool.

There's also an alternative that does not require a stored procedure - using DB auto-increment feature. For this you will need to go to each DbEntity in the Modeler and under the "Entity" tab select "Database-Generated" in the "Pk Generation Strategy" dropdown. This of course implies that your PK column is indeed an auto-increment in the DB (meaning you may need to adjust your DB schema accordingly).

answered Apr 10 '17 at 18:49

andrus_a

2,528
1
16
10

Thanks!!! It's working. I prefere your alternative solution (add auto-increment). I think it's better, isn't it? – Bob Apr 11 '17 at 09:38
1

Yes, auto-increment is what I usually prefer myself. – andrus_a Apr 11 '17 at 13:37
Thanks for your help! It was very very helpful – Bob Apr 11 '17 at 13:54
When I export my CayenneDataObject, Is it normal that I don't have the getter for the primary key? I understand for the setter because I put an auto-increment on it. But I should be able to get the primary key, shouldn't? Thanks – Bob Apr 13 '17 at 13:27
1

You should've started a separate SO question on this, as this is an unrelated issue. But anyways - yes, it is expected. By default Cayenne excludes PK columns from object properties (after all there is an opaque DataObject.getObjectId() that abstracts object identity). But you can actually map it to an ObjAttribute explicitly if you want. Or use "Cayenne.longPKForObject(..)" to retrieve the value. – andrus_a Apr 13 '17 at 15:07

stored procedure 'auto_pk_for_table' not found

1 Answers1

Linked