SET search_path TO {self._schema} is executing by SQLDatabase for all databases except 'snowflake' and 'bigquery'.

[mail2mhossain]

System Info

LangChain Version- 0.0.235, Windows, Python Version-3.9.16

As per below source code of SQLDatabase, before executing any sql query, connection.exec_driver_sql(f"SET search_path TO {self._schema}") is executed for all database except 'snowflake' and 'bigquery'.

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}")
cursor = connection.execute(text(command))

As per my knowledge, The SET search_path command is specific to PostgreSQL, not Oracle. This is why I am getting the following error.
sqlalchemy.exc.DatabaseError: (oracledb.exceptions.DatabaseError) ORA-00922: missing or invalid option
[SQL: SET search_path TO evr1]
(Background on this error at: https://sqlalche.me/e/20/4xp6)

Oracle does not recognize this command.

I think it is better to use SCHEMA.TABLE in all sql queries.

Who can help?

No response

Information

• The official example notebooks/scripts
• My own modified scripts

Related Components

• 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

oracle_connection_str = f"oracle+oracledb://{username}:{password}@{hostname}:{port}/?service_name={service_name}"
db = SQLDatabase.from_uri(
oracle_connection_str,
schema="evr1",
include_tables=[ ],
sample_rows_in_table_info=3,
)

llm = ChatOpenAI(model_name=GPT_MODEL, temperature=0, openai_api_key=OpenAI_API_KEY)

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

agent_executor = create_sql_agent(
llm=llm,
toolkit=toolkit,
agent_type=AgentType.ZERO_SHOT_REACT_DESCRIPTION,
return_intermediate_steps=True,
handle_parsing_errors=_handle_error,
verbose=True,
)

response = agent_executor(user_input)

Expected behavior

Should not execute SET search_path TO {self._schema} for Oracle

[dosubot]

Answer generated by a 🤖

Answer

I understand that you're encountering an issue with the SQLDatabase class in LangChain when using Oracle as your database. The problem arises because the command SET search_path TO {self._schema} is specific to PostgreSQL and is not recognized by Oracle. The current implementation of SQLDatabase does not account for Oracle's specific syntax, which is causing the error you're experiencing.

To address this issue, I suggest modifying the SQLDatabase class to include a condition for Oracle, similar to how it's done for 'snowflake' and 'bigquery'. Here's how you can adjust the code:

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}'")
    elif self.dialect == "oracle":
        # Oracle specific command to set schema
        connection.exec_driver_sql(f"ALTER SESSION SET CURRENT_SCHEMA = {self._schema}")
    else:
        connection.exec_driver_sql(f"SET search_path TO {self._schema}")
cursor = connection.execute(text(command))

In this revised code, I've added a condition for 'oracle' and used the ALTER SESSION SET CURRENT_SCHEMA = {self._schema} command, which is Oracle's equivalent of setting the search path. This modification should help resolve the issue you're facing.

Please note that this is one potential solution based on the information provided. If this doesn't work or if you encounter other issues, there might be other factors at play that we would need to consider.

If you find this solution correct and believe this is a bug that could impact other users, I encourage you to make a pull request with this change. This would greatly benefit the LangChain community.

If you're still experiencing this issue after trying this solution, it might be helpful to ensure you're using the latest version of LangChain, as updates often contain bug fixes and improvements.

Sources

• tests/unit_tests/test_sql_database_schema.py

---

This response is meant to be useful, save you time, and share context. It is not meant to be a precise solution, but rather a starting point for your own research.

Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant.

[mail2mhossain]

I have pull the source BUT did not get the changes.

[cccs-eric]

I ran into the same issue using Trino as my sql engine. Trino does not support SET search_path TO and this prevents any query from running. The following PR provides a fix for Trino

[dosubot]

Hi, @mail2mhossain,

I'm helping the LangChain team manage their backlog and am marking this issue as stale. From what I understand, you encountered an error related to the SET search_path command with Oracle databases, which is specific to PostgreSQL. There was a suggestion to use SCHEMA.TABLE in all SQL queries instead, and a potential solution was provided by modifying the SQLDatabase class to include a condition for Oracle and using the ALTER SESSION SET CURRENT_SCHEMA = {self._schema} command. Additionally, there was a related pull request for Trino support shared.

Could you please confirm if this issue is still relevant to the latest version of the LangChain repository? If it is, please let the LangChain team know by commenting on the issue. Otherwise, feel free to close the issue yourself, or it will be automatically closed in 7 days. Thank you!