HOW-TO

  • Getting Started
    • AIMMS Basics
      • Getting Started Tutorials
        • Tutorial for Beginners
      • Keyboard shortcuts for AIMMS
      • Share an AIMMS Project
      • Install AIMMS in another location
      • Overview: Create a reliable and successful AIMMS application
      • Add AIMMS Libraries
      • Encrypt Projects to Protect Intellectual Property
      • Overview: Model Sources, Version Control and aimmspack Files
    • Program Settings
      • Export Code to Another Project
      • Display Line Numbers in the Editor
      • Change Syntax Highlighting and Display Options
      • Set Encoding Format
      • Adjust Engine and Solver Settings
    • Licensing
      • Install single user developer license
      • Transfer a Developer License to Another Machine
      • Resolve License Error: Nodelock file is read-only
      • Resolve License Issues
      • How to link a solver to AIMMS
      • Activate Machine Nodelock Offline
      • Licensing using Network License Server
        • Configure a Network License
        • Register Named Users in Network License Manager
        • Install the Network License on a client computer
        • Migrate the AIMMS PRO License Server
  • AIMMS Developer
    • Data Connection
      • Databases
        • Which ODBC Drivers
        • Link an SQLite Database to a Project
        • Install Side-by-Side Drivers
        • Connection string for Access database files
        • Write Selected Database Tables
        • Write to a Database Efficiently
        • Write data to a table
        • Adapt model when linked database table is modified
        • Exchange data with the AIMMS Cloud Platform
        • Reading relations and indexed sets from databases
        • DirectSQL Example
        • Create a static AIMMS Library from a runtime library
      • Excel and CSV
        • AIMMS Excel Library - AXLL
        • Read Columns of Data from Excel
        • Read and Write CSV Files
        • Determine Data Ranges in Excel Files
      • HTTP Library
        • Overview: HTTP Client Library
        • Extract Data from an XML File
        • Extract XML File from a Server with the HTTP Library
        • Retrieve Geographic Data with Google Maps API
        • Retrieve Geographic coordinates with Google Maps API
        • Extract Photos Using the Flickr API
        • Convert Audio File with IBM API
        • Use the IBM Image Recognition API
        • Integrate AIMMS with Models Built in Languages like Python or R
        • Integrate AIMMS with Python
        • Integrate AIMMS with R
      • Data Exchange Library
        • How to integrate the Data Exchange Library
        • How to use the Data Exchange Library for extracting files and data
        • How to create mappings for matrix-oriented data via name-binds-to
        • Reading slices of data via ExternalBinding mapping element
        • Dealing with different data types in the DEX
        • OpenAPI
      • Other Functions
        • Create Email Templates
        • Overview: Email Client Library
        • Create an External Function with Visual Studio
        • Exchange Data with External Source
        • Run Executable Files
        • Run Batch Commands with AIMMS Command Line Tool
        • Link AIMMS with Tableau
        • Connect AIMMS with Gurobi
        • Overview: Linking to AIMMS
    • Mathematical Modeling
      • Adapt Solve Procedure with Callbacks for GMP
      • Color a Map with Constraint Programming
      • Use Constraint Programming to Solve ROGO Puzzle
      • Optimize Shift Schedules
      • Reduce the Time Window for Scheduling Jobs
      • Optimize Scheduling for Project Planning
      • Use a Multi-Objective Approach
      • Minimize Objective Containing a min Operator
      • Model Variables with Limited Values
      • Various (integer) linear modeling tricks
      • Model a Rounded Variable
      • Debug an Infeasible Model
      • Speed Up MIP Solve
      • Providing an Initial Solution to MIP Solvers
      • Data for Optimization Libaries
      • Nonlinear Presolve Algorithm in AIMMS
      • AIMMS Outer Approximation Algorithm for MINLP (using GMP functionality)
      • Solving convex MINLP problems with AIMMS
      • Vehicle Routing
        • Capacitated Vehicle Routing Problem Library
        • Capacitated Vehicle Routing Problem formulation
        • Explicit Dantzig-Fulkerson-Johnson formulation
        • Implicit Dantzig-Fulkerson-Johnson formulation
        • Miller-Tucker-Zemlin formulation
        • Time Windows
        • Testing the Library
    • AIMMS Language
      • AIMMS Language Basics
        • Overview: Set, Subset, Index and Element Parameter
        • Identifier Naming Convention
        • Application initialization and termination with libraries
        • Standardize Random Data in Distribution Functions
        • Using LoopCount instead of explicit counting parameter in loops
        • Solve in a Loop
        • Local Binding vs. Default Binding
        • Model category mapping of items
        • Monitor Identifiers for Changes
        • Convert string to numerical value
        • Write and Read AIMMS Data in Text Format
        • Interrupt Execution During a Single Statement
        • Generate random numbers
        • Get Name of an AIMMS Case
        • Check Computations with Units of Measurement
        • Localize Units of Measurement per User
        • Save a Case from an AIMMS Procedure
        • Identify differences
        • Finding unused identifiers
      • AIMMS sets
        • Composite objects
        • Representing time
        • Element after Last
        • Handling of Integer Sets and Operators
        • Select Constraints and Variables for a Math Program Declaration
        • Multiple indices for a set
        • Overview: types of Set
        • Overview: NBest Operator
        • Remove elements from a set
      • Execution Efficiency
        • Analyze Long Execution Time
        • Measure Execution Time
        • Optimize Execution Time
        • Reduce Memory Usage
        • Investigate Memory in Use
        • Monitor Memory Usage and Leaks
      • Libraries
        • Library of functions and procedures
        • Test Driven Development using the AIMMSUnitTest Library
        • Retrieve Value of Dynamic Identifier
        • Repeat Data with Model Query and Model Edit
        • Use Formulas as Data
        • Runtime functions with arguments
        • Create a static AIMMS Library from a runtime library
      • Solvers
        • Change the Frequency of Progress Updates
        • Retrieve Solver Log Files in AIMMS Developer
        • Retrieve Solver Log Files in AIMMS PRO
        • Overview: Generated Mathematical Programs
        • Set Stop Criteria for Math Programs
        • Generate Multiple Solutions with CP Optimizer
        • Use Alternative MIP Solutions with CPLEX Solution Pool
        • Automatic Benders’ decomposition
        • Solve with Benders Decomposition in CPLEX
        • Solve with Lazy Constraints
        • Debug Infeasible or Unbounded Results
        • Change the Default Solvers Used
        • Create Local Solver Configuration
        • Solve MINLP problems with AOA
        • Check if variable values satisfy constraints
        • Use a Multi-Objective Approach
        • Tune CPLEX Options from AIMMS
        • Solve in parallel with Asynchronous Solver Sessions
        • Adding a Solver
    • Error Handling
      • Get Log Files
      • Get More Log Information
      • Handle errors and warnings
      • Customize Settings for Errors and Warnings
      • Identify error
      • Configuring the logging of sessions ran on a AIMMS PRO on Premise
      • Download Server Session Log
      • Tracing procedures
      • In model tracing
      • Sending a dump file?
      • What is a dump file?
      • Trouble shooting
        • Openstreetmap images missing from network object
        • Academic and community licenses: No logon response from server
        • Storing project in One-Drive: A file cannot be created when it already exists
        • Empty WebUI after upgrade
        • Sudden Microsoft Access reporting: “make sure this is a valid datasource”
        • Error message “tlsv1 alert protocol version”
        • Writing to a Snowflake database
  • Application UI
    • UI Features
      • Showing progress of long running algorithms
      • Working with Wonderful WebUI Widgets
      • Create Wonderful WebUI Widgets
      • The iFrame widget
      • Create Forms in WebUI
      • Zoom and Scroll in a Gantt Chart
      • Sailing across the world
      • Add Compressed Files for User Download
      • Selecting Pages for Workflow Panels
      • Combine Bar Chart and Line Chart
      • Comparing schedules from scenarios
      • Add qualitative color palettes
      • Consistent data coloring on an AIMMS WebUI page
      • Refreshing an Image widget without changing the file name
      • Hiding Default Values
      • Using Scalar Border Annotation
      • Disable Standard WebUI Functions
    • Styling
      • Change Widget Styles with CSS
      • Adding a logo to an AIMMS WebUI app
      • Change Hover and Select Effects for WebUI Charts
      • Display an On/Off Switch in Widgets
      • Adding an Icon on your Button
      • Adding an Icon on your Menu Bar
      • Dos and Don’ts
      • Best Practices in your Application
  • Deployment
    • PRO Basics
      • Size AIMMS PRO Server Hardware
      • Publishing to offline servers
      • Create PRO User Groups
      • Understanding Concurrent Usage on AIMMS PRO/Cloud
      • Overview: Best Practices for Operating AIMMS PRO
      • Send and Receive Messages
      • Schedule Regular Jobs
      • Upload and Download Files on AIMMS PRO
      • Secure File Access
      • Copy Folder Operations in PRO Storage
      • Change AIMMS PRO account
      • Number of running and pending solver sessions
      • Migrate PRO Server to Another Machine
      • Troubleshoot Unresponsive Applications
      • Scaling AIMMS PRO
      • Protect AIMMS PRO installation for business continuity
      • Waiting for sub jobs to complete
      • Investigate Behavior of AIMMS PRO Job
    • Cloud Platform Basics
      • Exchange data with the AIMMS Cloud Platform
    • Client-Server Communication
      • Create responding applications
      • Deploy an Application on AIMMS PRO
      • Develop Multi-Platform Applications
      • Keep WebUI Active During Solve
      • Load solver session Results Manually
      • Use the Progress Window in WebUI
      • Show Intermediate Solutions
      • Interrupt the solver session
      • Modifying Session Timeout Duration for WebUI Apps
      • Communicate Data Changes in WebUI
      • Exporting aimmspack for WinUI or WebUI
      • Reduce Exchange Between Client Session and solver session
    • AIMMS PRO API
      • Start a Job via PRO API using Java
      • Start a Job via PRO API using C#
    • Collaborative Data Management (CDM)
      • Install CDM Service
      • Log CDM
  • Software Evolution
    • Evolution of AIMMS using Transport Layer Security
    • Convert a GAMS Model or AIMMS 3 model to an AIMMS 4 Model
    • Overview: Deprecation of Compound Sets
    • Prepare for the Deprecation of Compound Sets
    • Convert Files from DAT to DATA
    • Using UNICODE in maintained AIMMS applications
    • Upgrade WebUI Page Actions
    • Upgrade Old WebUI Maps
    • Upgrade an AIMMS PRO Installation
    • Check Version Dependencies for AIMMS Developer and AIMMS PRO
    • Update WebUI to newer AIMMS version
    • Unsupported browser
    • WinUI
      • Align Objects in WinUI
      • Change Views of 2D Charts in a WinUI Page
      • Enable the Page Manager
      • Add a Custom Startup Image in WinUI
      • More logging of WinUI Published Applications
  • Examples
    • Examples vs WebUI Widgets
    • Application Examples
      • Car Selection
      • Circle Packing
      • Contract Allocation
      • Data Reconciliation
      • Distribution Center Allocation
      • Economic Exchange Equilibrium
      • Employee Scheduling
      • Flow Shop
      • Gate Assignment problem
      • Knapsack Problem
      • Life Cycle Consumption
      • Project Analysis
      • Railway Stock
      • Reindeer Pairing
      • Sudoku
      • Traffic Equilibrium
      • Transport Model
      • Traveling Salesman
    • Functional Examples
      • 3D Chart
      • AIMMS API
      • Arcs and Nodes
      • Calendar
      • Camping
      • Chance Constraints
      • Coloring
      • Create CSV File
      • Database Inspection
      • Database Interface Generation
      • Data Feed
      • Date Aggregation
      • Demand Forecasting
      • Distributed Solver Sessions
      • Distributions
      • Email and Sound Support
      • Error Handling
      • Experiment
      • External Procedure
      • Gantt Chart
      • GIS Support
      • Goal Programming
      • Horizon
      • Indexed Page
      • ipTwist
      • Interrupt AIMMS Externally
      • Linked Objects
      • Model Edit API
      • Model Edit Functions
      • MPS Functions
      • Multi Start
      • Nested Solve
      • Oil Well Drilling
      • Page Resizability
      • Pivot Table
      • Power System Expansion RO
      • Print Pages
      • Rolling Horizon
      • Stochastic Programming
      • Tea Consumption
    • Modeling Book Examples
      • Bandwidth Allocation
      • Cutting Stock
      • Diet Problem
      • Employee Training
      • Facility Location Choice
      • Farm Planning
      • File Merge
      • Inventory Control
      • Investment Portfolio Selection
      • Media Selection
      • Performance Assessment
      • Power System Expansion
      • Refinery Pooling Planning
      • Telecommunication Network Design
      • Two Level Decision
    • Practical Examples
      • Chemical Engineering: Blending problem
      • Chemical Engineering: Chemical Equilibrium problem
      • Energy: Oil pipeline design
      • Energy: Water Distribution Network Design
      • Power Systems Planning - Methods and Applications
      • Engineering: 2D Strip Packing
      • Engineering: Goddard rocket
      • Engineering: Hanging chain
      • Engineering: Largest small polygon
      • Engineering: Robot arm
      • Engineering: Structural Optimization
      • Location: Capacitated warehouse location
      • Location: Capacitated facility location problem (CFLP)
      • Lot Sizing: Multi level bucket
      • Lot Sizing: Single level big bucket
      • Lot Sizing: Single level small bucket
      • Lot Sizing: Single level small bucket with two items
      • Miscellaneous: Asynchronous solver sessions
      • Miscellaneous: Multistart
      • Multiple Solutions: Dice problem
      • Production Planning: Machine scheduling
      • Routing: Capacitated Vehicle Routing Problem
      • Routing: Capacitated Vehicle Routing Problem with Time Windows
      • Telecommunications: Delay constrained routing
      • Telecommunications: Filter design
      • Telecommunications: Capacitated network design problem
      • Transportation: Aircraft Assignment
      • Transportation: Aircraft landing
      • Transportation: Piecewise linear transportation
      • Uncertainty: Synchronous Optical Network Ring Design
      • Uncertainty: Production Planning - Robust Optimization
      • Uncertainty: Production Planning - Stochastic Programming
  • Toolkit
    • Comparing Profiler Runs
    • Stopwatch Library
  • Recently added
