tire0011 commented 1 year ago

System Info

langchain version 0.0.198, windows, SQL Server 16.0.4025.1 windows docker container (linux)

In the output I get the message: Incorrect syntax near the keyword 'TO'

and program not ends correctly

Who can help?

No response

Information

[ ] The official example notebooks/scripts
[X] My own modified scripts

Related Components

[X] LLMs/Chat Models
[ ] Embedding Models
[ ] Prompts / Prompt Templates / Prompt Selectors
[ ] Output Parsers
[ ] Document Loaders
[ ] Vector Stores / Retrievers
[ ] Memory
[ ] Agents / Agent Executors
[ ] Tools / Toolkits
[ ] Chains
[ ] Callbacks/Tracing
[ ] Async

Reproduction

my python program:

server = 'localhost,1435' database = 'MyDB' username = 'me' pwd = '****' driver= 'ODBC Driver 17 for SQL Server'

def db_instance():

Creating SQLAlchemy connection sting

connectionString = 'DRIVER='+driver+';SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ pwd+';Encrypt=no;TrustServerCertificate=no;Connection Timeout=30;'
print(connectionString)
params = urllib.parse.quote_plus(connectionString)
conn_str = 'mssql+pyodbc:///?odbc_connect={}'.format(params)
db_instance = SQLDatabase.from_uri(conn_str,schema='ALG')
return db_instance

db = db_instance()

print(db.table_info)

Setting API Key and API endpoint for OpenAI

os.environ['OPENAI_API_TOKEN'] = '....'

llm = OpenAI(model_name='text-davinci-003')

LangChain Agent

toolkit = SQLDatabaseToolkit(db=db, llm = llm)

agent_executor = create_sql_agent( llm= llm, toolkit=toolkit, verbose=True, top_k = 5 )

Test

agent_executor.run("'how many Messages are in the DB'")

Expected behavior

Output from terminal: SELECT Message_0_0., Message_0_100001., Message_0_100002., Message_0_100003., Message_0_100004., Message_0_100000., Message_0_99999. FROM Message_0_0 INNER JOIN Message_0_100001 ON Message_0_0.id = Message_0_100001.id INNER JOIN Message_0_100002 ON Message_0_0.id = Message_0_100002.id INNER JOIN Message_0_100003 ON Message_0_0.id = Message_0_100003.id INNER JOIN Message_0_100004 ON Message_0_0.id = Message_0_100004.id INNER JOIN Message_0_100000 ON Message_0_0.id = Message_0_100000.id INNER JOIN Message_0_99999 ON Message_0_0.id = Message_0_99999.id Thought: The query looks correct, I can now execute it. Action: query_sql_db Action Input: SELECT Message_0_0., Message_0_100001., Message_0_100002., Message_0_100003., Message_0_100004., Message_0_100000., Message_0_99999. FROM Message_0_0 INNER JOIN Message_0_100001 ON Message_0_0.id = Message_0_100001.id INNER JOIN Message_0_100002 ON Message_0_0.id = Message_0_100002.id INNER JOIN Message_0_100003 ON Message_0_0.id = Message_0_100003.id INNER JOIN Message_0_100004 ON Message_0_0.id = Message_0_100004.id INNER JOIN Message_0_100000 ON Message_0_0.id = Message_0_100000.id INNER JOIN Message_0_99999 ON Message_0_0.id = Message_0_99999.id Obs Observation: Error: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'TO'. (156) (SQLExecDirectW)") [SQL: SET search_path TO ALG] (Background on this error at: https://sqlalche.me/e/20/f405) Thought: I should double check my query again with the query checker. Action: query_checker_sql_db Action Input: SELECT Message_0_0., Message_0_100001., Message_0_100002., Message_0_100003., Message_0_100004., Message_0_100000., Message_0_99999.* FROM Message_0_0 INNER JOIN Message_0_100001 ON Message_0_0.id = Message_0_100001.id INNER JOIN Message_0_100002 ON Message_0_0.id = Message_0_100002.id INNER JOIN Message_0_100003 ON Message_0_0.id = Message_0_100003.id INNER JOIN Message_0_100004 ON Message_0_0.id = Message_0_100004.id INNER JOIN Message_0_100000 ON Message_0_0.id = Message_0_100000.id INNER JOIN Message_0_99999 ON Message_0_0.id = Message_0_99

