can this gentool support sqlserver 2008 ?

when i try to generate the model from sqlserve 2008, i got error msg like this :

2022/03/23 21:04:26 /Users/keenliang/go/pkg/mod/gorm.io/driver/[email protected]/migrator.go:153 mssql: Invalid usage of the option NEXT in the FETCH statement.
[27.141ms] [rows:-] SELECT * FROM "LawCase" ORDER BY (SELECT NULL) OFFSET 0 ROW FETCH NEXT 1 ROWS ONLY

2022/03/23 21:04:26 /Users/keenliang/go/pkg/mod/gorm.io/[email protected]/generator.go:137
[error] generate struct from table fail: mssql: Invalid usage of the option NEXT in the FETCH statement.
2022-03-23 21:04:26.057 ERROR [email protected]/recovery.go:42 [PANIC]generate struct fail
goroutine 264 [running]:
git.code.oa.com/trpc-go/trpc-filter/recovery.glob..func1({0x101a763c8, 0x14002b6ede0}, {0x10186a660, 0x101a4ac30})
/Users/keenliang/go/pkg/mod/git.code.oa.com/trpc-go/trpc-filter/[email protected]/recovery.go:41 +0x6c
git.code.oa.com/trpc-go/trpc-filter/recovery.ServerFilter.func1.1(0x102325570, {0x101a763c8, 0x14002b6ede0}, 0x14002ca3250)
/Users/keenliang/go/pkg/mod/git.code.oa.com/trpc-go/trpc-filter/[email protected]/recovery.go:60 +0x64
panic({0x10186a660, 0x101a4ac30})
/Users/keenliang/sdk/go1.17.6/src/runtime/panic.go:1038 +0x21c
gorm.io/gen.(*Generator).GenerateModelAs(0x140005dc460, {0x10156d14a, 0x7}, {0x140032243f8, 0x7}, {0x0, 0x0, 0x0})
/Users/keenliang/go/pkg/mod/gorm.io/[email protected]/generator.go:138 +0x3ec
gorm.io/gen.(*Generator).GenerateModel(0x140005dc460, {0x10156d14a, 0x7}, {0x0, 0x0, 0x0})
/Users/keenliang/go/pkg/mod/gorm.io/[email protected]/generator.go:103 +0x80

and for now, xorm can generate models from sqlserver 2008 perfectly, hope grom/gen could make it as well, best wish

The document you expected this should be explained

Expected answer

			err = conn.Db.Migrator().DropTable(&a)
			if err != nil {
				return
			}

跟踪到sql执行的是

DROP TABLE IF EXISTS "AppDict"

I am getting a panic response when trying to use Create. It looks like this is because the result is nil and we are asking for the affected rows. I'm guessing this is due to the error not getting checked at L166

panic: runtime error: invalid memory address or nil pointer dereference
[signal SIGSEGV: segmentation violation code=0x1 addr=0x20 pc=0x13fcd02]

goroutine 1 [running]:
gorm.io/driver/sqlserver.Create(0xc00071bbc0)
	/Users/estenssoros/go/pkg/mod/gorm.io/driver/[email protected]/create.go:167 +0x1762
gorm.io/gorm.(*processor).Execute(0xc000716640, 0xc00071bbc0)
	/Users/estenssoros/go/pkg/mod/gorm.io/[email protected]/callbacks.go:101 +0x21d
gorm.io/gorm.(*DB).Create(0xc000226720, 0x174a4a0, 0xc00082e0e0, 0xc00082e0e0)
	/Users/estenssoros/go/pkg/mod/gorm.io/[email protected]/finisher_api.go:19 +0xa7

GORM Playground Link

go-gorm/playground#531

Description

SQL Server 自动迁移时的自增列问题：

• gorm.io/gorm v1.24.0
• gorm.io/driver/sqlserver v1.4.1

结构体

type TableExample struct {
	ID int64 `gorm:"column:id;type:bigint;size:8;not null;autoIncrement:true;autoIncrementIncrement:1;primaryKey" json:"id"` 

	...
}

