How to Create a Valid SQL Script

In order toTo create a valid SQL script,script in Appvance AIQ, you needmust to understandconfigure the basicdatabase configurationconnection settingsproperties embedded into the script file to create a connection.correctly. These settingsproperties are based on Hibernate and are used to map the database configuration settingsand correctly.establish a connection through the appropriate JDBC driver.

AThe listfollowing ofconfiguration thevalues dataare neededrequired:

to connect with the database follows: -

• User name - Name
• Password -
• URL (connectionConnection string) - String)
• Driver Class -
• Database name - Name
• Dialect -
• Connection delimiterDelimiters

All of these itemsvalues aremust requiredbe withincorrectly configured in the SQL script when using ASE to set upestablish a connectiondatabase using ASE. Let’s have a quick look into it.connection.

Database

Connection Properties

1. User Name

The User name:Name correspondsis the database account used to authenticate and access the required database.

Make sure that:

• The user exists in the database.
• The user has the required permissions to perform the requested operations.
• The database allows connections from the machine where the script is executed.
• For remote databases, remote access is enabled and permitted by the database and network configuration.

2. Password

The Password is the password associated with the specified database user.

The credentials must match a valid userdatabase nameaccount. registeredIf inauthentication fails, verify the username, password, and database to access itspermissions.

records.

3. SomeURL

SQL

The DBURL engines such as MySQL allows you to have users with remote access and user with no remote access, if you’re trying to reach a remote database make sure thatspecifies the database has remote connections enabled and that the user you are providing has enough privileges.

Password: database user password that matches the provided user name credentials.

URL: the URL corresponds to theJDBC connection string used to directly connect to athe database.

This

The format of the URL isdepends on the same URL used to create a database connectionengine using a JDBC driver. I.E: Usual URL for a MySQL connection goes like this:

Driver Class: The driver class is the specificand JDBC driver namebeing usedused.

For example, a typical MySQL JDBC connection URL can be structured as follows:

jdbc:mysql://<host>:<port>/<database>

Additional connection parameters can be included when required by the database or JDBC driver.

4. Driver Class

The Driver Class identifies the JDBC driver that Appvance uses to specify toestablish the Driverdatabase Managerconnection.

which JDBC drivers you’re trying to make connections with. With MySQL Connector/J, the name of this class is com.mysql.jdbc.Driver. Note that each SQL

Each database engine has its own JDBC driver class.

For

example, the current MySQL Connector/J driver class is:

com.mysql.cj.jdbc.Driver

Note: Older MySQL documentation may reference com.mysql.jdbc.Driver. This class name was deprecated in MySQL Connector/J 8.0 and should not be used for current configurations.

Make sure the appropriate JDBC driver is available to the AIQ environment where the SQL script is executed.

5. DB Name

The DB Name:Name It’sis the name of the database you’rethat tryingthe script connects to.

It must correspond to reach.an existing database that is accessible by the configured user.

For

example:

my_database

The database name is also commonly included as part of the JDBC connection URL.

6. Dialect

The Dialect:Dialect Herespecifies youthe Hibernate dialect used to communicate with the selected database engine.

For Appvance AIQ, the dialect must specifyuse athe concreteAppvance-specific Hibernate dialect forpackage:

org.appvance.hibernate.dialect

The Appvance dialect package is based on Hibernate dialect implementations and was modified to work with usinginclude the package org.appvance.hibernate.dialect. Thisappvance package has been modified from its original qualified name adding “appvance” to avoid runtime issues. A list of these values has been provided in the charts located at the end of this document.conflicts.

The

appropriate

Connectiondialect delimiters: A couple of reserved words have tomust be addedselected according to the database engine being used. Refer to the supported dialect table in this documentation for the available values.

7. Connection Delimiters

The SQL script must include the commands required to explicitly open and close the database connection.

The connection delimiters are:

Connect
Disconnect

Connect starts the database connection using the configured connection properties.

Disconnect closes the database connection after the SQL operations have completed.

These wordscommands are:should Connectbe used to clearly define the beginning and Disconnect.end Theirof namesthe database interaction.

Example Configuration

A valid SQL script must contain the required connection information and use the appropriate connection delimiters. The exact syntax and values depend on the database engine and JDBC driver being used.

Before executing the script, verify that:

1. The database user and password are self-explanatory.valid.
2. The database is reachable from the AIQ execution environment.
3. The JDBC URL is correctly formatted.
4. The required JDBC driver is available.
5. The database name is correct.
6. The appropriate Appvance Hibernate dialect is configured.
7. The Connect and Disconnect delimiters are present.
8. The database user has sufficient permissions for the SQL operations performed by the script.