Tag: software

Using Claude.ai with your Database (Part 2: MCP)

In an earlier post I talked about how to educate Claude about your ERP system or any other database so that Claude is able to accurately generate SQL queries for you based on your plain English input. 

Having SQL knowledge is not necessary, Claude has your back. 

I also talked about additional advantages this can give you such as having Claude explain how a particular part of your database works, for example Stock Control, and also asking Claude to generate interactive dashboards or documentation based around the structure of your database.

I then did a follow up to this post where I discussed and made available the documentation for a script that extracts the schema information from your database and makes it available to Claude as a basic text or markdown file. This is the basis for educating Claude about your database.

At the end of the first article I mentioned that we are able to take this a step further and connect Claude directly to the data in the database. So instead of generating the queries, which you can then execute using the SQL query tool of your choice, you can instead actually retrieve the data output directly into your Claude chat session, making the use of a separate query tool unnecessary.

An additional advantage of doing this is that what Claude cannot infer from the database schema, it will try to infer or verify from the data values in the database in order to further increase its understanding, which in turn increases the quality of its output.

The key to this ability is down to something called an MCP server. Model Context Protocol is an open standard, introduced by Anthropic in late 2024, that provides a universal, standardised way for AI models to connect with external tools, applications, and other data sources. It’s a common language that allows an AI system to communicate consistently with a range of external services, regardless of who built them or how they work behind the scenes.

An MCP Server is essentially a simple, lightweight application that provides the pipe (adaptor) that connects Claude to something else, in this case, your database.

Installing and configuring MCP is beyond the scope of this article, however a few minutes with Google and the ability to execute a couple of commands in the Terminal followed by editing the Claude JSON (claude_desktop_config.json) file and the connection is made. You will of course also need to know an appropriate database logon and password and of course the hostname, database and port of your database server. 

After setting up the MCP server, restart Claude and hopefully your configuration file changes are picked up and executed successfully by Claude. Backup your claude_desktop_config.json file before editing as if your changes are unsuccessful Claude will reset it to a default state, ie: you will lose any other changes you may have made.

Note that if setting up MCP for your users or colleagues, the database logon name and password you use should only allow access to the information within the database that is appropriate for those individuals. This can be configured within the database itself in the traditional way.

Assuming you have opened Claude after you’ve made the changes and Claude hasn’t complained, go to the Settings > Developer section of the Claude desktop application and hopefully you will see something like this:

So now that Claude is successfully connected to your database, give it a whirl. 🙂

Open a new chat and input something like this:


Claude will ask for your permission to use the tools it needs. Select ‘Always allow’ or ‘Allow once’ and Claude will proceed to give you some feedback as it generates an appropriate query and retrieves the data. 

As always with any AI model, verify the results.

As you can see if you aren’t already doing this, it can be game changing for your business. Simple and fast answers to questions you have about your data using plain English instructions rather than SQL queries. 

This doesn’t mean there is no longer a need for people who have that understanding or other programming or technical abilities. In fact those are the very people who are best placed to take the most advantage from the new tools that AI has made available.

Using AI and/or MCP in combination with a programming language is one of the best ways you can seamlessly introduce the power of AI into new or existing business applications. That way you can bring the benefits of AI to your users even if they don’t have access to an AI chatbot within their work environment, giving the Head of IT the ability to control what is sent to, and received from, AI. This level of control isn’t easily available using the standard chatbots. 

If you want a high level of auditing and control over your AI and haven’t got the technical abilities in house to create your own software around AI, or if you don’t wish to go that route, and require additional security guarantees, Copilot from Microsoft can provide this level of control as well as full access to both sides of an AI conversation. Setting up a Copilot solution is something you can choose to do yourself or alternatively you can farm it out to an MSP. MS Copilot can now use Claude as well as OpenAI, however if you are choosing Copilot for its security and wish to use Claude, it’s worth getting up-to-date information on this as Claude can still process your information outside of UK/EU boundaries. Depending on the nature of your business this may or may not matter to you. The latest article I could find (a couple of days ago) is here.

