Smalltalk › Gemtalk › Gemstone/S

SQL

_‹ Previous Topic Next Topic _›

Classic

List

Threaded

21 messages Options

Thierry Thelliez-2

SQL

Hello all,

For some complicated reasons, one of our customers wish to have our data in an SQL database. There is no real requirements, just politics and perception…

But this is not necessarily incompatible with a GemStone based solution. What are the options to connect GemStone to a relational database? The idea would be to export the data on a daily or weekly basis.

Is there any OR mapping system that could be used directly from GemStone? Could MySQL be connected to?

Thanks,

Thierry Thelliez

Bill Erickson

Re: SQL

Thierry Thelliez wrote:
> What are the options to connect GemStone to a relational database?

GemStone supports a product called GemConnect that allows GS to connect
to an Oracle database. An older open-source version is available for
Sybase.

> Is there any OR mapping system that could be used directly from
> GemStone?

As part of a consulting engagement I developed an OR mapping framework
built on top of GemConnect, but was limited to mapping from GemStone to
Oracle (but not back). If you're curious about it I can provide
additional documentation. As this was done for another customer, I'd
have to check to see if we can provide this for free or if there would
be a charge for the framework.

Regards,

--
------------------------------------------------------------------------
Bill Erickson VMware/SpringSource/GemStone/Smalltalk Engineering
Software Engineer 1260 NW Waterhouse Ave #200
[hidden email] Beaverton OR 97006
------------------------------------------------------------------------

Normand Mongeau

Re: SQL

In reply to this post by Thierry Thelliez-2

Thierry,

a few years back, I had a working version (although experimental) of GemConnect for ODBC. I developed it in fact with MySQL as a back end.

If you're interested, I can give you the code.

Normand

On 2010-08-26 13:48, Thierry Thelliez wrote:

Hello all,

For some complicated reasons, one of our customers wish to have our data in an SQL database. There is no real requirements, just politics and perception…

But this is not necessarily incompatible with a GemStone based solution. What are the options to connect GemStone to a relational database? The idea would be to export the data on a daily or weekly basis.

Is there any OR mapping system that could be used directly from GemStone? Could MySQL be connected to?

Thanks,

Thierry Thelliez

jgfoster

Re: SQL

In reply to this post by Thierry Thelliez-2

<base href="x-msg://23/">Hi Thierry,

How useful does the data need to be in the SQL database? If it is simply a requirement that you use the preferred technology, then you can play that game.

One approach I've suggested before (only partly in jest), is to modify GemStone's disk subsystem so that instead of writing 16 KB pages to the file system, they would instead be written to 16 KB blob fields in an Extent table in the SQL database of your choice. (There would also be a 512 byte blob field in the Transaction table.) In this way GemStone would no longer be infringing on the database territory and would simply be an application server. Backups, etc, would be the responsibility of the Oracle DBA. Of course, we would all know better (and the performance would suffer, but maybe not in a measurable way).

Another approach is to create a table for every class in which the primary key is the OOP and the remaining columns are the OOPs of the named instance variables. Classes with numbered instance variables would have another table with object OOP, index, and value OOP. There would be tables for each of the fundamental types (Integer, String, Float, DateAndTime, etc.). Finally, an Object table that maps OOP to Class should you have everything you need. Years ago when I was a GemStone customer, we were told to give someone all our data in a SQL format--and this is what I did.

James

On Aug 26, 2010, at 10:48 AM, Thierry Thelliez wrote:

Hello all,

For some complicated reasons, one of our customers wish to have our data in an SQL database. There is no real requirements, just politics and perception…

But this is not necessarily incompatible with a GemStone based solution. What are the options to connect GemStone to a relational database? The idea would be to export the data on a daily or weekly basis.

Is there any OR mapping system that could be used directly from GemStone? Could MySQL be connected to?

Thanks,
Thierry Thelliez

Paul Baumann

RE: SQL

In reply to this post by Thierry Thelliez-2

Hi Thierry,

It is a common "requirement". An RDB is useful for archival data and reporting. It will be easy unless you get compelled to read RDB data back into GS--like if the RDB is to be the dB of record and GS is just a cache, or if they think they need the RDB to replicate data between GS environments. They probably just want the data in a form they are comfortable with.

A typical way to implement it is to add objects to an RcQueue when they change. An rdb manager consumes the queue to feed the RDB. GemConnect has worked well in applications I've used it. You'll need to check and handle errors in the queue. RcQueue sequence issues (which can violate RDB data constraints) had been a problem on one application, but that depends on the RDB constraints and GS/S version. Each table tends to correspond to a domain class. You may only need some data outside of GS.

