I’m working on an n8n workflow to create an AI assistant that can answer questions from a Microsoft SQL database. The connection to the database is set up and working fine. My problem is figuring out how to let the AI generate the SQL query and use it in the Microsoft SQL node.
I’ve looked through the docs but can’t find any clear info on what to put in the Query parameter for this to work. I thought it might be something simple like {{ $fromAI('<some_parameter>') }}, but I’m not sure.
For context, I successfully made a similar agent with Supabase, which doesn’t need a query parameter. With the MS-SQL setup, I’ve tried leaving it blank, using empty quotes, and a few variations of $fromAI, but nothing seems to work.
Has anyone figured out the right input for the Query field to make this work? I’m really stuck and could use some guidance.
hey elizabeths, try using a variable node before the MS SQL node. Set the variable to your AI-generated query, then in the Query field use something like {{$node[‘Variable’].json[‘aiGeneratedQuery’]}}. This way n8n can dynamically insert the AI query. hope that helps!
I’ve encountered this issue before, and there’s a straightforward solution. Use a Function node before your Microsoft SQL node to process the AI-generated query. In the Function node, manipulate the query as needed and assign it to a variable. Then, in the MS SQL node’s Query field, reference that variable using n8n’s expression syntax.
Here’s a quick example of what the Function node might look like:
const aiQuery = items[0].json.aiGeneratedQuery;
const processedQuery = aiQuery.trim();
items[0].json.processedQuery = processedQuery;
In the MS SQL node, set the Query field to:
={{$node[“Function”].json[“processedQuery”]}}
This approach allows for flexible query handling, while enabling error checking and validation of the AI-generated queries before execution.
I’ve actually tackled a similar challenge in my work recently. From my experience, the key is to use an Expression node before your Microsoft SQL node. In the Expression node, you can set up your AI-generated query as a variable. Then, in the MS SQL node’s Query field, you’d reference that variable.
For example, in the Expression node, you might have something like:
const aiQuery = $input.item.aiGeneratedQuery; return { aiQuery };
Then in your MS SQL node, the Query field would look like:
={{$node["Expression"].json["aiQuery"]}}
This approach gives you more flexibility and control over how the AI-generated query is processed before it hits your database. It also allows for any necessary error handling or query modification if needed. Just make sure your AI output is properly sanitized to prevent any SQL injection risks.