Date Types PostGres SQL about vita HOT 34 CLOSED

try removing DbTypeSpec

from vita.

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

Thanks.

from vita.

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

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

from vita.

using 'date' it gives the same error.

from vita.

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?

from vita.

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

from vita.

the "dbo" is the EntityArea name.

from vita.

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?

from vita.

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

from vita.

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

Thanks.

from vita.

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

from vita.

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.

from vita.

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

from vita.

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.

from vita.

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

from vita.

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 ?

from vita.

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.

from vita.

@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"

from vita.

You do not define the database name?

from vita.

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.

from vita.

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

from vita.

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

from vita.

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

from vita.

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

from vita.

@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.

from vita.

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.

from vita.

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.d__231.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.d__52.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.d__52.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.d__119.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.d__119.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.d__107.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

from vita.

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

from vita.

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.

from vita.

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

from vita.

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.

from vita.

Any news oh the PgSql fix?

I think that i have every thing ready.

Continue the good work with VITA.

from vita.

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

from vita.

pushed, version 4.0.0, this issue is fixed

from vita.