Looks like some characters are not supported for Data type nvarchar. I am using latest version of WatsonORM with MySql.

For example cyrilic (Ш,Ћ,Ж), or Serbian latin, eg. Š,Ć,Ž. Or am I missing something? I see in tests that you added letter "é", I guess to test unicode, this letter works, however.

It will be good to retrieve unknown type as text when DataTable are returned as described here for npgsql
Link

using (var client = new DatabaseClient(DbTypes.SqlServer, database: "MyDb")) 
{
  var dboQueryResult = client.Select(tableName: "Users", returnFields: new List<string> { "UserName"}); //Works and returns UserNames!

  var mySchemaQueryResult = client.Select(tableName: "mySchema.Users", returnFields: new List<string> { "UserName"}); //Fails and throws an error!
}

I want to get data from a table db.mySchema.Users and am getting an error when I include the schema in the tableName parameter for the DatabaseClient.Select() method.

System.Data.SqlClient.SqlException: 'Invalid object name 'mySchema.Users'.'

I don't see another place to specify schema but maybe I need to look harder? Does this functionality exist? If not, can you point me to the area of the code where it might be possible and I will send you a pull request?

--- Bug Report ---
Operating system and version: Windows 10
Framework and runtime: .NET Core 3.1
Issue encountered: MySql syntax error when inserting into table with no primary key. The insert seems to work fine, the error occurs in the post-retreiveal section when the retrievalQuery is created since primaryKeyColumn is null.

Expected behavior: No Exception? Perhaps a IsNullOrEmpty check on primaryKeyColumn and return of the original results instead.
Steps to reproduce: I have a mysql test database and a ms sql test database with sample data. The TestDB in SqlServer has one table called Inventory. The table description can be seen in the output log.

Sample code encapsulating the problem:

            // Program idea: Copy db tables and content from ms sql to mysql.

            var tables = mssql_client.ListTables();
            foreach (var table in tables)
            {
    
                var cols = mssql_client.DescribeTable(table);
                var primkey = mssql_client.GetPrimaryKeyColumn(table);
                Console.WriteLine($"TableName: {table}, PrimaryKey: {primkey}");
                
                if (mysql_client.TableExists(table))
                {
                    mysql_client.DropTable(table);
                }

                mysql_client.CreateTable(table, cols);

                var data = mssql_client.Select(table, null, null, null, new Expression());

                if (data != null && data.Rows.Count > 0)
                {

                    var dictlist = data.AsEnumerable().Select(
                            row => data.Columns.Cast<DataColumn>().ToDictionary(
                            column => column.ColumnName,
                            column => row[column]
            )).ToList();
                    foreach (var item in dictlist)
                    {
                        var res = mysql_client.Insert(table, item);
                    }
                }

            }

Exception details: [insert exception output here] Stack trace: [if appropriate]

[DatabaseWrapper.SqlServer] query: SELECT TABLE_NAME FROM TestDB.INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = 'BASE TABLE'
[DatabaseWrapper.SqlServer] result: 1 rows
[DatabaseWrapper.SqlServer] query: SELECT col.TABLE_NAME, col.COLUMN_NAME, col.IS_NULLABLE, col.DATA_TYPE, col.CHARACTER_MAXIMUM_LENGTH, con.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.COLUMNS col LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE con ON con.COLUMN_NAME = col.COLUMN_NAME AND con.TABLE_NAME = col.TABLE_NAME WHERE col.TABLE_NAME='Inventory' AND col.TABLE_CATALOG='TestDB'
[DatabaseWrapper.SqlServer] result: 3 rows
[DatabaseWrapper.SqlServer] query: SELECT col.TABLE_NAME, col.COLUMN_NAME, col.IS_NULLABLE, col.DATA_TYPE, col.CHARACTER_MAXIMUM_LENGTH, con.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.COLUMNS col LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE con ON con.COLUMN_NAME = col.COLUMN_NAME AND con.TABLE_NAME = col.TABLE_NAME WHERE col.TABLE_NAME='Inventory' AND col.TABLE_CATALOG='TestDB'
[DatabaseWrapper.SqlServer] result: 3 rows
TableName: Inventory, PrimaryKey:
[DatabaseWrapper.Mysql] query: SHOW TABLES
[DatabaseWrapper.Mysql] result: 9 rows
[DatabaseWrapper.Mysql] query: DROP TABLE IF EXISTS Inventory
[DatabaseWrapper.Mysql] result: 0 rows
[DatabaseWrapper.Mysql] query: CREATE TABLE Inventory (id int NULL , name varchar(50) NULL , quantity int NULL ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
[DatabaseWrapper.Mysql] result: 0 rows
[DatabaseWrapper.SqlServer] query: SELECT * FROM [Inventory]
[DatabaseWrapper.SqlServer] result: 2 rows
[DatabaseWrapper.Mysql] query: START TRANSACTION; INSERT INTO Inventory (id,name,quantity) VALUES (1,'banana',150); SELECT LAST_INSERT_ID() AS id; COMMIT;
[DatabaseWrapper.Mysql] result: 1 rows
[DatabaseWrapper.Mysql] query: SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Inventory' AND TABLE_SCHEMA='classicmodels'
[DatabaseWrapper.Mysql] result: 3 rows
[DatabaseWrapper.Mysql] query: SELECT * FROM Inventory WHERE =0

An exception was encountered which triggered this message.
Method: Main
Text: Outer exception
Type: MySql.Data.MySqlClient.MySqlException
Data: System.Collections.ListDictionaryInternal
Inner:
Message: 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 '=0' at line 1
Source: MySql.Data
StackTrace: at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId)
at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at DatabaseWrapper.Mysql.DatabaseClient.Query(String query)
at DatabaseWrapper.Mysql.DatabaseClient.Insert(String tableName, Dictionary2 keyValuePairs) at DatabaseWrapper.DatabaseClient.Insert(String tableName, Dictionary2 keyValuePairs)
at Test.Sqlite.Program.Main(String[] args) in C:\git\testrepos\DBTest\DBTestWrapper\Program.cs:line 72
Stack: StackToString <= ExceptionConsole <= Main
Line: 0
File:
ToString: MySql.Data.MySqlClient.MySqlException (0x80004005): 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 '=0' at line 1
at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId)
at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at DatabaseWrapper.Mysql.DatabaseClient.Query(String query)
at DatabaseWrapper.Mysql.DatabaseClient.Insert(String tableName, Dictionary2 keyValuePairs) at DatabaseWrapper.DatabaseClient.Insert(String tableName, Dictionary2 keyValuePairs)
at Test.Sqlite.Program.Main(String[] args) in C:\git\testrepos\DBTest\DBTestWrapper\Program.cs:line 72

