graphql-engine: SQL Server Incorrect syntax Exception
Hello,
I really appreciate the graphql-engine project and i am really excited to try it out. I am trying to run the project on docker for MS Sql Server. I have a remote database server and I was able to connect to the server using the connection string:
Driver={ODBC Driver 17 for SQL Server}; Server=remoteurl,1433;Database=tempdb;Uid=sa;pwd=password and I am using
hasura/graphql-engine:v2.0.0-alpha.11 version.
When I try to query the object after tracking it, I get following error:
{
"errors": [
{
"extensions": {
"internal": {
"tag": "unsuccessful_return_code",
"contents": [
"odbc_SQLExecDirectW",
-1,
"[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'AS'.[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'AS'.o"
]
},
"path": "$",
"code": "unexpected"
},
"message": "sql server exception"
}
]
}
I am using the following docker-compose file:
version: '3.6'
services:
postgres:
image: postgres:12
restart: always
volumes:
- db_data:/var/lib/postgresql/data
environment:
POSTGRES_PASSWORD: postgrespassword
graphql-engine:
image: hasura/graphql-engine:v2.0.0-alpha.11
ports:
- "8080:8080"
depends_on:
- "postgres"
restart: always
environment:
HASURA_GRAPHQL_DATABASE_URL: postgres://postgres:postgrespassword@postgres:5432/postgres
## enable the console served by server
HASURA_GRAPHQL_ENABLE_CONSOLE: "true" # set to "false" to disable console
## enable debugging mode. It is recommended to disable this in production
HASURA_GRAPHQL_DEV_MODE: "true"
HASURA_GRAPHQL_ENABLED_LOG_TYPES: startup, http-log, webhook-log, websocket-log, query-log
## uncomment next line to set an admin secret
# HASURA_GRAPHQL_ADMIN_SECRET: myadminsecretkey
volumes:
db_data:
Is there any configuration that I am missing? I expect since the graphql-engine is running inside docker I don’t have to install it on my local. Do I need to add any config for SQL server in the docker-compose yml file? i am using connect database option to add database source to a remote SQL server instance.
Appreciate it!
About this issue
- Original URL
- State: closed
- Created 3 years ago
- Comments: 22 (8 by maintainers)
Hey folks, with
beta.1, we’ve changed the query generation logic to not useOPENJSON WITH, so queries should work fine. However, subscriptions requireOPENJSON, so you may see runtime errors with ‘sql server exception’. If this happens, check theCOMPATIBILITY_LEVELof your database as follows (OPENJSONrequiresCOMPATIBILITY_LEVELof at least130):If it is lower than
130, you’ll need to change thecompatibility_levelof your database to getOPENJSONand hence subscriptions working. Please let us know if subscriptions fail to work even after settingCOMPATIBILITY_LEVELto130or more.Tnks man. Its solved my problem with subscription errors.
I’m closing this issue as this should be resolved with
beta.1. However please feel free to open a new issue or reopen this if you still face this error.I just tested OPENJSON (@json) in mine and I didn’t get the hasura error, but I did have to alter the database compatibility to get OPENJSON to work. That being said, I was getting an invalid object error and not Incorrect Syntax Exception.
ALTER DATABASE db SET COMPATIBILITY_LEVEL = 140;