自动迁移

example := TableExample{}
tx.AutoMigrate(&example)

AutoMigrate 生成的 SQL

CREATE TABLE "table_example" (
    "id" bigint NOT NULL,
    /*其他字段...*/
    PRIMARY KEY("id")
)

结构体标签中定义了 autoIncrement:true;autoIncrementIncrement:1;，但是没有生成自增列的 IDENTITY。

我在获取sql server的uniqueidentifier类型的数据时，乱码了，我go的接收数据类型是[]byte，接收到了后转为string类型时，乱码！以下是乱码信息：Db\u000b��C.A�E\u0005QǪWB，实际上应该是5CD6C26F-DD9E-4713-BBA2-0028E0F8DF47这种样子才对，试过很多转的方法，都没用

GORM Playground Link

go-gorm/playground#1

Description

sqlserver 密码中包含@符号不能连接数据库

Version 1.4.0 fails compilation with errors:
/go/pkg/mod/gorm.io/driver/[email protected]/sqlserver.go:90:23: invalid operation: limit.Limit != nil (mismatched types int and untyped nil)
/go/pkg/mod/gorm.io/driver/[email protected]/sqlserver.go:90:31: invalid operation: cannot indirect limit.Limit (variable of type int)
/go/pkg/mod/gorm.io/driver/[email protected]/sqlserver.go:95:40: invalid operation: cannot indirect limit.Limit (variable of type int)

The issue is caused by this PR: #70 , which:

1. Compares the int limit.Limit to nil
2. Indirects limit.Limit, which is not a pointer

In create.go:121, db.Statement.Schema can be nil and it causes invalid memory address error

go-gorm/gorm#4845

It seems that there will be problems if there is # in the password。And The common special point is that all problems occur in MSSQL

The following is an example code:

    host:="sqlserver://sa:E3pl2021#@!@:localhost:1433"
	db, err = gorm.Open(sqlserver.Open(host), &gorm.Config{
			
		})

	if err != nil {
		log.Fatalf("Dbhost: %v, initDB failed：%s", host, err)
		return err
	}

failed to initialize database, got error

Describe the feature

Merge (upsert) queries currently require all of the columns in clause.Conflict.Columns to be primary keys, otherwise it silently falls back to a regular insert.

A table can have a primary key thats simply an auto-increment referencing the row, but also unique constraints which can trigger the ON CONFLICT case of a merge query.

Motivation

Upsert queries that aren't necessarily looking at the primary key.

	MERGE table1 AS [Target] USING (
	SELECT
	uniq_field1 = ?,
	uniq_field2   = ?,
	value = ?)
	AS [Source] ON [Target].uniq_field1 = [Source].uniq_field1 and [Target].uniq_field2 = [Source].uniq_field2
	WHEN MATCHED THEN
	UPDATE
		SET
		[Target].value=[Source].value,
		WHEN NOT MATCHED THEN
		INSERT (
			uniq_field1,
			uniq_field2,
			value)
	VALUES
		(
			[Source].uniq_field1,
			[Source].uniq_field2,
			[Source].value
		)

Related Issues

None.

Hi,

I cannot find a way to connect using a password with a # in the password.

The sqlstring is something like:
sqlserver://user:pass#word@theadress:23456?database=GENERIC"

This string would fail with an error of this type:
Failed to initialize database, got error parse sqlserver://user:pass: invalid port :pass after host.

The parsing seems to stop after the # char.

How can I workaround this?

Your Question

With the update to the driver (microsoft/go-mssqldb) I'd like to use connecting with Managed Identities. That method appears to require a connector passed to sql.OpenDB rather than the driver and DSN passed to sql.Open. Is connecting with Azure Managed Identities possible?
https://github.com/microsoft/go-mssqldb/blob/main/examples/azuread-accesstoken/managed_identity.go

The document you expected this should be explained

https://gorm.io/docs/connecting_to_the_database.html

