Date Types PostGres SQL

[rubenalves]

I am trying to convert my application to Postgres and i am getting this error updating the database:

Failed to map member type System.DateTime to DB type; member IvaTaxa.Data - error: Member IvaTaxa.Data: failed to map DB type date.

This is only the first error, there are many more, the entity definition is like this:

[Entity(Name = "IvaTaxa", TableName = "IvaTaxas")] public interface IIvaTaxa { [PrimaryKey, Identity] int ID { get; set; } IIva Iva { get; set; }

 [Column(DbTypeSpec = "Date")]
 DateTime Data { get; set; }

 [Column(Scale = 2, Precision = 5)]
 decimal Acores { get; set; }

 [Column(Scale = 2, Precision = 5)]
 decimal Continente { get; set; }

 [Column(Scale = 2, Precision = 5)]
 decimal Madeira { get; set; }

 [Column(Scale = 2, Precision = 5)]
 decimal Isenta { get; set; }

}

is there a difirent way to DbTypeSpec in postgres? Thanks.

[rivantsov]

try removing DbTypeSpec

[rubenalves]

But i only want the Date not a DateTime column, if i use DateOnly does it work?

Thanks.

[rivantsov]

Use this a ref to pg types: https://www.postgresql.org/docs/current/datatype-datetime.html

try using 'date' , with non-capital first char

[rubenalves]

using 'date' it gives the same error.

[rubenalves]

I am tying with mysql, the model bilds ok but when i connect i get this error:

Vita.Entities.DataAccessException: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"dbo"' at line 1'

What version works with Vita?

[rivantsov]

please run Vita extended tests for postgres and MySqL, they run OK. Then try playing with these and modify some fields (like date) and maybe you can figure it out. I will look at pg dates later (not today, tomorrow maybe), dont have time now. As for MySql error, it refers to SQL you're running, so look at the SQL, it should be printed out with error. But anyway, start with Vita extended tests Roman

[rubenalves]

the "dbo" is the EntityArea name.

[rivantsov]

Found the problem, 'date' was missing in Postgres types in Vita; I added it, tried on Books (with Column(DbtypeSpec='date') ) and it works. I will make a patch release of Postgres driver. Before I do that, what other errors you see?

[rubenalves]