Whether you are using Windows, macOS or Linux on your desktop you can setup MCP and gain the advantages I’ve talked about in this article. If any of the above even slightly interests you then have a go, as long as you start by setting up your database logon with Read Only permissions, you cannot do any harm and you never know, you might be impressed 👍

Future Articles

A forthcoming article will take this a step further and demonstrate how to get great quality, professional looking, downloadable, HTML dashboards instead of seeing the data output as text in the chatbot interface. I’ll demonstrate the techniques you can use to force the output into a design of your choice with appropriate headers and footers as well as your preferred type of charts, fonts etc. I’ll also give some tips on caching, images, models etc. in order to minimise the cost by reducing the amount of tokens used.

Another article will demonstrate some of the tools and techniques you can use to create a cross-platform compiled executable desktop application that leverages a cross-platform scripting language behind the scenes to interact with the AI. All the user sees is an app with a nice GUI.

If there is interest I will also write about how sometimes you can be inspired to create something with AI, but after a couple of hours you decide to do it all from code instead – a cheaper (to run) and sometimes better option.

Using Claude.ai with your Database (Part 1.1)

For those who messaged me asking for further information on the script that I used to create the database schema extract, I’ve made available below the documentation for that script. For those that are interested, AI was involved both in building the original script and in producing the documentation.

xSchema DocumentationDownload

Using Claude.ai with your Database (Part 1)

AI can answer a lot of questions with varying levels of accuracy, it is even pretty good at creating SQL queries, but if it doesn’t know your database then it’s a very detached and incomplete experience. In this post I’ll discuss how I overcame this and set Claude.ai up so that it understands my database and can easily create SQL queries that you can immediately try for yourself.

This has another set of advantages as well which I will talk about later in this document.

As with any AI the way to success is all about context. You need to find a way of telling the AI as much as you can about your database or in my case, our ERP system. 

You can of course construct a document that contains this information, which is a good idea and you can reuse the same document over again for future similar queries. This however becomes unwieldy if you want to ask for queries that you have not previously documented as context for the AI.

The database itself contains all the information about tables, views, indexes, foreign keys, constraints etc. that Claude will need so that it can write accurate queries for you.

Our ERP system has 1000+ tables and eleventy million other objects, there way no way I was going to even attempt to do anything manually. So I wrote a Python script and ran it against the database and the script generates a schema file containing everything the database knows about itself.

The image shows a small section of the schema file. Obviously for often used queries you can extract just the necessary information from this file and save it as a much smaller specific schema.

Now I’ve got this I just need to upload the schema file to Claude and ask it to generate a SQL query for me.

‘using the uploaded schema generate me a sql query to show me top customers by order value in 2026’

It will then give you some feedback before building the required query.

This is the query that was built by Claude:

It also added some notes:

This query was accurate and pasted into SSMS it ran first time.

I mentioned that there are other advantages of showing Claude a complete picture of your database. Once Claude has that picture you can then ask it to explain how part of the database works, or ask it to create reference documents for you to use.

Below are a three screenshots of interactive HTML tools I asked Claude to generate from the uploaded schema:

Claude will not always be 100% accurate with SQL queries that it generates and it may make assumptions about things which are not obvious from the schema file and ideally needs access to the data itself.

I’ll go through this in the next post.

Software Licensing & Piracy: Part 2

Part one of this article finished with the following question:

What is it fair to expect your customer to do to license their copy of your product, and so help protect your product, your sales, your livelihood and the future investment and development in a product that is important to both you ?

I believe that it is fair to expect the customer to do something to help the software developer protect their product, after all if the developer doesn’t stay in business then the product doesn’t have a future and the customer could be left high and dry.

That’s not to say the customer can be expected to jump through any hoops … the emphasis is on the developer to provide a method of licensing their product that can be done quickly and easily and thus encourage the customer to purchase and use a legitimate copy of the software rather than paying a visit to insert name of dodgy virus ridden download sites here or similar.

