SQL LOGIN

The SQL LOGIN command allows you to connect to an SQL data source specified in the dataEntry parameter. It designates the target of the SQL queries executed subsequently in the current process:

• via the SQL EXECUTE command,
• via code placed within the Begin SQL / End SQL tags (if the * parameter is passed).

The SQL data source can either be:

• an external 4D Server database that you access directly,
• an external ODBC source,
• the local 4D database (internal database).

In dataEntry, you can pass one of the following values: an IP address, a 4D database publication name, an ODBC data source name, an empty string or the SQL_INTERNAL constant.

• IP address
  Syntax: IP:<IPAddress>{:<TCPPort>}
  In this case, the command opens a direct connection with the 4D Server database executed on the machine with the IP address specified. On the "target" machine, the SQL server must be started. If you pass a TCP port number, it must have been specified as the publication port of the SQL server in the "target" database. If you do not pass a TCP port number, the default port will be used (19812). The TCP port number of the SQL server can be modified on the "SQL" page of the Database Settings. Refer to examples 4 and 5.
  If you have enabled SSL for the "target" SQL server (option available in the Database Settings), you must add the ":ssl" keyword to the end of the IP address in order for the server to be able to handle the request correctly (see example 6).

• 4D database publication name
  Syntax: 4D:<Publication_Name>
  In this case, the command opens a direct connection with the 4D Server database whose publication name on the network corresponds to the name specified. The network publication name of a database is set on the "Client-Server" page of the Database Settings.
  Refer to example 4.
  Note: The TCP port number of the target 4D SQL server (that publishes the 4D database) and the TCP port number of the SQL server of the 4D application that opens the connection must be the same.

• valid ODBC data source name
  Syntax: ODBC:<My_DSN> or <My_DSN>
  In this case, the dataEntry parameter contains the name of the data source as it has been set in the ODBC driver manager.
  Note: For compatibility with previous versions of 4D, it is possible to omit the "ODBC:" prefix. However, for better code readability, it is recommended to use this prefix.
  Refer to example 2.

• empty string
  Syntax: ""
  In this case, the command displays the connection dialog box so that the data source to be connected to can be entered manually:
  
  
  
  This dialog box includes several pages. The TCP/IP page includes the following elements:
  
  • Target Name: This menu is built using two lists:
    
    • The list of databases that have been opened recently in direct con-nection. The mechanism for updating this list is the same as that of the 4D application, except that the folder containing the .4DLink files is named "Favorites SQL vXX" instead of "Favorites vXX".
    • The list of 4D Server applications whose SQL server is started and whose TCP port for SQL connections is the same as that of the source application. This list is dynamically updated on each new call to the SQL LOGIN command without the dataEntry parameter. If the "^" character is placed before a database name, this indicates that the connection has been made in secured mode via SSL. 
  • Network Address: This area displays the IP address and possibly the TCP port of the database selected in the Target Name menu. You can also enter an IP address in this area and then click on the Connection button in order to connect to the corresponding 4D Server database. You can also specify the TCP port by entering a colon (:) followed by the port number after the address. For example: 192.168.93.105:19855
  • User Name and Password: These areas can be used to enter the con-nection identifiers. 
  • The User DSN and System DSN pages display, respectively, the list of user and system ODBC data sources specified in the ODBC driver of the machine. These pages can be used to select a data source and enter the identifiers in order to open a connection with an external ODBC data source.
  
  If the connection is established, the OK system variable is set to 1. Otherwise, it is set to 0 and an error is generated. This error can be intercepted via an error-handling method installed by the ON ERR CALL command.

• SQL_INTERNAL constant
  Syntax: SQL_INTERNAL
  In this case, the command redirects subsequent SQL queries to the internal 4D database.

Warning: The prefixes used in the dataEntry parameter (IP, ODBC, 4D) must be written in uppercase.

userName contains the name of the user authorized to connect to the external data source. For example, with Oracle®, the user name can be “Scott”.

password contains the password of the user authorized to connect to the external data source. For example, with Oracle®, the password can be “tiger”.

Note: In the case of a direct connection, if you pass empty strings in the userName and password parameters, the connection will only be accepted if 4D passwords are not activated in the target database. Otherwise, the connection will be refused.

The optional * parameter can be used to change the target of the SQL code executed within the Begin SQL/End SQL tags. If you do not pass this parameter, the code placed within the Begin SQL/End SQL tags will still be sent to the internal SQL engine of 4D, without taking the configuration specified by the SQL LOGIN command into account. If you do pass this parameter, the SQL code executed within the Begin SQL/End SQL tags will be sent to the source specified in the dataEntry parameter.

To close the current connection and free the memory, simply execute the SQL LOGOUT command. All the SQL queries are then sent to the internal 4D SQL database.
If you call SQL LOGIN again without having explicitly closed the current connection, it will be closed automatically.

Note: In the case where an external connection attempt via SQL LOGIN fails, the internal 4D database automatically becomes the current data source.

These parameters are optional; if no parameters are passed, the command will bring up the ODBC Login dialog box that allows you to select the external data source.

The scope of this command is per process; in other words, if you want to execute two distinct connections, you must create two processes and execute each connection in each process.

This statement will bring up the ODBC Manager dialog box:

Opening of a connection via the ODBC protocol with the "MyOracle" external data source. SQL queries executed via the SQL EXECUTE command and queries included within the Begin SQL/End SQL tags will be redirected to this connection:

Open a connection with the 4D internal SQL kernel:

Opening of a direct connection with the 4D Server application executed on the machine having the IP address 192.168.45.34 and replying on the default TCP port. The SQL queries executed via the SQL EXECUTE command will be redirected to this con-nection; the queries included within the Begin SQL/End SQL tags will not be redirected.

Opening of a direct connection with the 4D Server application executed on the machine having the IP address 192.168.45.34 and replying on TCP port 20150. The SQL queries executed via the SQL EXECUTE command and the queries included within the Begin SQL/End SQL tags will be redirected to this connection.

Opening of a direct connection in SSL with the 4D Server application running on the machine with the IP address 192.168.45.34 and responding on the default TCP port. You must have enabled SSL for the SQL server on the 4D Server application:

Opening of a direct connection with the 4D Server application which publishes, on the local network, a database whose publication name is "Accounts_DB." The TCP port used for the SQL server of both databases (set on the SQL page of the Database Settings) must be the same (19812 by default). The SQL queries executed via the SQL EXECUTE command will be redirected to this connection; the queries included within the Begin SQL/End SQL tags will not be redirected.

This example illustrates the connection possibilities provided by the SQL LOGIN command:

If the connection is successful, the system variable OK is set to 1; otherwise, it is set to