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 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" > An: "Pharo Development List" > Betreff: Re: [Pharo-dev] How production ready is Pharo on SQLServer? > On Sep 19, 2014, at 8:05 AM, Torsten Bergmann 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 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?
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" An: "Pharo Development List" 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?
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?
On Sep 19, 2014, at 8:05 AM, Torsten Bergmann 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?
On 19 Sep 2014, at 13:53, Sean P. DeNigris wrote: > Tudor Girba-2 wrote >> package: 'ConfigurationOfOpenDBXDriver'; >> load. >> #ConfigurationOfOpenDBXDriver asClass loadBleedingEdge. > > configurationOf: 'OpenDBXDriver'; > loadVersion: #bleedingEdge. > > :) Yes please. Check out #configuration, #loadStable, #loadDevelopment as well - only group loading is not there.
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?
Hi Sebastian, The DLL's have to be where Pharo VM is or in the subfolder where the other DBX libs are. If in doubt put it (temporarily) into C:/Windows/System32 as they will be found always then. No need to restart the OS but you should reopen Pharo VM with image. You can find the missing DLL here: http://web.archiveorange.com/archive/v/a1gO5ZoJReyXr6T4n5m7 Fortunately I bookmarked this one after going through the same hazzle. You have to download "libsybdb-5.xxx" and rename to "libsybdb-5.dll". I went through all this pain in the past - very time wasting and not what I would call user friendly. It is really a pity that no one is able to care about setting up a CI to build all the necessary stuff ... 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. Hope this helps in your OpenDBX adventure... When successfull maybe you could put a tutorial and all the necessary binaries on a webpage/github/... afterwards. Dont know how long the archive remembers the thread with the missing DLL. Thx T. Gesendet: Freitag, 19. September 2014 um 12:46 Uhr Von: "Sebastian Sastre" An: "Pharo Development List" Betreff: Re: [Pharo-dev] How production ready is Pharo on SQLServer? Thank you tudor that one loads. Answering a question about documentation mentions: Gofer it smalltalkhubUser: 'DBXTalk' project: 'Configurations'; configurationOf 'OpenDBXDriver'; loadStable. Which is consistent with the next step about the connectionSettings preparing to run the tests. What I've experienced is that you need to 1. copy all .dll's to the pharo dir 2. set that in the windows path (reboot/relogon required) 3. try connecting Minor issue: The tests assumes that you have a postgres backend so it will complain about you not having it installed (unless you do) Changing th ecode to use MSSQL leads to the apparently known issue of Windows complaining about libsybdb-5.dll More info here: http://lists.squeakfoundation.org/pipermail/squeakdbx/2011-March/000337.html As soon as you evaluate connect, you see that problem | conn connectionSettings | connectionSettings := (DBXConnectionSettings host: 'localhost' port: '1433' database: 'dbxtest' userName: 'sa' userPassword: '123'). conn := DBXConnection platform: DBXMSSQLBackend new settings: connectionSettings. conn connect. conn open. I have to continue later but I don't have clear yet where or who is suposed to install libsybdb-5.dll any help appreciated and thanks for the helpful tips in your replies seb o/ ---------------- De: "Tudor Girba" Para: "Pharo Development List" Enviadas: Sexta-feira, 19 de setembro de 2014 4:35:31 Assunto: 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 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[http://smalltalkhub.com/#!/~DBXTalk/Configurations] (or in the configuration browser) cheers, Esteban On 19 Sep 2014, at 08:00, Stephan Eggermont wrote: > Sebastian, that doesn't look like the correct one > DBXTalk is on smalltalkhub > > https://ci.inria.fr/dbxtalk/[https://ci.inria.fr/dbxtalk/] > > Stephan > -- www.tudorgirba.com[http://www.tudorgirba.com] "Every thing has its own flow"
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 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 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?
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 wrote: > 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?
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?
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 > wrote: > > ok, I'll try dbx and think of odbc as plan b > > from iPad > >> On 12/09/2014, at 17:14, Chris Cunningham 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 >>> wrote: >>> 2014-09-11 17:48 GMT-03:00 Sebastian Sastre : >>> > 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 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 >> wrote: >> 2014-09-11 17:48 GMT-03:00 Sebastian Sastre : >> > 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 wrote: > 2014-09-11 17:48 GMT-03:00 Sebastian Sastre >: > > 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 : > 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