My opinion is that if I, as a user, purchase a piece of software for my own use, then I should be entitled to copy it onto my PC and my Mac and my Laptop for use whenever I like. I should also be able to copy it onto a USB Drive, connect the drive to a friends computer and use the product there also. I should not however copy the software to my friends computer thereby giving them the ability to use it in my absence. I expect to have to follow a documented procedure to identify to the software that I am the legitimate owner. This should be a simple and one off process.

I believe the previous paragraph is fair to both the customer and the software developer. I believe most people are honest and do not mind paying a reasonable price for a quality product. The software industry has in some ways become its own worse enemy with some companies having complex and unworkable, illogical and unfair licensing practices. ‘We need the customer more than they need us’ is something developers should be bearing in mind.

Offer a choice of license types and schemes that not only give the user choice, but also offer additional discounts for worthy establishments, education and charities for example.

The different types of software license that are necessary in order to give the customer the choice they require, can also bring additional and unwanted complexity. In order to also give the customer the licensing choice as well as the simplicity they desire then maybe cost additional license types at N * x. N is the Single User Standard Price, and x is the multiplier?

UPDATE IN DECEMBER 2023: Is this still an issue? It seems software companies have over the last few years decided to adopt the subscription mechanism for almost everything. This makes sense for some categories of software, but for many others it is just an unnecessary inconvenience and expense for the customer. We’ll see if this model is sustainable over the long term…

Software Licensing & Piracy: Part 1

This is an article I wrote in 2014 and which was later published in Better Software Magazine, September / October 2014 edition.

Your favourite piece of software was created by someone, or many someones, who used their time and hard won expertise to build something useful or enjoyable, or both. With the exception of Free Software or Open Source Software, that someone is entitled to, expects and deserves to be rewarded for their efforts.

If a price is attached to the software and you use the software without paying the price that is asked, then that is Software Piracy. Many would argue that it amounts to nothing less than theft.

If I sell physical products, lets say Widgets, then if I have 10 and you take 10 without paying for them, then I now have 0, you have 10 and hopefully a guilty conscience as well. That is theft. If however you use my software without paying for it then I am not directly affected by it. I still have it. Would you have bought it if you couldn’t have obtained a pirate copy ? Maybe. Maybe Not. In my opinion that is why Software Piracy differs from traditional theft. What you have actually done is taken away my chance of receiving income from you for that software sometime in the future. It just isn’t clear cut either way, with laws, policies and attitudes being firmly rooted in the ‘pre digital media’ 20th century.

Having established that there is a cost to producing software and that the developer does deserve to be rewarded for their time and efforts, it stands to reason that we have to have a mechanism in place that makes this possible. That mechanism is generally known as Software Licensing.

I’ve been developing software for financial reward for nearly as long as I’ve been using computers, well over thirty years, and I have never wavered in my belief that whatever form of licensing you use, you should never punish the genuine, fee paying customer for the actions of the Software Pirate.

Seriously, I have bought software in the past that had licensing schemes so restricting or complex or time consuming (or just plain ridiculous), that I have saved time and effort by downloading a cracked copy of the software and using that instead, all the time cursing the software developers for making me waste my time and effort. In effect punishing me for my honesty. As a Software Developer or Software Publisher that’s not an experience you want for your customers.

Whatever Software Licensing mechanism is used, there will always be some who do not like it and resent it being used. If as a developer you have done your best to minimise the impact on the genuine customer, whilst making some effort to thwart the Software Pirate, then you have done all you can and any customer who is going kick up a fuss about your licensing mechanism is unlikely to be a customer you actually want. Most customers would actually like you to stay in business and realise that to do that, it is necessary to be paid for the work you do, and / or the products you sell. Sacking your customer can sometimes be a good thing. But that’s a subject for another blog post 🙂

So what is a fair software license, for both the supplier and the customer ? What is it fair to expect your customer to do to license their copy of your product, and so help protect your product, your sales, your livelihood and the future investment and development in a product that is important to both you ?

I’ll continue this in the next post.