AIMMS How-To
AIMMS Logo
  • Community
    • Welcome
    • Product Q&A
    • Common Interests
    • Product Updates & Roadmap
  • Documentation
    • User Guide
    • Language Reference
    • Modeling Guide
    • Function Reference
    • WebUI
    • PRO Platform
    • Cloud Platform
    • Library Repository
  • How To
    • Getting Started
    • AIMMS Developer
    • Application UI
    • Deployment
    • Software Evolution
    • Examples
    • Recently Added
  • Downloads
  • Release Notes
    • AIMMS Development RELEASE NOTES
    • AIMMS Deployment RELEASE NOTES
    • AIMMS SC App Store RELEASE NOTES
  • How-To »
  • AIMMS Developer »
  • Data Connection »
  • Databases »
  • Link an SQLite Database to a Project
  • Edit on Github
Help & feedback

Table Of Contents
  • Link an SQLite Database to a Project
    • Install the SQLite driver
    • Connect your SQLite database to an AIMMS identifier
    • Verify the database link
    • Example Download
    • Summary
    • AIMMS materials
    • Other related websites

Link an SQLite Database to a Project

Say you have a wonderful AIMMS application that needs to link a Database Table identifier db (an AIMMS identifier) to your amazing SQL database that is, however, an SQLite database. To be able to read it, AIMMS needs the appropriate driver. This article presents how to:

  1. Install the SQLite driver

  2. Connect your SQLite database

  3. Verify that you can access it through AIMMS