Let them know how much an RDB will increase their license and maintenance costs and they may come to realize that the fastest and simplest way is just fine. You could offer to export the data to files (XML, CSV, whatever) and then let them figure out how to read it into the RDB they want to maintain. Since there is no real requirement, the XML export file may be the furthest you need to go for the data to feel accessible to them. Or, get them to invest in the construction of a dynamic query language environment--and give them a workspace with instructions for common queries. Run transactionless. As long as you don't tell them it is Smalltalk they are typing then they'll think you've done a lot of work. :)

Paul Baumann

From: [hidden email] [mailto:[hidden email]] On Behalf Of Thierry Thelliez
Sent: Thursday, August 26, 2010 1:49 PM
To: GemStone Smalltalk Customer Forum
Subject: [gemstone-smalltalk] SQL

Hello all,

For some complicated reasons, one of our customers wish to have our data in an SQL database. There is no real requirements, just politics and perception…

Is there any OR mapping system that could be used directly from GemStone? Could MySQL be connected to?

Thanks,

Thierry Thelliez

This message may contain confidential information and is intended for specific recipients unless explicitly noted otherwise. If you have reason to believe you are not an intended recipient of this message, please delete it and notify the sender. This message may not represent the opinion of IntercontinentalExchange, Inc. (ICE), its subsidiaries or affiliates, and does not constitute a contract or guarantee. Unencrypted electronic mail is not secure and the recipient of this message is expected to provide safeguards from viruses and pursue alternate means of communication where privacy or a binding message is desired.

prakash karnawal

Re: SQL

In reply to this post by Thierry Thelliez-2

Thanks Bill for sharing such type of knowledges.

We appreciate and hope the same response in future also.

Regards

Prakash

On Thu, Aug 26, 2010 at 2:32 PM, Bill Erickson <[hidden email]> wrote:

Thierry Thelliez wrote:

What are the options to connect GemStone to a relational database?

GemStone supports a product called GemConnect that allows GS to connect to an Oracle database. An older open-source version is available for Sybase.

Is there any OR mapping system that could be used directly from GemStone?

As part of a consulting engagement I developed an OR mapping framework built on top of GemConnect, but was limited to mapping from GemStone to Oracle (but not back). If you're curious about it I can provide additional documentation. As this was done for another customer, I'd have to check to see if we can provide this for free or if there would be a charge for the framework.

Regards,

--
------------------------------------------------------------------------
Bill Erickson VMware/SpringSource/GemStone/Smalltalk Engineering
Software Engineer 1260 NW Waterhouse Ave #200
[hidden email] Beaverton OR 97006
------------------------------------------------------------------------

--
Regards!

Prakash Singh karnawal

Thierry Thelliez

Re: SQL

In reply to this post by Thierry Thelliez-2

Thanks all for the quick answers. You all have many interesting views and options.

In a meeting this afternoon, we were asked about SQLServer support. Someone in a CIO office loves Microsoft... Has anyone dealt with that platform?

In that particular project, the data schema is relatively simple. But the schema might change often and the business logic is growing quickly. So maybe we should try to convince them of a nightly CSV export... More later.

Thierry Thelliez

Mark Pirogovsky-3

Re: SQL

In reply to this post by Thierry Thelliez-2

About 10 years ago Gemstone used to sell product called GEMConnectect
ODBC - where Gemstone was used as ODBC data source. One had to set up
some specialized classes to behave like the relational tables ...
I did use it with Cristal Reports for some time. I am not sure it is
still available though

Normand Mongeau wrote:

> Thierry,
>
> a few years back, I had a working version (although experimental) of
> GemConnect for ODBC. I developed it in fact with MySQL as a back end.
>
> If you're interested, I can give you the code.
>
> Normand
>
>
>
> On 2010-08-26 13:48, Thierry Thelliez wrote:
>>
>> Hello all,
>>
>> For some complicated reasons, one of our customers wish to have our
>> data in an SQL database. There is no real requirements, just politics
>> and perception…
>>
>> But this is not necessarily incompatible with a GemStone based
>> solution. What are the options to connect GemStone to a relational
>> database? The idea would be to export the data on a daily or weekly
>> basis.
>>
>> Is there any OR mapping system that could be used directly from
>> GemStone? Could MySQL be connected to?
>>
>> Thanks,
>>
>> Thierry Thelliez
>>

Thierry Thelliez

Re: SQL

In reply to this post by Thierry Thelliez-2

I got a little bit more information about some expectations.

Even though our customer does not really know how the data will be used, someone in a CIO office wants the data in SQL for later using some reporting tools like Microsoft SQLServer Reporting services (they seem to like Microsoft stack there...).

What do you use for classic reporting functionality? (Directly from GemStone or from an export to an SQL database)