I also see thie error: Failed to build ORDER BY list for table Caixas: cannot find column for member DataCaixa. and Failed to map member type System.Nullable`1[System.DateTime] to DB type; member Expediente.ConcluirAte - error: Member Expediente.ConcluirAte: failed to map DB type date. Some are nullable fields and other is a ordered list. Thenks

[rubenalves]

This is not urgent, you do not need to wory about this.

Thanks.

[rivantsov]

got it, thanks, will try to push quicker nevertheless. For now, to continue experimenting with move to Postgres (smart by the way!), suggest to keep 'dates' as regular DateTimes, everything should work the same, SQL, Linq, etc. Then you'll switch to Date later when I push

[rubenalves]

I am tying mysql also and i get this error: Vita.Entities.DataAccessException: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"tsgest"."Unidades" ( "ID" varchar(50) NOT NULL , "Designacao" varchar(250) ' at line 1'

Where can i find a test with mysql?

I have this in my entity app EntityArea tsgestArea = AddArea("dbo"); MainModule = new TsGestModule(tsgestArea); EntityArea sociosArea = AddArea("dbo"); SociosModule = new SociosModule(sociosArea); EntityArea posArea = AddArea("dbo"); PosModule = new PosModule(posArea);

looks like the area is the problem. if change the area to the same of the database i get diferent errors.

Thanks.

[rivantsov]

Look at extended tests, _Startup.cs file (line 71); just assign entityApp.LogFilePath and you'll see the SQL log there. If you assign filename without path, the file will be in the bin/net8 folder. Look at full SQL statement that fails. About your setup code - you add multiple areas with the same name 'dbo' - that is error, assign different names or just use one area for all

[rubenalves]

I have change to only one Area, 'dbo' It works fine with sqlserver but with mysql i get the error complaining with the name dbo schema Vita.Entities.DataAccessException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"dbo"' at line 1 ---> MySql.Data.MySqlClient.MySqlException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"dbo"' at line 1 em MySql.Data.MySqlClient.MySqlStream.d__30.MoveNext() what version of mysql does vita support?

Thanks.

[rivantsov]

MySql version shows 8.0.25, pretty old I guess but it should work for later versions. Pls setup SQL logging as I explained and find SQL statement that fails. Alternatively, DataAccessException has field DbCommand which has failed SQL, stop on breakpoint and copy the SQL

[rivantsov]

by the way, at what stage it happens - at app start when app sets up database (creating tables), or later, when you execute data operations, running sqls etc ?

[rubenalves]

It gives errors on the setup de database (creating tables) stage. Vita.Entities.DataAccessException: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"dbo"' at line 1'

the SQL is 'CREATE SCHEMA "dbo";' but in my sql SCHEMA and DATABASE are the same thing, so probaby that is the problem.

[jasonlaw]

@rubenalves Is your connection string contains the database param? can you try remove it and run again? I am using MySQL and I found that the database param in the connection string is only working when the database is already pre created. Here is how my connection string looks like: "Server= xxx.xxx.xxx.xxx; Port= 3306; Uid= xxxx; Pwd= xxxxxx; Old Guids= true; SslMode= none; CharSet=utf8mb4"

[rubenalves]

You do not define the database name?

[jasonlaw]

Yes, for SQL Server, it is working with the database name, even the database is not pre-created yet. However, for MySQL, if the database is not pre-created, with the database name in the connection string, it will hit the error.

[rubenalves]

The database is already created with no tables, so vita can do the rest. But it just gives errors like this. 14:40:12 CREATE TABLE "Unidades" ( "ID" varchar(50) NOT NULL , "Designacao" varchar(250) NOT NULL , "Fator" int NOT NULL , "Ordem" int NOT NULL , "Orcamento" bit NOT NULL , "DataCriacao" datetime NOT NULL , "DataAlteracao" datetime NOT NULL , "UnidadeReferencia_ID" varchar(50) NULL ) Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"Unidades" ( "ID" varchar(50) NOT NULL , "Designacao" varchar(250) NOT NULL , ' at line 1 0,000 sec

[rivantsov]

just tried running this sql on my local MySql, it gives error about 'No database selected'. If you add a schema prefix like dbo."Unidades" for existing schema/database 'dbo', then it runs OK. the question is why your SQL has 'dbo' missing. Looking into how it might happen

[rivantsov]

did you see these comments in MySqlDbDriver.cs: /* Note:

• 1. Add the following options to sql-mode variable in my.ini file:
• ANSI_QUOTES
• 2. MySql supports ordered columns in indexes, but there's no way to get this information when loading index columns from the database. So we suppress ordering;
• we also set all column direction to ASC after construction DbModel
• 3. Connection string must have ' Old Guids=true' to properly handle Guids

  */ specifically #1 about Ansi quotes

[rivantsov]

and by the way, @rubenalves , my question again - can you run Vita tests on your local MySql ?! if yes, then look at differences in setup. If no, then let's look at tests, what's missing

[rubenalves]

@rivantsov i do not know how to run the Vita tests. I will try if i can figure it out.

But i thinks that i sill stick with MsSql that i know how to use. And looks like Vita also works better with it. Thanks.

[rivantsov]

Running vita tests is quite simple. Open Test Explorer window in VS (Test/Test Explorer menu), group tests by project (icon on toolbar). You will see a tree with test projects on top and individual tests under them. Start with basic tests, r-click on Vita.Testing.BasicTests, and select Debug. VS will run the tests, if anything fails, you will see red icon on the failed test. You might need to adjust appSettings.json file (connection string, server type) in corresponding projects, create databases for tests (VitaTest, VitaBooks etc). Start with MsSQL, then try for Postgres.

I would advice not to give up on postgres - it is free after all, and it is a really good server! I will add 'date' data type in the next push, and it will work, I am sure.

[rubenalves]

I have remove de date on all columns, now i see a new error,

I have this defenition on one or too entityes, [Column(Default = "0")] bool Cliente { get; set; }

I get this error create tables. Vita.Entities.DataAccessException: 42804: coluna "Cliente" é do tipo boolean mas expressão padrão é do tipo integer ---> Npgsql.PostgresException: 42804: coluna "Cliente" é do tipo boolean mas expressão padrão é do tipo integer em Npgsql.Internal.NpgsqlConnector.d231.MoveNext() --- Fim do rastreio da pilha da localização anterior em que a excepção foi emitida --- em System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() em Npgsql.NpgsqlDataReader.d52.MoveNext() --- Fim do rastreio da pilha da localização anterior em que a excepção foi emitida --- em System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() em Npgsql.NpgsqlDataReader.d52.MoveNext() --- Fim do rastreio da pilha da localização anterior em que a excepção foi emitida --- em System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() em System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) em Npgsql.NpgsqlDataReader.NextResult() em Npgsql.NpgsqlCommand.d119.MoveNext() --- Fim do rastreio da pilha da localização anterior em que a excepção foi emitida --- em System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() em Npgsql.NpgsqlCommand.d119.MoveNext() --- Fim do rastreio da pilha da localização anterior em que a excepção foi emitida --- em System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() em System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) em Npgsql.NpgsqlCommand.d107.MoveNext() --- Fim do rastreio da pilha da localização anterior em que a excepção foi emitida --- em System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() em System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) em Npgsql.NpgsqlCommand.ExecuteNonQuery() em Vita.Data.Driver.DbDriver.ExecuteCommand(IDbCommand command, DbExecutionType executionType) --- Fim do rastreio da pilha de excepção interna --- em Vita.Data.Driver.DbDriver.ExecuteCommand(IDbCommand command, DbExecutionType executionType) em Vita.Data.Upgrades.DbUpgradeManager.ApplyUpgrades() Exception data (Vita.Entities.DataAccessException): DbCommand = CommandType: Text CommandText: CREATE TABLE dbo."ContasSNC" ( "Conta" character varying(50) NOT NULL, "TipoConta" int NULL, "Designacao" text NOT NULL, "Reconciliavel" boolean NOT NULL, "PodeSerAlterada" boolean NOT NULL, "Cliente" boolean DEFAULT 0 NOT NULL, "Fornecedor" boolean DEFAULT 0 NOT NULL, "ContaIva" boolean DEFAULT 0 NOT NULL, "IncluirApuramento" boolean DEFAULT 0 NOT NULL, "TaxaNormal" boolean DEFAULT 0 NOT NULL, "TaxaIntermedia" boolean DEFAULT 0 NOT NULL, "TaxaReduzida" boolean DEFAULT 0 NOT NULL, "Isenta" boolean DEFAULT 0 NOT NULL, "ContaSNC_id" character varying(50) NULL, "Entidade_ID" uuid NULL ); Parameters:

Exception data (Npgsql.PostgresException): Severity = ERRO InvariantSeverity = ERROR SqlState = 42804 MessageText = coluna "Cliente" é do tipo boolean mas expressão padrão é do tipo integer Hint = Você precisará reescrever ou converter a expressão. File = heap.c Line = 2807 Routine = cookDefault

[rivantsov]

Just remove Default(0) in Column attribute. I guess it might be leftover from MS SQL, default should be false anyway Edit: or replace 0 with false

[rubenalves]

Just remove Default(0) in Column attribute. I guess it might be leftover from MS SQL, default should be false anyway Edit: or replace 0 with false

I need it like that because another app that i do not control writes on the same database and if i remove that it does not work, it does not send the values for that columns.

[rivantsov]

Since it's a bool column, try using false as an argument (instead of 0)

[rubenalves]

If i do it with false, ti failes to buid in sql server. I am in converstions with the developer to change it on his side. Will be better for every one.

[rubenalves]

Any news oh the PgSql fix?

I think that i have every thing ready.

Continue the good work with VITA.

[rivantsov]

OK, happy for you man, expect it by the end of the week. Sorry, been busy with upper layers, gathering things to fix in Vita

[rivantsov]

pushed, version 4.0.0, this issue is fixed