Re: [Pharo-dev] How production ready is Pharo on SQLServer?
Wonderful Torsten, thanks so much for that. Worked flawlessly from iPad On 19/09/2014, at 18:36, Torsten Bergmann asta...@gmx.de wrote: 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. 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. Gesendet: Freitag, 19. September 2014 um 21:00 Uhr Von: Sebastian Sastre sebast...@flowingconcept.com An: Pharo Development List pharo-dev@lists.pharo.org Betreff: Re: [Pharo-dev] How production ready is Pharo on SQLServer? On Sep 19, 2014, at 8:05 AM, Torsten Bergmann asta...@gmx.de wrote: In the end I used ODBC package as it is easy to load from the config browser and one only has to setup an ODBC name After installing all and getting things sorted, it rejects my connection. Triplechecked, network settings, server and client with tcp enabled, everything but can’t connect. So I guess is time to try ODBC. but odbc has no host:port, what should I do about connection settings? I how do I make a DBXConnection based in an odbc one? thanks! sebastian o/ blog: http://sebastianconcept.com LinkedIn: http://www.linkedin.com/in/sebastiansastre github: https://github.com/sebastianconcept
Re: [Pharo-dev] How production ready is Pharo on SQLServer?
Sebastian, that doesn't look like the correct one DBXTalk is on smalltalkhub https://ci.inria.fr/dbxtalk/ Stephan
Re: [Pharo-dev] How production ready is Pharo on SQLServer?
Here is how to load and install DBXTalk in Pharo 3.0: Gofer new smalltalkhubUser: 'DBXTalk' project: 'DBXTalkDriver'; package: 'ConfigurationOfOpenDBXDriver'; load. #ConfigurationOfOpenDBXDriver asClass loadBleedingEdge. #NBPharoOpenDBX asClass installAsCurrent. Cheers, Doru On Fri, Sep 19, 2014 at 8:45 AM, Esteban Lorenzano esteba...@gmail.com wrote: yeah… I told you that dbxtalk site information is outdated (and never find the time to update) you need to look here: http://smalltalkhub.com/#!/~DBXTalk/Configurations (or in the configuration browser) cheers, Esteban On 19 Sep 2014, at 08:00, Stephan Eggermont step...@stack.nl wrote: Sebastian, that doesn't look like the correct one DBXTalk is on smalltalkhub https://ci.inria.fr/dbxtalk/ Stephan -- www.tudorgirba.com Every thing has its own flow
Re: [Pharo-dev] How production ready is Pharo on SQLServer?
Tudor Girba-2 wrote package: 'ConfigurationOfOpenDBXDriver'; load. #ConfigurationOfOpenDBXDriver asClass loadBleedingEdge. configurationOf: 'OpenDBXDriver'; loadVersion: #bleedingEdge. :) - Cheers, Sean -- View this message in context: http://forum.world.st/How-production-ready-is-Pharo-on-SQLServer-tp4777508p4778944.html Sent from the Pharo Smalltalk Developers mailing list archive at Nabble.com.
Re: [Pharo-dev] How production ready is Pharo on SQLServer?
On Sep 19, 2014, at 8:05 AM, Torsten Bergmann asta...@gmx.de wrote: In the end I used ODBC package as it is easy to load from the config browser and one only has to setup an ODBC name After installing all and getting things sorted, it rejects my connection. Triplechecked, network settings, server and client with tcp enabled, everything but can’t connect. So I guess is time to try ODBC. but odbc has no host:port, what should I do about connection settings? I how do I make a DBXConnection based in an odbc one? thanks! sebastian o/ blog: http://sebastianconcept.com LinkedIn: http://www.linkedin.com/in/sebastiansastre github: https://github.com/sebastianconcept
Re: [Pharo-dev] How production ready is Pharo on SQLServer?
Le 19/09/2014 13:36, Sebastian Sastre a écrit : yes if things where in markdown friendly wiki and git you'll see a pull request in half a second I'll think of something but returning to the dll issue, you mention you endd up using odbc and I see there are two backends to choose from. The odbc and the mssql, so, i'm getting this right? If I use odbc that dll is not needed? Any reason not to use odbc? from iPad Hi, libsybdb.dll is for sybase, it may have worked well with (very) old versions of sql server but is definitely not the correct way to connect to MS sql server (*plus MS has it's own version of dblib.dll). (*plus I'm not sure that deploying sybase native libs is legal). The MS recommendations for client connectivity to Sql server is not to use native libraries but instead use a standard layer. it's a long story, but to make it short odbc seems to be the best choice once again: MS preconizations: odbc, then rdo, then dao, then oledb, then ado, (and ado.net) and finally odbc again (on top of sql native client) with ado.net for dotnet. see http://msdn.microsoft.com/en-us/data/aa937732 http://msdn.microsoft.com/en-us/library/ms810810.aspx I don't know how it works with opendbx, but odbc is quite solid, and you don't have to take care of native libs deployment. IMHO a good choice Regards Alain
Re: [Pharo-dev] How production ready is Pharo on SQLServer?
Hi Sebastian, I think there are two ways for ODBCing 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. 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 similarto 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 forthe connection in Smalltalk. If the connection fails try with adatabase 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 theuser 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 withDBXTalk and ODBC.Hope this helps. Bye T. Gesendet:Freitag, 19. September 2014 um 21:00 Uhr Von:Sebastian Sastre sebast...@flowingconcept.com An:Pharo Development List pharo-dev@lists.pharo.org Betreff:Re: [Pharo-dev] How production ready is Pharo on SQLServer? On Sep 19, 2014, at 8:05 AM, Torsten Bergmann asta...@gmx.de wrote: In the end I used ODBC package as it is easy to load from the config browser and one only has to setup an ODBC name After installing all and getting things sorted, it rejects my connection. Triplechecked, network settings, server and client with tcp enabled, everything but cant connect. So I guess is time to try ODBC. but odbc has no host:port, what should I do about connection settings? I how do I make a DBXConnection based in an odbc one? thanks! sebastian o/ blog:http://sebastianconcept.com LinkedIn:http://www.linkedin.com/in/sebastiansastre github:https://github.com/sebastianconcept
Re: [Pharo-dev] How production ready is Pharo on SQLServer?
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
Re: [Pharo-dev] How production ready is Pharo on SQLServer?
I'm trying to follow instructions from here: http://dbxtalk.smallworks.eu/DBXTalkDrivers/compiling-and-installing-opendbx-native-driver/ but the link about the binaries for Windows is broken suggestions? from iPad On 12/09/2014, at 17:21, Sebastian Sastre sebast...@flowingconcept.com wrote: ok, I'll try dbx and think of odbc as plan b from iPad On 12/09/2014, at 17:14, Chris Cunningham cunningham...@gmail.com wrote: DBXTalk is probably right. If it doesn't work, the old ODBC package will work - I have tried that later one (not against SQLServer, but it's just ODBC, so will work). -cbc On Fri, Sep 12, 2014 at 12:37 PM, Esteban A. Maringolo emaring...@gmail.com wrote: 2014-09-11 17:48 GMT-03:00 Sebastian Sastre sebast...@flowingconcept.com: Hi guys, there is a project that needs to connect and extend a system using SQLServer and Pharo is being considered for the backend. Just checking, how ready are we for a production-ready backend using Pharo on SQLServer? (absent) and how do we connect to it? DBXTalk, probably, but really can't tell. Database connectivity, other than PostgreSQL seems to be rather infrequent, if used at all. Esteban A. Maringolo
Re: [Pharo-dev] How production ready is Pharo on SQLServer?
2014-09-11 17:48 GMT-03:00 Sebastian Sastre sebast...@flowingconcept.com: Hi guys, there is a project that needs to connect and extend a system using SQLServer and Pharo is being considered for the backend. Just checking, how ready are we for a production-ready backend using Pharo on SQLServer? (absent) and how do we connect to it? DBXTalk, probably, but really can't tell. Database connectivity, other than PostgreSQL seems to be rather infrequent, if used at all. Esteban A. Maringolo
Re: [Pharo-dev] How production ready is Pharo on SQLServer?
DBXTalk is probably right. If it doesn't work, the old ODBC package will work - I have tried that later one (not against SQLServer, but it's just ODBC, so will work). -cbc On Fri, Sep 12, 2014 at 12:37 PM, Esteban A. Maringolo emaring...@gmail.com wrote: 2014-09-11 17:48 GMT-03:00 Sebastian Sastre sebast...@flowingconcept.com : Hi guys, there is a project that needs to connect and extend a system using SQLServer and Pharo is being considered for the backend. Just checking, how ready are we for a production-ready backend using Pharo on SQLServer? (absent) and how do we connect to it? DBXTalk, probably, but really can't tell. Database connectivity, other than PostgreSQL seems to be rather infrequent, if used at all. Esteban A. Maringolo
Re: [Pharo-dev] How production ready is Pharo on SQLServer?
ok, I'll try dbx and think of odbc as plan b from iPad On 12/09/2014, at 17:14, Chris Cunningham cunningham...@gmail.com wrote: DBXTalk is probably right. If it doesn't work, the old ODBC package will work - I have tried that later one (not against SQLServer, but it's just ODBC, so will work). -cbc On Fri, Sep 12, 2014 at 12:37 PM, Esteban A. Maringolo emaring...@gmail.com wrote: 2014-09-11 17:48 GMT-03:00 Sebastian Sastre sebast...@flowingconcept.com: Hi guys, there is a project that needs to connect and extend a system using SQLServer and Pharo is being considered for the backend. Just checking, how ready are we for a production-ready backend using Pharo on SQLServer? (absent) and how do we connect to it? DBXTalk, probably, but really can't tell. Database connectivity, other than PostgreSQL seems to be rather infrequent, if used at all. Esteban A. Maringolo
[Pharo-dev] How production ready is Pharo on SQLServer?
Hi guys, there is a project that needs to connect and extend a system using SQLServer and Pharo is being considered for the backend. Just checking, how ready are we for a production-ready backend using Pharo on SQLServer? and how do we connect to it? sebastian o/