Thanks,
Thierry Thelliez

Bruce Badger

Re: SQL

In reply to this post by Thierry Thelliez-2

HI,

The PostgreSQL drivers I wrote work in GemStone thanks to Sport.
This means you can access PostgreSQL databases from any gem running on
any box as long as it can open a socket to the PostgreSQL back end.
No DLLs or SOs needed.

I use GemStone as the definitive operational repository and PostgreSQL
as a warehouse for flat data I want to search quickly and for data
external systems wish to read using SQL. Pretty much what you are
looking to do.

All the best,
Bruce

On 26 August 2010 18:48, Thierry Thelliez <[hidden email]> wrote:

> Hello all,
>
>
>
> For some complicated reasons, one of our customers wish to have our data in
> an SQL database. There is no real requirements, just politics and
> perception…
>
>
>
> But this is not necessarily incompatible with a GemStone based solution.
> What are the options to connect GemStone to a relational database? The idea
> would be to export the data on a daily or weekly basis.
>
>
>
> Is there any OR mapping system that could be used directly from GemStone?
> Could MySQL be connected to?
>
>
>
>
>
> Thanks,
>
> Thierry Thelliez

--
Make the most of your skills - with OpenSkills
http://www.openskills.org/

Paul Baumann

Re: SQL

In reply to this post by Thierry Thelliez-2

We have a range of reports. The "classic" (read on-paper) reports are from SQL. From VW clients, many reports are first to tables (DataSet) views because they show columns of data well and are easy to create. From VW DataSet views we offer the ability to export to Excel. Some reports are colored text opened in tabbed workspaces. Some reports are streamed to strings and opened in MS Word. Some reports are delivered as emails. What we don't use is any *direct* write to a printer from Smalltalk. The functionality is built into VW, but it isn't something I've seen used. It isn't hard to add SQL-like queries to GS, but going from GS directly to paper can be. I'd love to be able to report directly to PDF or printer from GS. Most reports never get put onto paper.

Paul Baumann

From: [hidden email] [mailto:[hidden email]] On Behalf Of Thierry Thelliez
Sent: Saturday, August 28, 2010 12:05 PM
To: GemStone Smalltalk Customer Forum
Subject: [gemstone-smalltalk] Re: SQL

Georg Gollmann

Re: SQL

In reply to this post by Thierry Thelliez-2

Am 30.08.2010 um 16:11 schrieb Paul Baumann:

> I'd love to be able to report directly to PDF or printer from GS.

We create PostScript in GemStone and either send it to the printer or - increasingly - generate PDFs by piping it through ghostscript via "System performOnServer:"

Kind regards
Georg

Bruce Badger

Re: SQL

In reply to this post by Thierry Thelliez-2

The SPDF library directly writes PDF files from Smalltalk. Also
works in GemStone.

On 30 August 2010 15:11, Paul Baumann <[hidden email]> wrote:

> We have a range of reports. The "classic" (read on-paper) reports are from
> SQL. From VW clients, many reports are first to tables (DataSet) views
> because they show columns of data well and are easy to create. From VW
> DataSet views we offer the ability to export to Excel. Some reports are
> colored text opened in tabbed workspaces. Some reports are streamed to
> strings and opened in MS Word. Some reports are delivered as emails. What we
> don't use is any *direct* write to a printer from Smalltalk. The
> functionality is built into VW, but it isn't something I've seen used. It
> isn't hard to add SQL-like queries to GS, but going from GS directly
> to paper can be. I'd love to be able to report directly to PDF or printer
> from GS. Most reports never get put onto paper.
>
> Paul Baumann
>
> ________________________________
> From: [hidden email]
> [mailto:[hidden email]] On Behalf Of
> Thierry Thelliez
> Sent: Saturday, August 28, 2010 12:05 PM
> To: GemStone Smalltalk Customer Forum
> Subject: [gemstone-smalltalk] Re: SQL
>
> I got a little bit more information about some expectations.
>
> Even though our customer does not really know how the data will be used,
> someone in a CIO office wants the data in SQL for later using some reporting
> tools like Microsoft SQLServer Reporting services (they seem to like
> Microsoft stack there...).
>
> What do you use for classic reporting functionality? (Directly from GemStone
> or from an export to an SQL database)
>
>
> Thanks,
> Thierry Thelliez
>
>
>
>
> ________________________________
> This message may contain confidential information and is intended for
> specific recipients unless explicitly noted otherwise. If you have reason to
> believe you are not an intended recipient of this message, please delete it
> and notify the sender. This message may not represent the opinion of
> IntercontinentalExchange, Inc. (ICE), its subsidiaries or affiliates, and
> does not constitute a contract or guarantee. Unencrypted electronic mail is
> not secure and the recipient of this message is expected to provide
> safeguards from viruses and pursue alternate means of communication where
> privacy or a binding message is desired.
>

