> doc > odbc
|
History |
.............................................................................. .............................................................................. Usually the [../../def/linux] distributions do not install the [http://www.uni Usually the [../../def/linux] distributions do not install the [http://www.uni If you prefer to compile the [http://www.unixodbc.org|unixODBC] package yourse If you prefer to compile the [http://www.unixodbc.org|unixODBC] package yourse To enable the [../../def/gambas] ODBC component, you need to compile [../../de To enable the [../../def/gambas] ODBC [../../def/component], you need to compi +unixODBC Configuration +unixODBC Configuration .............................................................................. ..............................................................................
.............................................................................. ..............................................................................
Following I'll quote an example program that I used to insert image files into Following I'll quote an example program that I used to insert image files into
{box Blob Example {box Blob Example
==
' Gambas class file
' [../../def/gambas] [../../def/class] file PRIVATE $hConn AS Connection
[../../lang/private] $hConn [../../lang/as] [../../comp/gb.db/connection] PUBLIC SUB Form_Open()
$hConn = NEW Connection
END
[../../lang/public] [../../lang/sub] Form_Open() ==
$hConn = [../../lang/new] Connection
[../../lang/end]
The Connect button runs this function
The Connect button runs this function:
==
PUBLIC SUB btConnect_Click() PUBLIC SUB btConnect_Click()
[../../lang/with] $hConn WITH $hConn
.Type = "odbc" <<< The ODBC database type .Type = "odbc" <<< The ODBC database type
.Host = tbDatasource.Text <<< Text Box with the Data Source Name .Host = tbDatasource.Text <<< Text Box with the Data Source Name
.Login = tbUser.Text <<< The user used for the connection .Login = tbUser.Text <<< The user used for the connection
.............................................................................. ..............................................................................
END WITH END WITH
$hConn.Open $hConn.Open
[../../lang/if] ($hConn.Opened = [../../lang/false]) [../../lang/then] IF ($hConn.Opened = FALSE) THEN
[../../comp/gb.qt/message/error|Message.Error]("[../../comp/gb/error] Message.Error("Error opening the connection")
[../../lang/print] $hconn.Error PRINT $hconn.Error
[../../lang/else] ELSE
.
.
.
[../../lang/endif]
END ...
This function is called by a button, show a [../../comp/gb.form/dialog] Box fo ENDIF
END
==
This function is called by a button, show a [../../comp/gb.form/dialog] Box fo
==
PUBLIC SUB select_and_insert_into_blob_field_a_file() PUBLIC SUB select_and_insert_into_blob_field_a_file()
[../../lang/dim] hFile AS [../../comp/gb/file] DIM hFile AS File
DIM sLine AS [../../comp/gb/string] DIM sLine AS String
DIM outs AS blob DIM outs AS blob
DIM path AS String DIM path AS String
DIM hResult AS [../../comp/gb.db/result] DIM hResult AS Result
[../../comp/gb.qt/dialog/filter|Dialog.Filter] = ["*", "All Files"]
[../../comp/gb.qt/dialog/title|Dialog.Title] = "[../../comp/gb.qt/select] th
IF [../../comp/gb.qt/dialog/openfile|Dialog.OpenFile]() THEN [../../lang/ret
path = [../../comp/gb.qt/dialog/path|Dialog.path]
sLine = [../../comp/gb/file/load|File.Load]([../../comp/gb.qt/dialog/path|Di
IF [../../lang/not] $hConn.Opened THEN $hConn.Open
Dialog.Filter = ["*", "All Files"]
Dialog.Title = "Select the file to insert into the BLOB field"
hresult = [../../comp/gb.db/db/create|DB.Create]("table") <<< [../../com IF Dialog.OpenFile() THEN RETURN
hresult!blob = sLine <<< blob is the name of the blob field in the path = Dialog.path
hresult!filename = path <<< filename is a string field in the table sLine = File.Load(Dialog.Path)
hResult.Update
IF NOT $hConn.Opened THEN $hConn.Open
$hConn.Commit hResult = DB.Create("table") ' Table name
$hConn.Close hResult!blob = sLine ' blob is the name of the blob field in the ta
hResult!filename = path ' filename is a string field in the table
hResult.Update
$hConn.Commit
$hConn.Close
... ...
END END
.............................................................................. ..............................................................................
The [../../def/gambas] ODBC [../../def/component] uses the API provided by the The [../../def/gambas] ODBC [../../def/component] uses the API provided by the
The [../../def/gambas] ODBC component relay on the configuration made in the [ The [../../def/gambas] ODBC [../../def/component] relay on the configuration m
You'll find some example of both the configuration methods further in this doc You'll find some example of both the configuration methods further in this doc
Usually the [../../def/linux] distributions do not install the [http://www.uni Usually the [../../def/linux] distributions do not install the [http://www.uni
.............................................................................. ..............................................................................
Following I'll quote an example program that I used to insert image files into Following I'll quote an example program that I used to insert image files into
{example {box Blob Example
Blob Example
' Gambas class file ' [../../def/gambas] [../../def/class] file
PRIVATE $hConn AS Connection [../../lang/private] $hConn [../../lang/as] [../../comp/gb.db/connection]
PUBLIC SUB Form_Open() [../../lang/public] [../../lang/sub] Form_Open()
$hConn = NEW Connection $hConn = [../../lang/new] Connection
END [../../lang/end]
The Connect button runs this function The Connect button runs this function
PUBLIC SUB btConnect_Click() PUBLIC SUB btConnect_Click()
WITH $hConn [../../lang/with] $hConn
.Type = "odbc" <<< The ODBC database type .Type = "odbc" <<< The ODBC database type
.Host = tbDatasource.Text <<< Text Box with the Data Source Name .Host = tbDatasource.Text <<< Text Box with the Data Source Name
.Login = tbUser.Text <<< The user used for the connection .Login = tbUser.Text <<< The user used for the connection
.............................................................................. ..............................................................................
END WITH END WITH
$hConn.Open $hConn.Open
IF ($hConn.Opened = FALSE) THEN [../../lang/if] ($hConn.Opened = [../../lang/false]) [../../lang/then]
Message.Error("Error opening the connection") [../../comp/gb.qt/message/error|Message.Error]("[../../comp/gb/error]
PRINT $hconn.Error [../../lang/print] $hconn.Error
ELSE [../../lang/else]
. .
. .
. .
ENDIF [../../lang/endif]
END END
This function is called by a button, show a Dialog Box for the file selection This function is called by a button, show a [../../comp/gb.form/dialog] Box fo
PUBLIC SUB select_and_insert_into_blob_field_a_file() PUBLIC SUB select_and_insert_into_blob_field_a_file()
DIM hFile AS File [../../lang/dim] hFile AS [../../comp/gb/file]
DIM sLine AS String DIM sLine AS [../../comp/gb/string]
DIM outs AS blob DIM outs AS blob
DIM path AS String DIM path AS String
DIM hResult AS Result DIM hResult AS [../../comp/gb.db/result]
Dialog.Filter = ["*", "All Files"] [../../comp/gb.qt/dialog/filter|Dialog.Filter] = ["*", "All Files"]
Dialog.Title = "Select the file to insert into the BLOB field" [../../comp/gb.qt/dialog/title|Dialog.Title] = "[../../comp/gb.qt/select] th
IF Dialog.OpenFile() THEN RETURN IF [../../comp/gb.qt/dialog/openfile|Dialog.OpenFile]() THEN [../../lang/ret
path = Dialog.path path = [../../comp/gb.qt/dialog/path|Dialog.path]
sLine = File.Load(Dialog.Path) sLine = [../../comp/gb/file/load|File.Load]([../../comp/gb.qt/dialog/path|Di
IF NOT $hConn.Opened THEN $hConn.Open IF [../../lang/not] $hConn.Opened THEN $hConn.Open
hresult = DB.Create("table") <<< Table name hresult = [../../comp/gb.db/db/create|DB.Create]("table") <<< [../../com
hresult!blob = sLine <<< blob is the name of the blob field in the hresult!blob = sLine <<< blob is the name of the blob field in the
hresult!filename = path <<< filename is a string field in the table hresult!filename = path <<< filename is a string field in the table
hResult.Update hResult.Update
.............................................................................. ..............................................................................
$hConn.Commit $hConn.Commit
$hConn.Close $hConn.Close
.
. ...
.
END END
} }
.............................................................................. ..............................................................................
+unixODBC Configuration Examples +unixODBC Configuration Examples
Example of the odbcinst.ini located in the /etc directory. This file contains Example of the odbcinst.ini located in the \/etc directory. This file contains
{example {example
odbcinst.ini odbcinst.ini
.............................................................................. ..............................................................................
+odbc.ini +odbc.ini
\System wide odbc.ini located in the /etc directory \System wide odbc.ini located in the \/etc directory
{example {example
odbc.ini odbc.ini
.............................................................................. ..............................................................................
The [../../def/gambas] ODBC module uses the API provided by the [http://www.un The [../../def/gambas] ODBC [../../def/component] uses the API provided by the The [../../def/gambas] ODBC module relay on the configuration made in the [htt The [../../def/gambas] ODBC component relay on the configuration made in the [ You'll find some example of both the configuration methods further in this doc You'll find some example of both the configuration methods further in this doc Usually the [../../def/linux] distributions do not install the [http://www.uni Usually the [../../def/linux] distributions do not install the [http://www.uni If you prefer to compile the [http://www.unixodbc.org|unixODBC] package yourse If you prefer to compile the [http://www.unixodbc.org|unixODBC] package yourse To enable the [../../def/gambas] odbc module, you need to compile [../../def/g To enable the [../../def/gambas] ODBC component, you need to compile [../../de +unixODBC Configuration +unixODBC Configuration .............................................................................. ..............................................................................
.............................................................................. .............................................................................. +\Gambas Example +\Gambas Example \Gambas comes whit a lot of example applications, the \Database example can be \Gambas comes with a lot of example applications, the \Database example can be \select the \Database \example application, run the application and then fill \select the \Database \example application, run the application and then fill .............................................................................. ..............................................................................
.............................................................................. ..............................................................................
</tr> </tr>
</table> </table>
[../../comp/gb.qt/select] the \type of the \database from the list, chose \odb \Select the \type of the \database from the list, chose \odbc, in the host nam
<table border="0" cellspacing="0" cellpadding="8" style="border:solid 2px #95C <table border="0" cellspacing="0" cellpadding="8" style="border:solid 2px #95C
<tr> <tr>
.............................................................................. ..............................................................................
<th>\Database example application</th> <th>\Database example application</th>
</tr> </tr>
</table> </table>
After the connection you can run any SQL commands.
+Blob Example
Following I'll quote an example program that I used to insert image files into
{example
Blob Example
' Gambas class file
PRIVATE $hConn AS Connection
PUBLIC SUB Form_Open()
$hConn = NEW Connection
END
The Connect button runs this function
PUBLIC SUB btConnect_Click()
WITH $hConn
.Type = "odbc" <<< The ODBC database type
.Host = tbDatasource.Text <<< Text Box with the Data Source Name
.Login = tbUser.Text <<< The user used for the connection
.Password = tbPassword.Text <<< The user's password
.Name = "" <<< Not used in the ODBC connection
END WITH
$hConn.Open
IF ($hConn.Opened = FALSE) THEN
Message.Error("Error opening the connection")
PRINT $hconn.Error
ELSE
.
.
.
ENDIF
END
This function is called by a button, show a Dialog Box for the file selection
PUBLIC SUB select_and_insert_into_blob_field_a_file()
DIM hFile AS File
DIM sLine AS String
DIM outs AS blob
DIM path AS String
DIM hResult AS Result
Dialog.Filter = ["*", "All Files"]
Dialog.Title = "Select the file to insert into the BLOB field"
IF Dialog.OpenFile() THEN RETURN
path = Dialog.path
sLine = File.Load(Dialog.Path)
IF NOT $hConn.Opened THEN $hConn.Open
hresult = DB.Create("table") <<< Table name
hresult!blob = sLine <<< blob is the name of the blob field in the
hresult!filename = path <<< filename is a string field in the table
hResult.Update
$hConn.Commit
$hConn.Close
.
.
.
END
}
.............................................................................. .............................................................................. <table border="0" cellspacing="0" cellpadding="8" style="border:solid 2px #95C <table border="0" cellspacing="0" cellpadding="8" style="border:solid 2px #95C <tr> <tr> <td><a href="/image/doc/odbc/:gambas-ide" target="_blank"><img src="/image/doc <td><a href="/image/doc/odbc/:gambas-ide" target="_blank"><img src="/image/doc </tr> </tr> <tr> <tr> <th>\Gambas</th> <th>\Gambas</th> .............................................................................. ..............................................................................
.............................................................................. .............................................................................. <table border="0" cellspacing="0" cellpadding="8" style="border:solid 2px #95C <table border="0" cellspacing="0" cellpadding="8" style="border:solid 2px #95C <tr> <tr> <td><a href="/image/doc/odbc/:\gambas" target="_blank"><img src="/image/doc/od <td><a href="/image/doc/odbc/:gambas-ide" target="_blank"><img src="/image/doc </tr> </tr> <tr> <tr> <th>\Gambas</th> <th>\Gambas</th> .............................................................................. ..............................................................................
.............................................................................. .............................................................................. +\Gambas Example +\Gambas Example \Gambas comes whit a lot of example applications, the \Database example can be \Gambas comes whit a lot of example applications, the \Database example can be \select the \Database \example application, run the application and then fill [../../comp/gb.qt/select] the \Database \example application, run the applicat <table border="0" cellspacing="0" cellpadding="8" style="border:solid 2px #95C <table border="0" cellspacing="0" cellpadding="8" style="border:solid 2px #95C <tr> <tr> <td><a href="/image/doc/odbc/:[../../def/gambas]" target="_blank"><img src="/i <td><a href="/image/doc/odbc/:\gambas" target="_blank"><img src="/image/doc/od </tr> </tr> <tr> <tr> <th>\Gambas</th> <th>\Gambas</th> </tr> </tr> </table> </table> Select the \type of the \database from the list, chose \odbc, in the host name [../../comp/gb.qt/select] the \type of the \database from the list, chose \odb <table border="0" cellspacing="0" cellpadding="8" style="border:solid 2px #95C <table border="0" cellspacing="0" cellpadding="8" style="border:solid 2px #95C <tr> <tr> .............................................................................. ..............................................................................
.............................................................................. .............................................................................. LockTimeoutWaitTransactions = 0 LockTimeoutWaitTransactions = 0 SafeThread = Yes SafeThread = Yes } } +\Gambas Example \Gambas comes whit a lot of example applications, the \Database example can be [../../comp/gb.qt/select] the \Database \example application, run the applicat <table border="0" cellspacing="0" cellpadding="8" style="border:solid 2px #95C <tr> <td><a href="/image/doc/odbc/:[../../def/gambas]" target="_blank"><img src="/i </tr> <tr> <th>\Gambas</th> </tr> </table> Select the \type of the \database from the list, chose \odbc, in the host name <table border="0" cellspacing="0" cellpadding="8" style="border:solid 2px #95C <tr> <td><a href="/image/doc/odbc/:example-db" target="_blank"><img src="/image/doc </tr> <tr> <th>\Database example application</th> </tr> </table>
.............................................................................. ..............................................................................
+unixODBC Configuration Examples +unixODBC Configuration Examples
Example of the odbcinst.ini located in the /etc directory Example of the odbcinst.ini located in the /etc directory. This file contains
this file contains all the drivers configuration and the [../../comp/gb.sdl.im
{example {example
odbcinst.ini odbcinst.ini
.............................................................................. ..............................................................................
+odbc.ini +odbc.ini
[../../comp/gb.eval/system] wide odbc.ini located in the /etc directory \System wide odbc.ini located in the /etc directory
{example {example
odbc.ini odbc.ini
.............................................................................. ..............................................................................
+.odbc.ini +.odbc.ini
[../../comp/gb/user]'s odbc configuration located in the user's home directory \User's odbc configuration located in the \user's home directory .odbc.ini
{example {example
.odbc.ini .odbc.ini
.............................................................................. ..............................................................................
.............................................................................. ..............................................................................
Setup64 = /usr/lib/firebird/libOdbcFb32.so CPTimeout =
UsageCount = 1 CPReuse =
CPTimeout = }
CPReuse =
}
+odbc.ini
[../../comp/gb.eval/system] wide odbc.ini located in the /etc directory
{example
odbc.ini
[MySQL_DB_test]
Description = MySQL
Driver = MySQL
Host = localhost
Database = TestDB
Port =
[Numeric_DB]
Description = Firebird
Driver = firebird
Dbname = localhost:
Client =
User = User_ID
Password = Password
Role =
CharacterSet = NONE
ReadOnly = No
NoWait = No
Dialect = 3
QuotedIdentifier = Yes
SensitiveIdentifier = No
AutoQuotedIdentifier = No
UseSchemaIdentifier = 0 - Set null field SCHEMA
LockTimeoutWaitTransactions = 0
SafeThread = Yes
}
+.odbc.ini
[../../comp/gb/user]'s odbc configuration located in the user's home directory
{example
.odbc.ini
[System_i]
Description = iSeries Access ODBC Driver
Driver = iSeries Access ODBC Driver
System = 9.71.196.44
UserID =
Password =
Naming = 0
DefaultLibraries = QGPL,qiws
Database =
ConnectionType = 0
CommitMode = 2
ExtendedDynamic = 1
DefaultPkgLibrary = QGPL
DefaultPackage = A/DEFAULT(IBM),2,0,1,0,512
AllowDataCompression = 1
MaxFieldLength = 32
BlockFetch = 1
BlockSizeKB = 128
ExtendedColInfo = 0
LibraryView = 0
AllowUnsupportedChar = 0
ForceTranslation = 0
Trace = 0
[mySQL_test]
Description = MySQL
Driver = MySQL
Host = localhost
Database = BlobDB
Port =
[NumericFB]
Description = fbtest
Driver = firebird
Dbname = localhost:/home/bortolan/fbtest/fbtest.fbd
Client =
User = bortolan
Password = Password
Role =
CharacterSet = NONE
ReadOnly = No
NoWait = No
Dialect = 3
QuotedIdentifier = Yes
SensitiveIdentifier = No
AutoQuotedIdentifier = No
UseSchemaIdentifier = 0 - Set null field SCHEMA
LockTimeoutWaitTransactions = 0
SafeThread = Yes
}
.............................................................................. ..............................................................................
You must have one \Database \driver configured for each \Database type and one You must have one \Database \driver configured for each \Database type and one
{example
+unixODBC Configuration Examples +unixODBC Configuration Examples
Example of the odbcinst.ini located in the /etc directory Example of the odbcinst.ini located in the /etc directory
this file contains all the drivers configuration and the Driver Manager's conf this file contains all the drivers configuration and the [../../comp/gb.sdl.im
{example
odbcinst.ini
[iSeries Access ODBC Driver] [iSeries Access ODBC Driver]
Description = iSeries Access for Linux ODBC Driver Description = iSeries Access for Linux ODBC Driver
.............................................................................. ..............................................................................
.............................................................................. ..............................................................................
</table> </table>
You must have one \Database \driver configured for each \Database type and one You must have one \Database \driver configured for each \Database type and one
{example
+unixODBC Configuration Examples
Example of the odbcinst.ini located in the /etc directory
this file contains all the drivers configuration and the Driver Manager's conf
[iSeries Access ODBC Driver]
Description = iSeries Access for Linux ODBC Driver
Driver = /opt/ibm/iSeriesAccess/lib/libcwbodbc.so
Setup = /opt/ibm/iSeriesAccess/lib/libcwbodbcs.so
NOTE1 = If using unixODBC 2.2.11 or later and you want the 3
NOTE2 = the following Driver64/Setup64 keywords will provide
Driver64 = /opt/ibm/iSeriesAccess/lib64/libcwbodbc.so
Setup64 = /opt/ibm/iSeriesAccess/lib64/libcwbodbcs.so
Threading = 2
DontDLClose = 1
UsageCount = 1
[MySQL]
Description =
Driver = /usr/lib/odbc/libmyodbc.so
Driver64 = /usr/lib
Setup = /usr/lib/odbc/libodbcdrvcfg1S.so
Setup64 = /usr/lib
UsageCount = 1
CPTimeout =
CPReuse =
[ODBC]
Trace = No
TraceFile = /tmp/sql.log
ForceTrace = No
Pooling = No
[Firebird]
Description =
Driver = /usr/lib/firebird/libOdbcFb32.so
Driver64 = /usr/lib/firebird/libOdbcFb32.so
Setup = /usr/lib/firebird/libOdbcFb32.so
Setup64 = /usr/lib/firebird/libOdbcFb32.so
UsageCount = 1
CPTimeout =
CPReuse =
}
.............................................................................. .............................................................................. </tr> </tr> </table> </table> You must have one \Database \driver configured for each \Database type and one You must have one \Database \driver configured for each \Database type and one