The message query looks correct it should get the correct count. SQL server trace contains also the query: SET search_path TO ALG

I found that string in the github repo under /sql_database.py line 347

        with self._engine.begin() as connection:
            if self._schema is not None:
                if self.dialect == "snowflake":
                    connection.exec_driver_sql(
                        f"ALTER SESSION SET search_path='{self._schema}'"
                    )
                elif self.dialect == "bigquery":
                    connection.exec_driver_sql(f"SET @@dataset_id='{self._schema}'")
                else:
                    connection.exec_driver_sql(f"SET search_path TO {self._schema}")

devstein commented 1 year ago

Generated by a 🤖

Answer

The issue is caused by the line connection.exec_driver_sql(f"SET search_path TO {self._schema}") in the sql_database.py file, which is not valid for SQL Server. To fix this issue, modify the sql_database.py file and replace the lines:

else:
    connection.exec_driver_sql(f"SET search_path TO {self._schema}")

with:

elif self.dialect == "postgresql":
    connection.exec_driver_sql(f"SET search_path TO {self._schema}")
elif self.dialect == "mssql":
    connection.exec_driver_sql(f"USE {self._schema}")

This change will ensure that the correct query is executed for SQL Server, which is "USE {self._schema}" instead of "SET search_path TO {self._schema}".

Sources

langchain/tools/sql_database/prompt.py

This response is meant to be helpful and save users time. It is not meant to be a precise solution.

Please leave a 👍 if this is helpful and 👎 if it is irrelevant

anujmehta commented 1 year ago

Agree, need a fix for MS SQL server at https://github.com/hwchase17/langchain/blob/master/langchain/sql_database.py#L347

Rajlakshmi0187 commented 1 year ago

Hello all, I am getting the same error while executing the sql agent executor. Now I am not using the sql_database.py file, I have individual notebook where I am building my code, connecting to SQL server. So, can anyone suggest, how do I update below: elif self.dialect == "mssql": connection.exec_driver_sql(f"USE {self._schema}")

PastorJordi commented 1 year ago

@Rajlakshmi0187 I am not really fluent using sqlalchemy. However, it looks as if USE must be followed by a database rather than a schema. Replacing connection.exec_driver_sql(f"USE {self._schema}") with connection.exec_driver_sql(f"USE {self._engine.url.database}") solved the issue in my case.

dosubot[bot] commented 1 year ago

Hi, @tire0011! I'm Dosu, and I'm helping the LangChain team manage their backlog. I wanted to let you know that we are marking this issue as stale.

Based on my understanding, the issue you reported is related to an error message "Incorrect syntax near the keyword 'TO'" when executing a query in the program. There have been some suggestions in the comments on how to fix this issue, such as modifying the sql_database.py file by replacing the line connection.exec_driver_sql(f\"SET search_path TO {self._schema}\") with connection.exec_driver_sql(f\"USE {self._schema}\") for SQL Server. This solution was suggested by devstein and agreed upon by anujmehta. Additionally, PastorJordi suggested replacing connection.exec_driver_sql(f\"USE {self._schema}\") with connection.exec_driver_sql(f\"USE {self._engine.url.database}\") to solve the issue.

Before we proceed further, could you please confirm if this issue is still relevant to the latest version of the LangChain repository? If it is, please let us know by commenting on this issue. Otherwise, feel free to close the issue yourself or it will be automatically closed in 7 days.

Thank you for your contribution to the LangChain repository, and please let us know if you have any further questions or concerns!

langchain-ai / langchain

Incorrect syntax near the keyword 'TO' #6105