Praise (and a few questions)

[Former Member]

<p>I love what you have done with CAL and GemCutter. Having a functional language and still being able to use the whole Java set of libraries is marvellous. Only Scala offers elements of this. And Visual Programming has always struggled, but given the composability of a functional language finally makes it work.</p><p>Great job CAL team, and thank you!</p><p>Now a couple of low level questions. I was experimenting with GemCutter, in particular connecting to MS SQL Server using the jtds jdbc driver. It almost works! I used the jdbcConnectionWithDriverCheck and four Value Gems for input. I wired the output to a jdbcQueryToResultSet Gem. With a query &#39;SELECT * FROM Member&#39; (in another Value Gem) I get all the columns names correctly, but the table below is empty! If I add a resultSetToText Gem, I get the textual representation of the result set, but now the result set seems to grind the system to a halt (I have ~9000 rows of name, address and lots of other attributes).</p><p>I also notice that in the scope window, that on occasion, when I change the query in the Value Gem, the code does not change up there. Nor does the system even recognize that the Value Gem value has changed (query results). Even saving my Gem does not get it to recognize.</p><p>Keep up the great work, and thanks for any help.</p><p>Alex</p>

[Former Member]

Hi Alex, <p class="MsoPlainText">Thanks very much for your feedback. It&#39;s great to hear from someone using and enjoying CAL and the GemCutter.</p> <p class="MsoPlainText">Now for your specific questions: </p> <p class="MsoPlainText">I haven&#39;t been able to reproduce the problems with the resultSetText gem. I have tested with a Derby database containing 20,000 address records without any significant delays in the UI. Does the system go slowly when the gems are actually running, or when you expand the resultSet string? You could try using the rowCount gem instead of the resultSetText gem to see if the problem is with the display, or with the database connection.</p> <p class="MsoPlainText">One thing you may be running into is that some of the result value panels in the GemCutter have scalability issues with very large datasets. Note that this is not an issue with the underlying CAL data processing. We have used CAL and the SQL data library extensively with large databases. <br /></p> <p class="MsoPlainText">I have experimented with the scope window and think I may have found the issue that has been causing you problems. If you enter a value into an editor and before pressing enter click away, for example on the scope window or the play button, it will not update. However, it should update as soon as you click on the table top area again, or if you press enter after editing the value. We will look into addressing this issue.</p> <p class="MsoPlainText">Please let us know if the problems persist and we can look into them further.</p> <p class="MsoPlainText">Thanks, </p><p class="MsoPlainText">Magnus.</p>

[Former Member]

You might also be interested in using the Generate -> Generate JDBC Data Source menu item in the GemCutter. This provides a wizard for building a gem that runs a database query and outputs a list of CAL records. It is helpful if you want to reproduce the content from the Gem Cutter videos.

Before using the menu item a connection gem must exist in the workspace. The connection gem establishes a connection to a JDBC data source. The simplest way to create a connection gem is to use the DataGems.jdbcConnectionWithDriverCheck gem. This gem takes as input the jdbc driver location (i.e. classpath), the url of a jdbc database resource, and the login id/password for accessing the database.Drag the jdbcConnectionWithDriverCheck gem onto the GemCutter table top. Drop four value gems (i.e. the blue rectangle in the tool bar) onto the desktop and connect one to each of the inputs of jdbcConnectionWithDriverCheck. Fill in the values for the four inputs.

For example, to use the Sun JDBC to ODBC driver to connect to an ODBC data source named âu20ACu02DCXtreme Sample Database 10âu20AC(tm) the inputs would be:

1) sun.jdbc.odbc.JdbcOdbcDriver

2) jdbc:odbc:Xtreme Sample Database 10

3) username

4) password

Connect the output of jdbcConnectionWithDriverCheck to the âu20ACu02DCresultâu20AC(tm) target on the table top. Right click on âu20ACu02DCresultâu20AC(tm) and choose âu20ACu02DCRename Gemâu20AC(tm). Provide a new name, ex. xtremeConnection. Right click on the target, choose âu20ACu02DCSave Gemâu20AC(tm), and save the gem as public. This will save the xtremeConnection gem into the current module, which will probably be GemCutterSaveModule.

At this point the gems on the table top can be deleted.

From the menu choose Generate | JDBC Data Sourceâu20AC¦

Provide a name for the new gem which will be generated and, optionally, a comment. Remember that gem names cannot be capitalized. Check the âu20ACu02DCInclude Record Extractor Gemâu20AC(tm) check-box and continue to the next dialog page.

Pull down the âu20ACu02DCConnection Gemâu20AC(tm) list box and select the previously created connection gem. In the âu20ACu02DCSQL Builder Gemâu20AC(tm) list select the SQL builder gem that best matches your data source (i.e. ODBC, Oracle, MS Access, etc.). If you are not sure select SqlBulder.defaultSqlBuilder. Continue to the next dialog page.

Select a table from the list of tables provided. These are the tables in the database accessed by the connection gem. Continue to next dialog page

Select the fields of interest from the list of available fields and transfer to the Selected Fields list. The fields in the Selected Fields list can be re-ordered as desired. Continue to the next dialog page.

Select the fields, and order, for sorting. Continue to next dialog page.

At this point the text of the generated SQL query is displayed for review. Clicking next will preview a sample of the results of the current query.

Selecting finish will save two gems into the current module. For example if you specified the name foo at the beginning of the dialog the gems saved will be foo and fooRecords.

The foo gem will run the SQL query and produce an instance of DataGems.ResultSet.

The fooRecords gem is usually more useful as it will produce a list of records. Each record corresponds to a row in the query result set and contains fields for each column in the result set. The fields will have names and types corresponding to the columns of the result set.

There is more information and examples in the CAL tutorial module Cal.Tutorials.DataProcessing, which is included in the Quark download. This demonstrates much of the content from the GemCutter videos.

Magnus.