--- End ---

'The given key '28265' was not present in the dictionary.' when trying to connect to my server I run on MariaDB | DatabaseWarpper V 4.0.0.1

Hi Joel,

I cannot change the regional settings the SQL Server and the computers have to use here. The SQL Server variable @@language returns 'Deutsch' and the regional settings are also German ("de-DE").

The following problems were encountered when running Test.SqlServer:

1. Wrong format for the datetime strings. This happened during insert. The cure was to add "Current Language=us_english; " to the connection string.
2. Now the format of the number 123.456 was "123,456" in the German locale and it was of course wrong for the SQL Server. This was fixed by adding the following to the beginning of Main:
   System.Globalization.CultureInfo.CurrentCulture = System.Globalization.CultureInfo.InvariantCulture;

I can think of the following options:

1. Use the abovementioned fixes. I am not sure about alphabetical ordering; the initial tests show that it might work.
2. Check all the uses of ToString() and put System.Globalization.CultureInfo.InvariantCulture everywhere.
3. Create a function to format datetimes and strings according to the column type (as fetched from the database) and the CultureInfo.InvariantCulture.
4. Skip conversion to string by stashing everything to query parameters. This makes debugging queries harder than it needs to be. On the plus side, all conversion woes should disappear and the library will tolerate any locale settings on both user side and SQL Server side.

There is a related problem, namely, I cannot insert into tables with columns of type "datetime", because there are too many decimal points and the SQL Server cannot convert it. Although the decimal points are zeroes, but still. Maybe just drop the extra zeroes?

Which options of the above are "no-no"? Is there any "right" solution? I would give implementing it a try.

I would be happy for all the input.

Heyo,

while implementing my Database into my C# Project i stumbled upon this exception while inserting Data to a Table:

System.ArgumentException: "Unknown DataType: longtext"

After Further testing i realized that the data gets inserted into the table just fine and that i also can get the Datavalue out of the table with a select statement or updating a row.

The code im trying to execute:

string teststring = "Cooler string der nicht so lang ist wie gedacht";
byte[] bytes = Encoding.ASCII.GetBytes(teststring);
Dictionary<string, object>  d = new Dictionary<string, object>();
d.Add("test", teststring);
d.Add("test2", bytes);
dbclient.Insert("Test", d);

the test table setup is like this:

Im using MySQL with Xampp
Server Type: MariaDB
Server version: 10.4.21-MariaDB - mariadb.org binary distribution
Protocol version: 10
Server Character set: UTF-8 Unicode (utf8mb4)

Does anyone have sample nunit test using this wrapper for postegresql using that if we can test the DB like updating a record , deleting ?

I have a problem using the pgsql wrapper.
When I use the Insert method with a string containing some character like 'é','è' ,'à'... there are converted as unicode (\u) and then I have an error: There's no column named '\u'

Is this possible to have a fix to keep the characters as they are ?

Using the query method everything is fine.

Best regards

Can this library call Views or Stored Procedures? Thanks

Hi,

thanks for the great library. Any plans to go full async/await?

Cheers,
Sascha

Hi Joel,
It just a great library I came across.
I was trying to use gatekeeper nuget package for postgresql database. Did required changes to support it. I was getting a following error.

In following scenario I reproduced it

1. Ran GateKeeperConsole project
2. first time it is running properly and creating all required table in database
3. during second time execution, it is giving above error
   I think it is trying to get table information from database, during that time we are getting error.
   function LoadTableColumnsQuery from DatabaseWrapper.Postgresql/PostgresqlHelper.cs class had hardcode test value. It should be like ***** "FROM " + database + ".INFORMATION_SCHEMA.COLUMNS cols " + ****
   

Could you please do this changes?

Is there a way to construct DatabaseClient using the connection string in the form of:

Data Source=DESKTOP;Initial Catalog=X1;Integrated Security=True

That's using localhost default port Windows Authentication. The examples all use user credentials.

It's possible to add a constructor for the DatabaseClient accepting directly the connection string as parameter ?
Actually I parse the conection string to create the client and your code recreate the connection string.
I tried to make a pull request but it's not possible on the Master branch.

Table name must have either zero or one period

I received this error today while trying to make use of this wrapper. I am attempting to use a fully qualified table name thus making use of multiple databases on one client. This would generate perfectly valid SQL and seems an arbitrary limitation imposed by this library.