--
Make the most of your skills - with OpenSkills
http://www.openskills.org/

chh

Re: SQL

In reply to this post by Normand Mongeau

Hello, everyone!

I met the same problem as Thierry. One of our customers would like to integrate GemStone with other systems using SQL interface or web services. How to do it conveniently? It is better there is less development work...

@Normand,

Could you please share your code with me? Thanks!

Thank you in advance!

Best regards,
Chen Hui

Bruce Badger

Re: SQL

Using the Smalltalk Postgres drivers means you can access a database without the need for vendor DB libraries on the client host. So direct from Gem -> RDBMS.

Nice.

On 20 August 2013 10:06, chh <[hidden email]> wrote:

Hello, everyone!

I met the same problem as Thierry. One of our customers would like to
integrate GemStone with other systems using SQL interface or web services.
How to do it conveniently? It is better there is less development work...

@Normand,

Could you please share your code with me? Thanks!

Thank you in advance!

Best regards,
Chen Hui

--
View this message in context: http://forum.world.st/SQL-tp2340139p4704272.html
Sent from the Gemstone/S mailing list archive at Nabble.com.
_______________________________________________
GemStone-Smalltalk mailing list
[hidden email]
http://lists.gemtalksystems.com/mailman/listinfo/gemstone-smalltalk

--
Make the most of your skills - with OpenSkills
http://www.openskills.org/

_______________________________________________
GemStone-Smalltalk mailing list
[hidden email]
http://lists.gemtalksystems.com/mailman/listinfo/gemstone-smalltalk

chh

Re: SQL

Hi, Nice.

Thanks for your reply!

I am a freshman here. Could you please let me know how to find the Smalltalk Postgres drivers and how to do in the next step?

Thank you.

Best regards,
Chen Hui

Bruce Badger

Re: SQL

I'm on a vacation in Scotland where I have ~no internet. I'll send you the .gs filles you'll need when I get back.

Bruce

Sent from my phone. Please forgive brevity.

On 21 Aug 2013 06:28, "chh" <[hidden email]> wrote:

Hi, Nice.

Thanks for your reply!

I am a freshman here. Could you please let me know how to find the Smalltalk
Postgres drivers and how to do in the next step?

Thank you.

Best regards,
Chen Hui

--
View this message in context: http://forum.world.st/SQL-tp2340139p4704414.html
Sent from the Gemstone/S mailing list archive at Nabble.com.
_______________________________________________
GemStone-Smalltalk mailing list
[hidden email]
http://lists.gemtalksystems.com/mailman/listinfo/gemstone-smalltalk

_______________________________________________
GemStone-Smalltalk mailing list
[hidden email]
http://lists.gemtalksystems.com/mailman/listinfo/gemstone-smalltalk

chh

Re: SQL

Hi, Bruce,

Thanks a lot for your help!

Best regards,
Chen Hui

Bruce Badger

Re: SQL

Hi - I'm back from Scotland now (smashing trip btw)

You can find the drivers on Sourceforge:
https://sourceforge.net/projects/st-postgresql/

Let me know if you need any help

On 23 August 2013 02:20, chh <[hidden email]> wrote:

Hi, Bruce,

Thanks a lot for your help!

Best regards,
Chen Hui

--

View this message in context: http://forum.world.st/SQL-tp2340139p4704701.html

Sent from the Gemstone/S mailing list archive at Nabble.com.
_______________________________________________
GemStone-Smalltalk mailing list
[hidden email]
http://lists.gemtalksystems.com/mailman/listinfo/gemstone-smalltalk

--
Make the most of your skills - with OpenSkills
http://www.openskills.org/

_______________________________________________
GemStone-Smalltalk mailing list
[hidden email]
http://lists.gemtalksystems.com/mailman/listinfo/gemstone-smalltalk

Pieter Nagel-3

Re: SQL

Let me take this opportunity to thank Bruce for these PostgreSQL drivers
- assuming these are the same as what he emailed me some two years ago.

I needed them to help export our system data to a SQL database after our
company was bought, and as soon as that was done I was made redundant.
So I never had the time to make the "experience report" blog post that
he asked for.

Nevertheless, his code was robust, and very useful to us for that task
at that time.

The "reverse Object-Relational mapping problem" is very interesting
indeed, and not as often tackled in the Object -> Relational direction.

--
Pieter Nagel

_______________________________________________
GemStone-Smalltalk mailing list
[hidden email]
http://lists.gemtalksystems.com/mailman/listinfo/gemstone-smalltalk