I have some databases for which I’m developing several Servoy front ends. These databases are also accessed by an existing web-based PHP system. The PHP system uses the database’s (MS SQL2000) auto incremented primary keys. I understand that Servoy will not allow you to have the primary key managed by the back-end database and requires you to have Servoy insert and manage the primary key. I need some suggestions on how to handle this. Should I create a sudo PK coloumn just for Servoy? And what happens if the PHP system inserts a new record. How will Servoy handle a NULL in it’s sudo PK column? I’m kinda stumped.
As far as I can tell, you will stay stumped until Servoy implements some database-specific work arounds.
In the meantime, the rule is that if you use PHP to add records to a table, you can only reliably view and search this table in Servoy. If you add records to a table with Servoy, don’t add records with any other front-end.
One work-around is to use a separate “web only” table that PHP can add records to. Then transfer the data as needed in Servoy from the “web only” table to the production table. Works fine for general HTML form input. If you absolutely need the transfer to happen in near real-time, set Servoy Client running on a box with a timed script doing the transfer at repeating intervals.
A little clunky but it works.
- david workman
Not what I wanted to hear. This is a BIG problem. If Servoy can’t use the database’s auto-incremented primary key, why is it even an option in the program??
auto increment pk’s are on the roadmap directly after 1.1
That database value is for the “default value” columns in the database
I want ONLY to be able to READ from a particular database. Servoy would be used solely for creating and managing tables that join to existing tables managed by another application. In fact, my software license for the existing database application prohibits me from creating/editing/deleting records in its database.
Since this may be a common problem thread, would one workaround be for Servoy to have a built-in read-only flag on a dataprovider, eliminating the need (and possibility, for that matter) for a Servoy managed PK?
Thanks
John Fitzpatrick
There are several options for Servoy to be in read only mode
-set the rigths for the database connection to be readonly
-use Servoy security
-In the column(dataprovider) properties set a column to be ‘db managed’ (Note: this does NOT yet work in combination with primary keys, is plannen for release after 1.1, need to implement db specific routines to support this)
I’m reviving this very old thread because I’ve run into the same issue using Servoy 3.1, not 1.1! I need to do some maintenance on my phpbb bulletin board data, and would like to create some Servoy methods & forms to do so.
I have successfully set up the connection from Servoy on my local computer to the mySQL (version 5 something, I believe) db hosted by Datatude’s domain host. Servoy can see the tables & columns. But it does not see any of the columns as a primary key so it will not retrieve any data.
I tried “auto-discovery” in Relationships but it just times out.
I tried checking the “row identifier” box for the id row in a table, but Servoy will not let me make this change.
I confess this is my very first experience attempting to work with a pre-existing back end; everything else I’ve done involved designing & creating the back end myself. I also confess I’ve spent a grand total of perhaps 30 minutes trying to solve this with the manual and with forum searches, and perhaps am being a little lazy here. I’m hoping there is a quick answer to how to make Servoy recognize a db-managed (or perhaps it is php managed; I’m not sure) pk in a database that was not built via Servoy. This is probably a pathetically basic question … sorry
kazar
Woops I solved this just moments after posting and ran into what looks like a tiny buglet.
What is going on here is that the table in question does not have a primary key … instead it has what I think is called a compound pk – the combination of two fk’s where the combo will always be unique. So rather than a single pk field, I told Servoy that the two fk’s are row identifiers. Data is now retrieved.
However, in case anyone runs into the same circumstance, it will appear as though Servoy will not let you identify rows in a pre-existing db as “row_ident” … it will let you select that designation but the Apply button does not activate. To get around this, define a new field as a temporary dummy and the Apply button then activates. Then you can delete the dummy field and the row_ident designations will stick.
Hi Ilyse,
a while ago, I tried the same, as you. (connecting to a phpBB forum) but got stuck.
thanks for the tip!
Did you post the little apply-bug in the Servoy support solution?
Just posted it after reading your confirmation. This is mySQL 4.1.15, not 5 as I had thought. And I could not reproduce the problem in SQL Anywhere after creating a b-e table in Sybase Central that had no PK … with SAS Servoy had no problem letting me designating columns as row_ident
I’m now wondering if it’s a driver issue since I may be using a newer driver incompatible with mySQL 4 … I am also now getting an error msg (some big long Nested Exception message that is unintelligible to me) when I try to define a relationship. Heading back to mySQL.com now
kazar
The behavior and functionality of the database designer screen can be radically different depending on what the backend database is, what driver you are using to connect with, and even what your connection parameters are. I don’t think you can call these differences “bugs” in Servoy then – you should be using a 3rd party tool to manage things once you start stepping out of the normal bounds.
Servoy needs a valid primary key and multicolumn incrementing keys in myisam tables don’t qualify along with many other table setup variations. I’ve run into several web applications in MySQL and SQL Server where I’ve had to go through and manually add a primary key column to all the tables before I could connect with Servoy.
kazar:
Woops I solved this just moments after posting and ran into what looks like a tiny buglet.What is going on here is that the table in question does not have a primary key … instead it has what I think is called a compound pk – the combination of two fk’s where the combo will always be unique. So rather than a single pk field, I told Servoy that the two fk’s are row identifiers. Data is now retrieved.
However, in case anyone runs into the same circumstance, it will appear as though Servoy will not let you identify rows in a pre-existing db as “row_ident” … it will let you select that designation but the Apply button does not activate. To get around this, define a new field as a temporary dummy and the Apply button then activates. Then you can delete the dummy field and the row_ident designations will stick.
We tested this, and could not reproduce the behavior.
For an row_ident flag is no “Apply” needed (we store this info in the repository and do not change the table), nor could we duplicate that the row_ident flag is lost
I wonder if this is a new issue.
I have linked servoy up to a phpbb forum in the past (about 18 months ago) with no problems at all, running MySQL 4.1.x. I only hooked up the tables for users, forums, topics, posts, post_texts and could create new posts in Servoy which would display on my web site. Maybe my phpbb was an older version.
I found phpbb attracted too many hackers and spammers so I have since replaced it with a home-made solution written in Ruby-on-Rails.