Install the SQLite driver

SQLite driver

To install SQLite ODBC driver, please refer to the following website, and download sqliteodbc.exe or sqliteodbc_w64.exe, depending on your AIMMS’ configuration (and NOT your computer’s configuration):

SQLite ODBC Driver (external link)

Then just run the .exe and follow the instructions.

Note

This driver is open source under a BSD-type license. You may read the license terms for details.

Verify the installation

To verify that the driver is properly installed, please open the “Administrative Tools” of your computer (type “administrative tools” in Windows search bar). You should see a window like this :

../../_images/2odbc.png

Then open the “ODBC Data Sources (64-bit)” (or 32-bit) and reach the “Drivers” tab. Normally, you should find 3 new drivers, named SQLite ODBC (UTF-8), SQLite ODBC and SQLite3 ODBC. The window may include other drivers (as Microsoft Access for example) :

../../_images/3odbc.png

Congrats! The installation is successful.

Download an SQLite database sample to test

In order to test our installation, you could use your own generated SQLite database, or download a sample on this website: example db

(click on the “SQLite Sample Database” download link). The downloaded database is named chinook.db, and we will use it in the rest of this tutorial.

Connect your SQLite database to an AIMMS identifier

Generate the connection file

To read one database, AIMMS needs to know the name of the driver it should use and the location of your SQLite database. There are 3 different ways to give those indications to an AIMMS database identifier:

  • By generating a .dsn file, that you will store somewhere on your computer (almost equivalent to a text file .txt) and link it to your AIMMS identifier

  • By generating a system .dsn file, that your computer will store for you in a specific place

  • By creating a connection string, that will have the same role as a .dsn file, but directly written into the AIMMS application. This appears to be particularly useful when, for instance, a password is needed to read a certain database, and you don’t want to rewrite it each time you open your application or the database.

