4D v16

External connections

Home

 
4D v16
External connections

External connections  


 

 

4D allows you to use external databases, in other words to execute SQL queries on databases other than the local one. To do this, you can connect to any external data source via ODBC or directly to other 4D databases.
Here are the commands that allow you to manage connections with external databases:

  • Get current data source tells you the ODBC data source used by the application.
  • GET DATA SOURCE LIST can be used to get the list of ODBC data sources installed on the machine.
  • SQL LOGIN allows you to connect to an external database directly or via an ODBC data source installed on the machine.
  • SQL LOGOUT can be used to close any external connection and to reconnect to the local 4D database.
  • USE DATABASE (SQL command) can be used to open an external 4D database using the 4D SQL engine.

The example below shows how to connect to an external data source (ORACLE), how to get data from the ORACLE database, and then how to disconnect from the ORACLE database and return to the local database.
Suppose that there is a valid data source named "Test_ORACLE_10g" installed in the system.

 ARRAY TEXT(aDSN;0)
 ARRAY TEXT(aDS_Driver;0)
 C_TEXT($Crt_DSN;$My_ORACLE_DSN)
 ARRAY TEXT(aTitles;0)
 ARRAY LONGINT(aNrActors;0)
 ARRAY LONGINT(aSoldTickets;0)
 ARRAY INTEGER(aMovieYear;0)
 ARRAY TEXT(aTitles;0)
 ARRAY TEXT(aDirectors;0)
 ARRAY TEXT(aMedias;0)
 C_LONGINT($MovieYear)
 C_TEXT($tQueryTxt)
 REDUCE SELECTION([MOVIES];0)
 $MovieYear:=1960
  `By default the current DSN is the local one, ";DB4D_SQL_LOCAL;", which is the value of the SQL_INTERNAL constant
 $Crt_DSN:=Get current data source
 ALERT("The current DSN is "+$Crt_DSN)
 
  `Do something on the local database
 Begin SQL
    SELECT Year_of_Movie, Title, Director, Media, Sold_Tickets
    FROM MOVIES
    WHERE Year_of_Movie >= :$MovieYear
    INTO :aMovieYear, :aTitles, :aDirectors, :aMedias, :aSoldTickets;
 End SQL
 
  `Get the data sources of the User type defined in the ODBC manager
 GET DATA SOURCE LIST(User data source;aDSN;aDS_Driver)
 $My_ORACLE_DSN:="Test_Oracle_10g"
 If(Find in array(aDSN;$My_ORACLE_DSN)>0)
  `Establish a connection between 4D and the data source $My_ORACLE_DSN="Test_Oracle_10g"
 
 SQL LOGIN($My_ORACLE_DSN;"scott";"tiger";*)
 
  `The current DSN is the ORACLE one
 $Crt_DSN:=Get current data source
 ALERT("The current DSN is "+$Crt_DSN)
 ARRAY TEXT(aTitles;0)
 ARRAY LONGINT(aNrActors;0)
 ARRAY LONGINT(aSoldTickets;0)
 ARRAY INTEGER(aMovieYear;0)
 ARRAY TEXT(aTitles;0)
 ARRAY TEXT(aDirectors;0)
 ARRAY TEXT(aMedias;0)
 
  `Do something on the external (ORACLE) database
 Begin SQL
    SELECT ENAME FROM EMP INTO :aTitles
 End SQL
 
  `Close the external connection opened with the SQL LOGIN command
 SQL LOGOUT
  `The current DSN becomes the local one
 $Crt_DSN:=Get current data source
 ALERT("The current DSN is "+$Crt_DSN)
Else
 ALERT("ORACLE DSN not installed")
End if

To test the above example, in the main window of the "4D SQL Code Samples" database, press the Connect to ORACLE button.

 
PROPERTIES 

Product: 4D
Theme: Tutorial

 
HISTORY 

 
ARTICLE USAGE

4D SQL Reference ( 4D v16)