Le 19/09/2014 23:36, Torsten Bergmann a écrit :
Hi Sebastian,
I think there are two ways for ODBC'ing from Pharo:
1. one is the one included in DBXTalk, I think I had this running also
in the past and added
the glorp adaptor for ODBC. Cant remember why I switched to 2.
The same for me :)
About Glorp, it is a really nice piece of code, but has some drawbacks
(like all orms): IIRC, there is a "global" object cache and Glorp manage
object's locking conflicts in addition to Sql locking (imho bad). ORM is
perfect for quick and simple queries but tend to write bad queries when
complex.
2. the ODBC package (on top of FFI) that you can load from the Config
browser in Pharo 3.0
This one is based on the ODBC known from Squeak
(http://wiki.squeak.org/squeak/2480)
Try it with the second first. You have to open the ODBC settings panel
in Windows first,
and then connect to the DB by creating a system wide DSN name
similar to what is explained here for Oracle:
http://www.rpi.edu/datawarehouse/dw-brio-oce-stu-prod.html
You have to choose the correct MSSQL server driver. Then use this name
for the connection in Smalltalk.
If the connection fails try with a database viewer tool first.
In detail:
1. Use a new Pharo 3.0 as from the website first.
2. Install "ODBC" from the config browser (this will install
requirements like FFI automatically)
3. Install "OSWindows" from config browser
4. Evaluate
WinControlPanel showODBCSettings
to open the ODBC panel
5. Click on the "System DSN" tab there
6. Click "Add"
7. Select the SQL Server driver (usually "SQL Server Native Client
blah"), click Next
8. Enter an unused DSN name like "MYAPP_DB", also enter the IP
address (xxx.xxx.xxx.xxx)
of the server and go Next (no need for port as SQLServer usually
runs on 1433)
9. Authentication depends on installation. Usually one uses the SQL
Server authentication, either with
a dedicated user connection administrated in SQL Server Tools or
by default the user is something
with "sa" (for system administrator)
10. After login you should be able to select the database and test
connection right in the ODBC control panel
11. Go back to Pharo and run some query:
| con results |
Transcript open.
con := ODBCConnection dsn:'MY_SYSTEM_DSN_NAME' user: 'db_user' password:
'secretOne'.
results := (con query: 'select * from MyApp.dbo.MyTable') execute.
results do:[:row | Transcript show: row; cr].
con close.
Try the query with a database tool first. If the query works from ST you
can either use this or try your luck
with DBXTalk and ODBC. Hope this helps.
Bye
T.
Perfectly correct, I would also add:
1) On windows, it's simpler to connect to a server without registering a
DSN, you can specify the driver to use with odbc, it will find it in the
registry. For example, using the MS driver, you specify
Driver={SQL Server}; in the connection string (don't remember for oracle
but it can work the same way), with other attributes like server name,
instance name (if any) ,user , password and so on. You can take a look
in the registry to find installed drivers on your pc.
There is a class method on ODBCConnection that do the job:
toSqlServer:database:....
And you can define your own if you need. Example:
ODBCConnection toSqlServer: 'MyServer\MyInstance'
database: 'AdventureWorks'
applicationId: 'MyApp'
workstationId: 'LOCALST'
user: 'MyUser' password: 'MyPassord'
Using sa in an application is considered bad practice : a huge security
breach and dangerous for your sql server.
2) On windows, you don't need to specify the IP address, the network
resolution will find it for you. If you use tcp sockets, the sql browser
service has to be started, it will handle port resolution for you, you
just specify the server and instance name (if any).
3) to use windows authentication, you don't specify user and password
but have to add a SSPI=true attribute (not sure, you should rtfm...
al...zheimer?).
Your windows user is already defined as a sql login, but you have to map
it to a windows user in your database and the sql server have to accept
sql and windows authentication.
4) For production system, you *may* experience severe locking problems,
because the Pharo vm is single threaded. That is the only big problem
actually. you may also lock yourself between to connections (not a sql
deadlock, we could call it a vm deadlock ...).
Apart of the last one, odbc/ffi works like a charm and is very fast.
HTH
Alain