Generate a .dsn file connected to your SQLite database

Every database has its own .dsn file, so you need to generate one for each of your databases. In addition to that, every ODBC driver has a different .dsn file structure.

To generate the appropriate .dsn file from your SQLite ODBC driver, please select the tab “File DSN” from the ODBC Data Sources administrator (the one that we opened just before to check that the installation was completed). Then click the “Add” button in the upper right of the window. You should see this pop-up window:

../../_images/4odbc.png

Select the driver you want to use (in our case, SQLite3 ODBC Driver) and click “Next”.

At this point, the computer is asking you the name of the .dsn file you want to create. Here, it is named “test”.

../../_images/5odbc.png

After that, you will have access to the ‘SQLite ODBC Driver Connect’, which finally asks you to browse and select your SQLite database. In this example, the database is named chinook.db.

../../_images/8odbc.png

After clicking OK, you should see a new .dsn file (named test here) in the file explorer of your ODBC Data Source Administrator.

Then you may go directly to the section fill-out-db-table-id in order to complete the connection with your AIMMS database.

Generate a .dsn system file connected to your SQLite database

To generate the appropriate .dsn System file from your SQLite ODBC driver, please reach the tab “System DSN” from the ODBC Data Sources administrator (the one that we’ve opened just before to check that the installation was completed).

  • click Add in the upper right of the window.

  • Select SQLite3 ODBC Driver as shown below.

