Sept. 19, 2014
10:55 p.m.
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