Expected answer

Yes or No and if yes, a sample.

• https://learn.microsoft.com/zh-cn/sql/t-sql/data-types/int-bigint-smallint-and-tinyint-transact-sql?view=sql-server-ver16

Because in SQL Server, the range of values for the tinyint type is from 0 to 255, which exactly matches the range of uint8, so when field.Size < 16 and field.DataType == schema.Uint, should sqlType be tinyint? Like this:

 case schema.Int, schema.Uint:
 	var sqlType string
 	switch {
 	case field.Size < 16:
		if field.DataType == schema.Uint {
			sqlType = "tinyint"
		} else {
			sqlType = "smallint"
		}
 	case field.Size < 31:
 		sqlType = "int"
 	default:
 		sqlType = "bigint"
 	}
  
 	if field.AutoIncrement {
 		return sqlType + " IDENTITY(1,1)"
 	}
 	return sqlType

Hi, I'm trying to work with SQLServer database created with collation SQL_Latin1_General_CP1_CS_AS (case sensitive).
While migration tables, I discovered that several migrator's queries fail. After some research I found that migrator for SQL Server uses names of System Views and their columns names sometimes in uppercase, sometimes in lowercase.

Example:
in function: func (m Migrator) HasColumn written:
"SELECT count(*) FROM INFORMATION_SCHEMA.columns WHERE table_catalog = ? AND table_name = ? AND column_name = ?"

After I change it to "SELECT count(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG = ? AND TABLE_NAME = ? AND COLUMN_NAME = ?" - this query works.

Does someone did test GORM for case sensitive collation (SQL Server)?

Appreciate your fast response.

Reproduction Link

Unfortunately I cannot provide a playground link as I am on an ARM system, but here is a link to a reproduction repo.

https://github.com/selaux/gorm-sql-server-upsert-issue

Description

If I use the following code with SQL Server I expect the selected row to be updated when I call this code a second time (this is a short excerpt of the code, for the full code see the example repo above).

type TestModel struct {
	gorm.Model
	Matcher string `gorm:"uniqueIndex;size:512"`
	Data    string
}

result := db.Clauses(clause.OnConflict{
	Columns:   []clause.Column{{Name: "matcher"}},
	DoUpdates: clause.Assignments(map[string]interface{}{"age": 55}),
}).Create(&User{
	Name: "foobar",
	Age:  30,
})

Instead the following error is thown: mssql: Cannot insert duplicate key row in object 'dbo.test_models' with unique index 'idx_test_models_matcher'. The duplicate key value is (foobar).

I am aware that SQL Server does not support the ON CONFLICT clause directly, but there are existing solutions to work around this, e.g. here or here. It would be nice for GORM to either:

• Map the clause.OnConflict clause to one of those implementations
• Return an error that explicitly states that clause.OnConflict is not supported for SQL Server and add this information to GORMs documentation

Migrator().DropTable( &item{}) is not working properly when implementing item.TableName() and returning a name containing a schema, e.g:

type item struct {
	Name string
}

func (i *item)TableName() string {return "test.item"}	// specify schema name as "test"

calling db.Migrator().DropTable(&item{}) generates the following SQL statement:

DROP TABLE IF EXISTS "item"

the statement is not using the full table name, the "schema." part is missing.

What is the proper way to find the table name for a struct? - I have seen different methods throughout issues and code.

SQL Server datatype selection based on int value data size is not appropriate. Here is the condition

switch {
case field.Size < 16:
	sqlType = "smallint"
case field.Size < 31:
	sqlType = "int"
default:
	sqlType = "bigint"
}

Golang integers