../../_images/7odbc.png

When the SQLite3 configuration window pops up :

  • define the System DSN file name (here, chinook SQLite3),

  • define the location of your SQLite database (db in this case) :

  • Click OK

../../_images/8odbc.png

You should now see that there is a new System DSN file in the System DSN tab:

../../_images/9odbc.png

Congrats! You may go directly to the Fill out an AIMMS Database Table identifier in order to complete the connection with your AIMMS database.

Create a connection string

A connection string is an AIMMS string parameter sp that you could fill out thanks to a procedure. This procedure should use the “SQLCreateConnectionString” function. Let’s build that connection string as follows :

  • Create an AIMMS string parameter sp named “ConnectionString” .

  • Check, Commit and Close.

  • Create a new procedure proc named “WriteTheConnectionString” (the name is not important)

  • Double click on procedure’s name and write the following code in its body field:

ConnectionString := SQLCreateConnectionString (
    DatabaseInterface              :  'odbc',
    DriverName                     :  "SQLite3 ODBC Driver",
    DatabaseName                   :  "C:\\Users\\Arthur.AIMMS\\Documents\\SQLite\\sqlite-dll-win64-x64-3150000\\chinook.db", !The path of your database
    AdditionalConnectionParameters :  "") ;

As you may see, this function fills out your string parameter with a “coded” string that will be read by your AIMMS datasource table identifier. This function allows you to define a user name and a password as well, by default empty, to access your SQLite database.

