Postgres, ZEOS and Visual PlanIt - unable to create a table

[kkuba]

@paweld You did not understand me. I do not transfer the project to CT, but I do not want to enter the trunk version with a large project because of a minor problem. And updating components not via OPM is very problematic when you need to have multiple Lazarus installations. You then need to write an manual on how to configure the IDE for the project to be compiled successfully.
The problem with TMS, on the other hand, is that they only sell components in packages and it is very expensive.
But thanks again for your help - now it works! 

[wp]

@wp: attached modified version of VpZeosDs.pas taking into account setting the structure depending on RDBMS, for now only sqlite, mysql, pstgres and mssql

Committed with minor modifications.

[kkuba]

@wp Then I am attaching the .po file for the Polish language. Maybe it is worth reporting the whole package to OPM as a new version?

[paweld]

@wp: thank you very much.

[wp]

I extended the sample in folder examples/datastores/zeos to show application of a firebird v3 database with the ZEOSDatastore. Since I am not very experienced with Firebird I'd like to ask interested users to test this example, and maybe to extend it to firebird v2.x or other DB systems.

[kkuba]

@wp I checked example and We have to go back to the beginning. ZEOS 8 has better error handling, but ZEOS 7.2 shows that there are problems with VpZeosDs.
First of all I can see that the code has a confusing reference to the Connected property from Database Connection Component.
For example, see the procedure TVpZeos.Datastore.SetConnected
It says:
  if (AValue = Connected) or (FConnection = nil) then Exit;
  if AValue and AutoCreate and not TablesExist then  CreateTables;
  FConnection.Connected := AValue;
It probably does not make sense. The order is also probably wrong. Shouldn't it be like this?
if FConnection = nil then  Exit;
if AValue = FConnection.Connected then Exit;
FConnection.Connected := AValue;
if AValue and AutoCreate and not TablesExist then CreateTables;
Similar bugs are in procedure TVpZeosDatastore.CreateTables. For example look at wasConnected variable usage.

It's hard for me to improve code because I don't know the whole component. Maybe You could take a look at this?
In my opinion, this problem generates random errors.

[calebs]

Sorry to revive this post. I've tried visualplanit with zeos and sqlite and the tutorial in the wiki worked fine as expected. The problem appear when i try to use it with mariadb (10.4). It cant create the tables because the autoincrement (without the dash). and hanged completely the ide sometimes.
I couldnt find a sql to manually create the table so i use IA to generate the sql from the pdf documentation to create the tables and remove the autocreate parameter from vpzeos datastore but when i try to first run it gives me another error that i think is that doesn't recognize the tables and tries to correct them and fail.
Anyone has the right sql to create the tables on db? or any other workaround?
I want to use it with mysql 5.7 and newer and mariadb compatible versions.

[wp]

Finally got mysql running again and was able to fix some bugs in the ZEOS datastore. Committed to SVN (https://sourceforge.net/p/lazarus-ccr/svn/HEAD/tree/components/tvplanit/source/addons/zeos/vpzeosds.pas).

[wp]

Uploaded also a dedicated datastore for MySQL, based on SQLDB, as well as a sample project in (tvplanit)/examples/datastores/mysql. Tested it wiith TMySQL57Connection on Windows 32-bit and 64-bit - working.

[calebs]

Hello wp, i've downloaded a snapshot of tvp and tried to install it but it gives me error on this line

198 -   VpMySqlDS,                  { Datastore for MySQL databases                }     
vpreg.pas(198,3) Error: Cannot find VpMySqlDS used by VpReg of package laz_visualplanit_design.

but when i search the files in the folder, vpmysqlds.pas is present on source folder.
then i removed it, proceed to install vpzeos and installed ok
but then tried again vpdesign and gives the same error.

[wp]

When units are not found in the building process although they are existent, it usually helps to force a clean recompilation. Usually do a clean rebuild of the IDE ("Tools" > "Configure build Lazarus" > check "Clean all" and "Switch to automatic mode after building").

[calebs]

hi wp, downloaded svn, then proceed to erase builded lazarus with fpdeluxe, then rebuild again (fpc stable, lazarus fixes), installed all my  usual components (zeos, synapse, and others) then installed the svn version of visual planit and worked fine, compiled and runned.
It will be good to put that version on online package manager instead the non working.
Many thanks

[calebs]

Sorry, shouted victory before last battle. In other machine downloaded svn 9909 and added to lazarus 4.5 (stable fpc, fixes lazarus) and tried again with zeos and 10.5.29-MariaDB-0+deb11u1 - Debian 11 and errors again when tries to create the tables.
Downloaded svn 9910 and tried again but with no success. Can't autocreate the tables on the db
I attach the snapshot of the error (it looks like the long cat meme)

[wp]

The first screenshot mentions "AUTOINCREMENT" - this looks as if you are still using the old version somehow. In the unit using TvPlanIt right-click on the word "VpZeosDS" in the uses clause. This should open the VpZeosDS unit in your system used. In the implementation section find the procedure "AdjustSQLFieldTypeNames". In the block "if protocol = 'mariadb'" you should see "AUTO_INCREMENT" rather than "AUTOINCREMENT" (likewise in the "mysql" block).

• If you don't, delete the "lib" folder in your "tvplanit" installation directiory, and recompile the IDE.
• If you do, we maybe have another issue because I only was able to test mysql instead of mariadb, and I noticed that mysql does not like the fieldtype 'TIMESTAMP' and had to replace it by 'DATETIME' (at least in the version that I had access to) - maybe mariadb requires the same modification: Go to the procedure "AdjustSQLFieldTypeNames" that I had mentioned already. Find the "if protocol ='mysql'" block, copy the line "FDateTimeFieldTypeNameInSQL := 'DATETIME'" and paste it into the block "if protocol = 'mariadb'"; you maybe also have to adjust the value of the FBoolFieldTypeNameInSQL from "BOOLEAN" to "BIT" used by mysql. Not sure if it helps, but if it does, absolutely report back so that I can adjust the sources.

[calebs]

Hello. It seems that doesnt recognize the protocol because i've chequed vpzeosds and says "auto_increment" in mariadb/mysql section but in the error still says autoincrement even after clean & recompile. So i proceed to change in the upper section (lines 127 and after)

Code: Pascal  [Select][+][-]

1. ////  FIdFieldTypeNameInSQL := 'INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT';
2.   FIdFieldTypeNameInSQL := 'INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT';
3.   FBoolFieldTypeNameInSQL := 'BIT';
4. //  FBoolFieldTypeNameInSQL := 'BOOL';
5. //  FDateTimeFieldTypeNameInSQL := 'TIMESTAMP';
6.   FDateTimeFieldTypeNameInSQL := 'DATETIME';  

and now it creates the tables and worked as expected.
btw, before tried on 10.4.32-MariaDB - mariadb.org binary distribution with windows as server and it worked well. I dont know if is that the protocol parameter of the zeos was selected by object property or assigned programatically but is for sure that i never put mariadb on protocol value, only mysql.
thanks