SQL Server integration?

[vlasvlasvlas]

Hi! This project is amazing, thanks!

Could this be compatible with ms-sqlserver? what strategy do you suggest?

Thanks!

[app-generator]

Hello @vlasvlasvlas,

The project uses SqlAchemy ORM as DB interface. To use ms-sqlserver, please follow this simple set up:

• make sure you have MsSql up & running in your environment
• Install the pymssql driver
• Update the project configuration to use it - config.py

Let us know the results. P.S. Thank you for using our projects.

[vlasvlasvlas]

thanks a lot! ill test it and let you know :)

[vlasvlasvlas]

Hi, i tested and got an error (pymssql.OperationalError: (20002, 'DB-Lib error message 20002, severity 9:\nAdaptive Server connection failed )

i installed pymssql and this is what i did with config.py

SQLALCHEMY_DATABASE_URI = '{}://{}:{}@{}:{}/{}'.format(
    config( 'DB_ENGINE'   , default='SQL Server'    ),
    config( 'DB_USERNAME' , default='sa'       ),
    config( 'DB_PASS'     , default='sqlrealpassword'          ),
    config( 'DB_HOST'     , default='realipserver'     ),
    config( 'DB_PORT'     , default=1433            ),
    config( 'DB_NAME'     , default='realdatabasename' )
)

mm or i dunno if i must put "''mssql+pyodbc" or "mssql+pymssql" on db_engine ,,

UPDATE 1:

Ok, i used pypyodbc making a conntest.py and it works.

Do you know if i can implement the following connection within .env / config.py?

import pypyodbc
import sqlalchemy
sqlalchemy.create_engine("mssql+pyodbc:///?odbc_connect=Driver=FreeTDS;Servername=REALSERVERNAME;port=1433;uid=REALUSER;pwd=REALPWDe;database=REALDB;TDS_Version=8.0;",module=pypyodbc,echo=True)

UPDATE 2:

Do i need an specific version of pysmssql? i do get some errors. i found that maybe i need an specific pymssql version (2.1.1)?

UPDATE 3: also got same error "(pymssql.OperationalError: (20002, 'DB-Lib error message 20002, severity 9:\nAdaptive Server connection failed )" within same docker image with this conn line:

import pymssql
conn = pymssql.connect(server="HOSTIP",port=1433,user="REALUSER",password="REALPWD",database="REALDB")

Thanks! :)

[vlasvlasvlas]

UPDATE 4:

I successfuly used pymssql by hardcoding conn engine string.

But now i have the following issue when the create user table is executed:

sqlalchemy.exc.OperationalError: (pymssql._pymssql.OperationalError) (1919, b"Column 'email' in table 'User' is of a type that is invalid for use as a key column in an index.DB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\n") [SQL: CREATE TABLE [User] ( id INTEGER NOT NULL IDENTITY(1,1), username VARCHAR(max) NULL, email VARCHAR(max) NULL, password VARBINARY(max) NULL, PRIMARY KEY (id), UNIQUE (username), UNIQUE (email) )

]

I think there's an issue inside the create table (here's an sqlserver2019) with the varchar(MAX) and nvarchar(MAX) used in indices.

I change models.py lines:

username = Column(String(256), unique=True) email = Column(String(256), unique=True)

but then i still got more errors (inserting, and login)

[app-generator]

Hello Vladimiro,

Try to limit the VARCHAR fields to 900:

"username VARCHAR(900) NULL, email VARCHAR(900) NULL, "

More information regarding this fix, you can find here: https://stackoverflow.com/questions/2863993/is-of-a-type-that-is-invalid-for-use-as-a-key-column-in-an-index

Let us know if you have further issues.

AppSeed.us Support

On Thu, 15 Apr 2021 at 05:04, Vladimiro Bellini @.***> wrote:

UPDATE 4:

I successfuly used pymssql hardcoding the create engine string.

But now i have the following issue when the create user table is executed:

sqlalchemy.exc.OperationalError: (pymssql._pymssql.OperationalError) (1919, b"Column 'email' in table 'User' is of a type that is invalid for use as a key column in an index.DB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\n") [SQL: CREATE TABLE [User] ( id INTEGER NOT NULL IDENTITY(1,1), username VARCHAR(max) NULL, email VARCHAR(max) NULL, password VARBINARY(max) NULL, PRIMARY KEY (id), UNIQUE (username), UNIQUE (email) )

]

I think there's an issue inside the create table (here's an sqlserver2019) with the varchar(MAX) and nvarchar(MAX) used in indices.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/app-generator/flask-dashboard-adminlte/issues/3#issuecomment-819981846, or unsubscribe https://github.com/notifications/unsubscribe-auth/AMFUJGF73MTMXTNNN7IUPX3TIZCUDANCNFSM42ZOT33A .

[vlasvlasvlas]

Hi! yes! i made that change at models.py,

i got a new one thats at the insert step:

for this, i did a manual sql insert and then i could login:

INSERT INTO [User] (username, email, password) VALUES ('admin', '[email protected]', convert(varbinary(max),'1a656bc4d631781429dbc6060546bda5df32bb475fb8fd21e8d86bf339f08372ece4bdefac18f9248edb478f06c5d7ac9ec4337ba09da845ae5d8acbbbd8e890e0d131a040ed0f2144f6fada7a535e6ee52915adbceba2321c7d6ca43560d67b') )

But i do need to get the insert done from the code. I think its about the convert( varbinary(max) thing that the code is just inserting string without converting to varbinary(max).

thanks a lot!