For more details on SQLCreateConnectionString() function syntax, right-click on SQLCreateConnectionString() in the body field and select the ‘help on’ item

  • Finally, you should run the procedure WriteTheConnectionString, in order to fill out your String parameter.

Fill out an AIMMS Database Table identifier

Let’s start a super simple new AIMMS project, containing only one Database Table  named Table1 :

../../_images/10aimms.png
  • Create a new Database Table ,

  • Specify Table1,

  • Activate the Data Source wizard,

Link Type

Action

Link with a .dsn file

  • Choose the Select File Data Source… command in the menu that pops up,

  • Select your DSN file (”dsn” in our case).

  • Press the Save

Link with a System .dsn file

  • Choose the Select User/System Data Source… command in the menu that pops up,

  • Select your DSN System file (chinook SQLite3 in our case).

  • Press the Save

Link with a connection string

  • Choose the Select String Parameter/Connection String… command in the menu that pops up,

  • Select the String Parameter sp you’ve just created (named ConnectionString in our case)

Verify the database link

Once you have linked the data source, you are now ready and able to select a table from this source. Execute the following steps:

  • Activate the Table Name wizard,

  • Choose the Select Table/Query Name… command from the pop-up menu,

  • You should see table names from your database… if not, please see the instructions bellow.

Warning

If you receive the following error message when trying to link with a connection string:

../../_images/11aimms.png

your connection string might be empty. Please check if ConnectionString parameter is empty by accessing its data (right click on its icon and choose Data…). It should be filled out with the following string :

DRIVER={SQLite3 ODBC Driver};DATABASE=C:\Users\Arthur.AIMMS\Documents\SQLite\sqlite-dll-win64-x64-3150000\chinook.db;

Example Download

Database read AIMMS example

Note

You will need the SQLite ODBC driver to be installed to run this example, as described in Install the SQLite driver

Please tell us if you think this example could be improved !

Summary

In this article we installed the SQLite driver, and linked our SQLite database to an AIMMS database table identifier db that we now may further use in our AIMMS application. We presented 3 different ways to link the database, namely the .dsn file, the system .dsn file and the connection string. We finally concluded by verifying that we were able to read our SQLite database through our AIMMS  database table identifier.

For further reading, please refer to the following related links :

AIMMS materials

  • Communicating With Databases

  • SQLCreateConnectionString()

Other related websites

  • SQLite ODBC Driver

  • SQLite Website

Last updated: Mar 18, 2022

Help & Feedback

Docs

Edit this page to fix an error or add an improvement in a pull request
Create an issue to suggest an improvement to this page

Product

Create a topic if there's something you don't like about this feature
Propose functionality by submitting a feature request

Support

Not what you where looking for? Search the docs

Remember we also have Community support

Still having trouble ? Reach out to us



Was this helpful?

Next Previous

AIMMS
AIMMS COMMUNITY
AIMMS DOCS
DISCLAIMER
PRIVACY STATEMENT
© 1989 - 2023 AIMMS B.V.