• int8 : 1 byte or 8 bits
• int16: 2 bytes or 16 bits
• int32: 3 bytes or 32 bits
• int64 4 bytes or 64 bits`

SQL Server integers equivalent

• tinyint : int8
• smallint : int16
• int : int32
• biging : int64

The condition should be

switch {
case field.Size < 16:
	sqlType = "tinyint"
case field.Size < 31:
	sqlType = "smallint"
case field.Size < 64:
	sqlType = "int"
default:
	sqlType = "bigint"
}

dd

When using SQLServer Driver, i got this error

Having a table like the following, with triggers

CREATE TABLE [dbo].[test](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[test] [varchar](50) NULL,
 	CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED ( [id] ASC))
GO
CREATE TRIGGER [dbo].[triggert_test] 
	 ON  [dbo].[test]
	 AFTER  INSERT,DELETE,UPDATE
  AS 
  BEGIN
	  SET NOCOUNT ON;
  END

When using the orm with the create statement, in sql server it returns the following error.

mssql: The target table 'test' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.
[71.636ms] [rows:0] INSERT INTO "test" ("test") OUTPUT INSERTED."id" VALUES ('Prueba');

The error is due to the fact that if the table has triggers it must be used in the OUTPUT statement together with INTO

Example: INSERT INTO "test" ("test") OUTPUT INSERTED."id" into @test VALUES ('Test');

Must declare the table variable "@test"

Is it possible to fix this

https://learn.microsoft.com/en-us/archive/blogs/sqltips/output-clause-in-insertupdatedelete-statements
the sqlalchermy implementation
https://docs.sqlalchemy.org/en/20/core/connections.html#correlating-returning-rows-to-parameter-sets

Your Question

The document you expected this should be explained

Expected answer

I am using a uuid package (RFC 4122) and I am receiving a wrong data

newUser := &User{
	Email:    "[email protected]",
	UniqueID: uuid.New(),
	JoinedAt: time.Now(),
}
_ = db.Create(&newUser).Error
fmt.Println(newUser.UniqueID.String(), "Inserted Data")

521a8bc1-2641-4e6f-9a82-e8e6b15da603 Inserted Data (and database record)

user, _ := db.Where(&User{Email: "[email protected]"}).First(&user).Error
fmt.Println(user.UniqueID.String(), "Query Result")

c18b1a52-4126-6f4e-9a82-e8e6b15da603 Query Result

First blocks are looking incorrect.

Best regards

packages
github.com/google/uuid v1.2.0
gorm.io/driver/sqlserver v1.0.6
gorm.io/gorm v1.21.2

Hello,

I've noticed an issue with the way GORM maps Go's int8 and int16 types to SQL Server types. In the current implementation, int8 is mapped to smallint and int16 is mapped to int.

This mapping seems odd because int8 in Go is an 8-bit integer, and SQL Server has a matching tinyint type which is also 8 bits. Mapping int8 to smallint (which is 16 bits) seems unnecessary.

Similarly, int16 in Go is a 16-bit integer, but it's being mapped to int in SQL Server, which is a 32-bit integer. SQL Server's smallint would be a better match for int16 because it's also 16 bits.

I propose that the mapping should be changed as follows:

int8 in Go should map to tinyint in SQL Server
int16 in Go should map to smallint in SQL Server

switch {
case field.Size <= 8:
  sqlType = "tinyint"
case field.Size <= 16:
  sqlType = "smallint"
case field.Size <= 32:
  sqlType = "int"
default:
  sqlType = "bigint"
}

This would make the type mapping more intuitive and efficient, and it would prevent unnecessary widening of the integer types.

Please let me know if you need any additional information about this issue.

Thank you for your consideration.

generate struct from table fail: mssql: 在 FETCH 语句中选项 NEXT 的用法无效。

Describe the feature

The SQLserver driver for gorm does not use database schemas like (e.g.) the one for postgres. Please implement the support for schemas, especially in the migration part, for the sqlserver driver. Creating a new table within a scheme works perfect using the "TableName" interface, but when migrating it does not work anymore, as the functionality is not implemented yet in the driver.

Motivation

I want to use GO/gorm in a bigger scale project and we are forced to use MS Sqlserver as out primary database. Due to the complexity and internal restrictions, a lot of database schemas are used, so I have to use them.

Related Issues

go-gorm/gorm#4116