I’m gobsmacked to find that every column in every table in both databases used by my solution have had the NULL? attribute set to YES.
My dev machine and the testing machine both remain sane so I’m left with the conclusion that either the recent uploading of a new release has clobbered the db or some process in the mysql server has done it.
Has anyone else had this experience ? Its certainly new to me.
Do you mean that “Allow Null” is now checked for all your tables now when you look in developer/table editor?
If yes, what does some other DB tool report about those columns? Servoy shouldn’t modify this by itself…
Developer isn’t directly attached to these tables just so I don’t inadvertently screw things up. Developer is attached copies of these databases which is how I was able to verify that things had changed and yes, every column in every table in two databases is affected by this - about 180 tables in all. I’ve only just uploaded my solution to it in the last few days and to my knowledge all was well before that. One other change that may have affected it is upgrading the server to 5.1.4 and consequently upgrading the databases themselves. This was done before I uploaded my solution for the first time.
I’ve checked this with phpMyAdmin and in fact tried to reset the attribute using it both via the GUI and also using a SQL query. It returns success but the attribute is actually unchanged. As to whether Servoy is responsible or not I can only say at tis stage that its the prime suspect. The reason being that I have three databases on the site. One database contains 160+ tables and supports a cms (xoops). I’ve recently added another database specifically serviced by the Servoy solution I’m developing. Servoy also accesses some of the tables in the xoops database. Consequently when I upload the solution the tables in both databases are affected if there are datamodel changes.
The webhost seems mystified as well. Their only response has been to upgrade the phpmyAdmin to the latest version.
This is quite a scary situation. I’ve had to shut down the website as the merchant gateway on the site is now acting up.
Version: 5.1.2 - build 959
Lee and Andrei,
We stumbled upon the identical situation just today! We have two applications sharing a common set of Visual FoxPro tables. One application is written in VFP and the other a Servoy solution. The VFP application (which had been working flawlessly) began to fail, because some table columns contained NULL values. After investigating, we were astonished to find that all columns were set to allow NULL values; other VFP tables had the same thing happen to them (in 25 years of product development, we never allowed NULL values in VFP tables). Looking at the same table via Servoy Developer also showed that all columns were set to allow NULL values, and we cannot uncheck the ‘Allow NULL’ checkbox.
We tested this on our server (a different computer) accessing the VFP table with both the VFP and Servoy applications. Curiously, the structure of this identical table does not have any of its columns marked to allow NULL values, although we did see some fields with NULLs in them. The columns containg NULL values are supposed to have empty string values.
gobsmacked - flabbergasted, astounded, speechless, overawed
Yeah, that’s us, too!
I just tried a few things:
The only way I can see it happening on import is if the solution was exported somehow with tables that allow null for their columns and imported into a blank server - so all tables/columns are created on import.
I would like to see this reproduced.
Lee,
You said you upgraded the server to 5.1.4 and consequently upgraded the databases themselves. What do you mean by upgrading the databases? When upgrading Servoy, you do not need to upgrade the databases, maybe just the repository database, but that only if the repository version has changed… You also said that developer is attached to copies of the databases - is it possible that creating those copies results in allow null being changed? When you uploaded/imported the solution into the app. server, did you already have the tables or were they created by the import? I am trying to understand how you manage your servers/tables between developer and app. server.
Kim,
It is normal not to be able to uncheck the “allow null” checkbox. You need to delete and recreate the column to be able to do that (thus loosing the data in it in the process) because making a column that allows null values change into a column that does not allow null values would require all data to be non-null in that column. You said “the structure of this identical table does not have any of its columns marked to allow NULL values, although we did see some fields with NULLs in them” - this shouldn’t be allowed by the database itself - non-null columns with null values… very strange. I see that you also have 2 sets of tables - on the server and developer. How do you keep them in sync?
Andrei Costescu:
I just tried a few things:
You said you upgraded the server to 5.1.4 and consequently upgraded the databases themselves. What do you mean by upgrading the databases? When upgrading Servoy, you do not need to upgrade the databases, maybe just the repository database, but that only if the repository version has changed… You also said that developer is attached to copies of the databases - is it possible that creating those copies results in allow null being changed? When you uploaded/imported the solution into the app. server, did you already have the tables or were they created by the import? I am trying to understand how you manage your servers/tables between developer and app. server.
[/quote]
Andrei - sorry you did all that work when I’ve just discovered that the problem is not Servoy but the version of phpMyAdmin that I was using. My webhost offers v2.5.5, which doesn’t seem to know how to handle NULL, but when I tried v3.3.3 the proper/developer structure was still there.
[attachment=1]acsp -phpMyAdmin 2.5.5.png[/attachment] [attachment=0]acsp-phpMyAdmin 3.3.3.png[/attachment]As to managing the different databases held by the developer versus production systems, I start with mirrored copies uploaded via phpmyAdmin. As the developer copy gets stale I refresh it the same way with a download from production. I would much rather if I could periodically attach the developer to the production servers but need an ssh tunnel and haven’t found a way in eclipse to set that up.
I’m glad to hear that you found the problem.
You should be able to set up SSH tunnel for DB servers. I did use that in the past.
Andrei,
How do you keep them in sync?
We copy the VFP data set (i.e. individual DBF files) to the computer hosting Servoy developer.
I tried to replicate the actions you performed as follows:
I will continue to test and look for NULLs in the data and will let you know if I find any.
I an still concerned why Servoy developer continues to show that all fields in every DBF table (whether we use them or not) accepts NULLs when we do not want NULLs in our data fields. We do not create the DBF tables using Servoy as they are an existing data set to which Servoy attaches to using the HXTT driver. We installed the HXTT driver and created a connection to the VFP file location…then, we were able to access the DBF files from developer’s resource menu. We did nothing to allow/disallow NULL characters in Servoy (that I can think of, anyway). I would like to try to clear the ‘allow NULL’ settings in Servoy developer and re-attach to a ‘clean’ DBF data set to see if the results are the same, but I do not know how to do that. Any suggestions?
Are you sure the database servers in developer point to the correct DBF files?
It’s strange that non-null columns show as allow null in developer. There might be a misunderstanding between the driver and Servoy or a problem in the driver.
Try to create a new table from Servoy developer in one of the existing database servers - and uncheck “allow null” when you create the columns. After you save the table, close/reopen editor and see all is set correctly and then check the new table’s structure with VFP to see if there is any difference between this table and a table that already existed before (regarding “allow null”).
Another thing you could try is insert a new row with null values for non-null columns (as seen by VFP) that show “allow null” in Servoy in an existing table from a Servoy developer client. Does it give an error message when you try to save the data complaining that it shouldn’t be null?
Try to create a new table from Servoy developer in one of the existing database servers - and uncheck “allow null” when you create the columns. After you save the table, close/reopen editor and see all is set correctly and then check the new table’s structure with VFP to see if there is any difference between this table and a table that already existed before (regarding “allow null”).
Using Servoy developer, a new DBF table was created with 3 columns all with ‘Allow Null’ unchecked. The table was saved, and Servoy developer closed and reopened. Now, the table structure showed all 3 columns marked as ‘Allow Null’ (not good). Examing the table structure using VFP shows those same columns as not accepting nulls.
Another thing you could try is insert a new row with null values for non-null columns (as seen by VFP) that show “allow null” in Servoy in an existing table from a Servoy developer client. Does it give an error message when you try to save the data complaining that it shouldn’t be null?
Programatically, from Servoy developer client, nulls were written to these 3 columns (shown as ). Examining these 3 columns in VFP shows as blank (no nulls).
It appears that the issue is on the Servoy side, not the VFP side. So, is the problem Servoy developer or the HXTT driver? The HXTT we use is ‘jdbc:dbf:////c:\arm5?lockType=VFP’.
Thanks!
Yes, the problem is then either in the driver or in Servoy. Please add a case to the Support System describing this.
http://servoy.com/s
You could also try to update the driver meanwhile (if new versions are available) and see if that solves the problem.
Submitted case No. 301508
Thanks for all your help, Andrei!
Kim,
This is an issue with the HXTT driver, see http://www.hxtt.com/support_view_issue. … 1277743708
Rob
Thanks for checking into this, Rob! If you hear anything directly from HXTT, would you please let us know.
This is the answer we received from HXTT:
Xbase format doesn’t support not null constraint. If we provides nonstandard not null constraintsupport, it means that other Xbase tool won’t see that constraint.
I don’t fully agree with their assertion regarding xBase…I have found several xBase documents supporting the NULL constraint in field flags, and VFP certainly supports it.
Thanks again for all your help!
