Topics   All   Mac OS X (Only)   Windows (Only)   Linux (Only, Not)   iOS (Only, Not)  
Components   Crossplatform Mac & Win   Server (Not)   Client   Old   Guides   Examples
New in version: 6.1   6.2   6.3   6.4   6.5   7.0   7.1   7.2   7.3   7.4    Statistic  

SQL.Connect

Opens the connection to a data source.

Component Version macOS Windows Server FileMaker Cloud FileMaker iOS SDK
SQL 2.6 Yes Yes Yes Yes Yes

MBS( "SQL.Connect"; Connection; DBString { ; Username; Password; Client } )

Parameters

Parameter Description Example value
Connection The connection reference number gained with SQL.NewConnection. $Connection
DBString Database connection string.
Please specify port with comma after IP, not double colon.
"/test/database.db"
Username Optional
The user name to use when establishing the connection.
""
Password Optional
The password to use when establishing the connection.
""
Client Optional
Optional, one of the following client values: ODBC, Oracle, SQLServer, Firebird, InterBase, SQLBase, DB2, Informix, Sybase, MySQL, PostgreSQL or SQLite.
"SQLite"

Result

Returns "OK" or error message.

Description

Opens the connection to a data source.
Be aware that for most database servers, you need to specify client library with SQL.SetConnectionOption.

see also
http://www.sqlapi.com/OnLineDoc/Connection_Connect.html

Examples

Connect to an Oracle Database:

MBS("SQL.Connect"; $Connection; "MyDatabase"; "MyName"; "MyPass"; "Oracle")

Connect to a Microsoft SQL Server Database:

MBS("SQL.Connect"; $Connection; "MyServer\SQLEXPRESS@pubs"; ""; ""; "SQLServer")

Connect to a Sybase Database:

MBS("SQL.Connect"; $Connection; "MyServer@MyDatabase"; $name; $pass; "Sybase")

Connect to a DB2 Database:

MBS("SQL.Connect"; $Connection; "Server=myAddress:myPortNumber;Database=myDataBase;UID=myUsername;PWD=myPassword; CurrentSchema=mySchema;"; ""; ""; "DB2")

Connect to a Informix Database:

MBS("SQL.Connect"; $Connection; "Database=myDataBase;Host=192.168.10.10;Server=db_engine_tcp;Service=1492; #

Connect to a InterBase Database:

MBS("SQL.Connect"; $Connection; "MyServer:MyDatabase"; $name; $pass; "InterBase")

Connect to a SQLBase Database:

MBS("SQL.Connect"; $Connection; "MyDatabase"; $name; $pass; "SQLBase")

Connect to a MySQL Database:

MBS("SQL.Connect"; $Connection; "MyServer@MyDatabase"; $name; $pass; "MySQL")

Connect to a PostgreSQL Database:

MBS("SQL.Connect"; $Connection; "MyServer@MyDatabase"; $name; $pass; "PostgreSQL")

Connect to a ODBC Database (e.g. Microsoft Access):

MBS("SQL.Connect"; $Connection; "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\mydatabase.accdb;Uid=Admin;Pwd=;"; $name; $pass; "ODBC")

Connect to a local SQLite Database:

MBS("SQL.Connect"; $Connection; "/Users/cs/Desktop/test.db"; ""; ""; "SQLite")

Connect to Visual FoxPro via ODBC:

MBS("SQL.Connect"; $Connection; "Driver={Microsoft Visual FoxPro Driver};SourceType=DBC;SourceDB=c:\myvfpdb.dbc;Exclusive=No;NULL=NO;Collate=Machine;BACKGROUNDFETCH=NO;DELETED=NO;"; ""; ""; "ODBC")

Connect to a new in memory SQLite Database:

MBS("SQL.Connect"; $Connection; ":memory:"; ""; ""; "SQLite")

Connect to a MySQL Database via path:

MBS("SQL.Connect"; $Connection; "/Applications/MAMP/tmp/mysql/mysql.sock@MyDatabase"; $name; $pass; "MySQL")

Connect to Firebird Database:

# start a new connection
Set Variable [$Connection; Value:MBS( "SQL.NewConnection" )]
# if you like set where to find the libraries
Set Variable [$r; Value:MBS( "SQL.SetConnectionOption"; $Connection; "IBASE.LIBS"; "c:\Firebird\fbclient.dll" )]
# connect to database
Set Variable [$r; Value:MBS("SQL.Connect"; $Connection; "MyDatabase"; "MyName"; "MyPass"; "Firebird")]

Connect to PostgreSQL with timeout and ssl:

MBS("SQL.Connect"; $Connection; "127.0.0.1,5432@dbname=postgres connection_timeout=10 sslmode=require"; $name; $pass; "PostgreSQL")

Connect to Microsoft SQL Server from Mac via FreeTDS library:

Set Variable [$Connection; Value:MBS("SQL.NewConnection")]
#Tell plugin we want to use Microsoft SQL Server via ODBC
Set Variable [$result; Value:MBS("SQL.SetClient"; $Connection; "ODBC")]
#Tell plugin where freetds library is
Set Variable [$result; Value:MBS( "SQL.SetConnectionOption"; $Connection; "ODBC.LIBS"; "/Users/cs/Desktop/libtdsodbc.dylib")]
#Connect to database
Set Variable [$result; Value:MBS("SQL.Connect"; $Connection; "DRIVER={FREETDS};Server=192.168.2.32;UId=SA;PWD=test;Database=test;TDS_VERSION=7.2;Port=1433")]
If [$result  ≠  "OK"]
    Show Custom Dialog ["Error: " & $result]
    Set Variable [$result; Value:MBS("SQL.FreeConnection"; $Connection)]
    Halt Script
End If

Connect to ODBC via data source name:

MBS("SQL.Connect"; $Connection; "DSN"; $name; $pass; "ODBC")

Connect to MySQL and run a query:

#Start a new connection
Set Variable [$Connection; Value:MBS("SQL.NewConnection")]
#Tell plugin where MySQL library is (put it where you like)
Set Variable [$result; Value:MBS("SQL.SetConnectionOption"; $Connection; "MYSQL.LIBS"; "/Users/cs/Desktop/libmysqlclient.dylib")]
#Connect to a mysql database:
Set Variable [$result; Value:MBS("SQL.Connect"; $Connection; "192.168.11.51@Server_Config"; "user"; "password"; "MySQL")]
If [$result ≠ "OK"]
    #Connection failed
    Show Custom Dialog ["Error: " & $result]
    Set Variable [$result; Value:MBS("SQL.FreeConnection"; $Connection)]
    Halt Script
Else
    #Create a query:
    Set Variable [$Command; Value:MBS("SQL.NewCommand"; $Connection; "SELECT * FROM Server_Config where ServerName=:Name")]
    #If you use parameters, you can fill them here
    Set Variable [$r; Value:MBS("SQL.SetParamAsText"; $Command; "Name"; "MacMini")]
    #Execute it
    Set Variable [$result; Value:MBS("SQL.Execute"; $Command)]
    If [$result ≠ "OK"]
        Set Field [MySQL Query::Result; $result]
        Show Custom Dialog ["Error: " & $result]
    Else
        Set Variable [$lines; Value:""]
        Set Variable [$fieldcount; Value:MBS("SQL.FieldCount"; $command)]
        Loop
            #call FetchNext to get the next record
            Set Variable [$result; Value:MBS("SQL.FetchNext"; $Command)]
            Exit Loop If [$result ≠ 1]
            Set Variable [$line; Value:""]
            Set Variable [$i; Value:1]
            Loop
                #We query field names and values to show them later
                Set Variable [$v; Value:MBS("SQL.GetFieldAsText"; $command; $i)]
                Set Variable [$n; Value:MBS("SQL.GetFieldName"; $command; $i)]
                Set Variable [$line; Value:$line & $n & ": " & $v & ¶]
                Set Variable [$i; Value:$i+1]
                Exit Loop If [$i > $fieldCount]
            End Loop
            Set Variable [$lines; Value:$lines & ($line & ¶)]
        End Loop
        Set Variable [$lines; Value:$lines & ¶]
        Show Custom Dialog ["Result from Query:"; $lines]
    End If
    #Cleanup
    Set Variable [$result2; Value:MBS("SQL.FreeCommand"; $Command)]
End If
Set Variable [$result2; Value:MBS("SQL.FreeConnection"; $Connection)]

See also

Example Databases


SQL.Commit   -   SQL.Disconnect

Feedback: Report problem or ask question.




Links
MBS Xojo Chart Plugins