SQL LOGIN

version 11.3 (Modified)

SQL LOGIN{(dataEntry; userName; password; *)}

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 application:

• 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 internal SQL engine.

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/Configuration" page of the application Preferences.

Refer to examples 1 and 2.

• 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 corre-sponds to the name specified. The network publication name of a database is set on the "Client-Server/Configuration" page of the application Preferences.

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 4.

• 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 v11" instead of "Favorites v11".

- 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 SQL engine of the database.

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 SQL engine of the database.

If you call SQL LOGIN again without having explicitly closed the current connection, it will be closed automatically.

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.

1. This statement will bring up the ODBC Manager dialog box:

   SQL LOGIN 

2. 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:

   SQL LOGIN("ODBC:MyOracle";"Scott";"tiger";*)

3. Open a connection with the 4D internal SQL kernel:

   SQL LOGIN(SQL_INTERNAL;$user;$password)

4. Opening of a direct connection with the 4D Server v11 SQL 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.

   SQL LOGIN("IP:192.168.45.34";"John";"azerty")

5. Opening of a direct connection with the 4D Server v11 SQL 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.

   SQL LOGIN("IP:192.168.45.34:20150";"John";"azerty";*)

6. Opening of a direct connection with the 4D Server v11 SQL 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/Configuration page of the Preferences) 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.

   SQL LOGIN ("4D:Accounts_DB";"John";"azerty")

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

   ARRAY TEXT (30;aNames)
   ARRAY LONGINT(aAges;0)
   SQL LOGIN("ODBC:MyORACLE";"Marc";"azerty")
   If(OK=1)
         `The following query will be redirected to the external ORACLE  database
      SQL EXECUTE("SELECT Name, Age FROM PERSONS";aNames; aAges)
         `The following query will be sent to the local 4D database
      Begin SQL
         SELECT Name, Age 
         FROM PERSONS
         INTO :aNames, :aAges;
      End SQL
         `The following SQL LOGIN command closes the current connection
         `with the external ORACLE database and opens a new connection
         `with an external MySQL database
      SQL LOGIN ("ODBC:MySQL";"Jean";"qwerty";*)
      If(OK=1)
            `The following query will be redirected to the external MySQL database
         SQL EXECUTE("SELECT Name, Age FROM PERSONS";aNames; aAges)
            `The following query will also be redirected to the external MySQL database
         Begin SQL
            SELECT Name, Age 
            FROM PERSONS
            INTO :aNames, :aAges;
         End SQL
         SQL LOGOUT
            `The following query will be sent to the local 4D database
         Begin SQL
            SELECT Name, Age 
            FROM PERSONS
            INTO :aNames, :aAges;
         End SQL
      End if
   End if

Begin SQL, End SQL, SQL LOGOUT.

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