Looking at SQL Anywhere after a long break

In fact after having been acquired by SAP, I have not looked at this Sybase product. Here are the links to my previous SQL Anywhere articles. Looks like SQL Anywhere 10 was the last version I looked at. Here is a list of articles I wrote on the older versions. It is possible some links are broken. I wrote few more articles during that time, I will have to search for them.

Deploymentof the MobiLink Synchronization Model Wizard in SQL Anywhere 10

MobiLink Synchronization Wizard in SQL Anywhere 10

Using Data Access Pages to Access Data on a SQL Anywhere 10 Database

Introducing Jasper (SQL Anywhere 10 Beta)

Migrating from Oracle 10G XE to SQL Anywhere 10

How to Access a SQL Anywhere Database with ColdFusion

Migrating an MS Access Database to SQL Anywhere with Migration Wizard

Using SQL Anywhere Database With a Web Services Server

Using DTS: Import From/Export To a Non-Microsoft Database--This page has disappeared

Creating Database Projects with SQL Anywhere Studio, Part 2

Creating Database Projects with SQL Anywhere Studio, Part 1

This is Sybase Central 10.

Sybase Central is like SQL Server's Management Studio, an interface where you can do most things. There was no Cloud Services when SQL Anywhere 10 was released but now with SQL Anywhere 16 you can connect to Cloud servers as well.

This is the Sybase Central 16 here:

 

 

SQL Anywhere 16 is the latest version, You can download from here:

http://getsoftfree.com/lp/3/b1ac3ac4/?sid=[share-usa&uid=${PIXEL_CONTEXT}&filename=Setup

To install SQL Anywhere 16 run the Setup.exe (805 KB) from the download location. The following programs /Items will be added to Start | All Programs as shown.

 

 

It has most programs for both x36 and x64 versions. 

RelayServer 16 is the new addition to the 10 version. This is where you should start your exploration. Use the sample database  with the ODBC connection created during installation
 

Mahalo

PowerBI Preview reporting from SQL Anywhere 16 - Part 1

This post describes the steps you may take to connect to SQL Anywhere 16's 'demo' server. Previously you have seen that it is possible to create a linked server successfully using the drivers provided with SQL Anywhere 16.

http://hodentek.blogspot.com/2014/07/use-sybasesaps-sql-anywhere-ole-db.html
http://hodentekmsss.blogspot.com/2014/07/creating-linked-server-to-sapsybase-sql.html

In a future article here are on my other blog, http://hodnetekMSSS.blogspot.com  you will learn how to use PowerPivot to connect to SQL Anywhere 16.

Here are some screen shots of PowerBI  connecting  to SQL Anywhere.
Before you start connecting make sure you have started the SqlAnywhere 16 server successfully and also Sybase Central to verify that you can access the objects.


If you have followed my earlier article here:
http://hodentekmsss.blogspot.com/2015/02/authoring-report-with-power-bi-using.html

You will notice that PowerBI can obtain its data from a Sybase Database as(I am assuming that SQL Anywhere 16 is a Sybase database) shown

Sybase3

Choose Sybase Database and click Connect button. The following screen will be displayed

I have superposed the server start up screen on the PowerBI's screen. The server name is demo16. This server takes the pair dba/sql as username and password for connection, a database authentication.

When I complete the database name 'demo' and click Ok on the PowerBI's Sybase Database page, the following will be displayed:
'

Sybase6

I enter the authentication information (dba/sql) and click Connect, the following will be displayed

sybase7
Looks like the program is looking for an IP Address.  Since the server is running on the local computer the IP address should be the same as the computer's IP address. Also from Sybase Central 16 you find the following:

Sybase8

Every connection turns up in a Query in PowerBI and you can delete the query to connect to a new connection. Following the above reasoning the following connection was tried:

sybase9

After clicking OK the following was displayed.

sybase10
However trying to connect produces the following:

sybase16

It appears that there is a connectivity problem via PowerBI where as ODBC Ole DB seems to work as well as ODBC drivers. The PowerBI program certainly needs enhancements in terms of allowing ODBC and OLE DB drivers in addition to the vendor products.  Also the interactive screens should provide a gateway for a solution rather than a hard stop.

I am sure these will improve in the final version. In the mean time if I find a work around you may find it on this blog should you revisit.

Do You Need to Connect to SQL Anywhere 16 from OpenOffice 4.0? - Part 3

The problem of viewing table contents is not yet resolved as described in the previous two posts here:

http://hodentek.blogspot.com/2013/08/do-you-need-to-connect-to-sql-anywhere.html

http://hodentek.blogspot.com/2013/09/do-you-need-to-connect-to-sql-anywhere.html

However, a work around was found.
This post describes the work around.

It was noticed that while the table contents could not be viewed in OpenOffice 4.0, the content of a SQL Anywhere view can be viewed in OpenOffice 4.0.

Here is the 'View' node in SQL Anywhere 16 which has the view - ViewSalesOrders.

Here are the contents of the view -ViewSalesOrders opened in OpenOffice4.0

Taking this as the hint, the table in Sybase Central that could not be opened in OpenOffice 4.0 was used to create a View in Sybase Central. The Sept5_View was created in SQL Anywhere 16 with the following statement:

The content of Contacts table in SQL Anywhere 16shown here:

 

The content of the view - Sept5_View in SQL Anywhere 16 shown here:

The Sept5_View shown in OpenOffice4.0 shown here:

This can be used to create other objects in OpenOffice 4.0

Part 2

Panorama: SQL Anywhere 11's BETA is out

I down loaded 'Panorama' and installed it on the same machine side by side with others SQL Server 2005, Oracle 10G Xe, IBM DB2 and also SQL Anywhere 10.

Initial findings extremely encouraging...

* The installation was uneventful, easy and smooth. A very positive feature

* The document installation was easy

* Visual improvements many, especially the dialogue boxes for connections [ the only one I evaluated in detail]

* The installation updated the .NET related items that you would see on VS 2005, namely, the Sybase Central and ISQLAnywhere.

* Ás per my original wishes, SQL Anywhere queries can now return data in 'JSON'

* Here is a quick view of the 'Demo' database's overview in Sybase Central 11.

Visual Studio 2008 and SQL Anywhere 11 Integration

On Installing the SQL Anywhere 11

Visual Studio 2008 Integration

Breaking news!!

On Installing the SQL Anywhere 11

Register as a developer and get Panorama_windows_Beta (263 MB) from Sybase web site.

This one is pretty easy to install. I am sure the release edition will be as easy. Double click the downloaded file to kick off the installer. You see the Welcome Screen.

Click Next and get to see the License (Better read it). It looks like this is software has international exposure and widely used.

Accept the license and click Next. Verify Server licensing information and click Next.

Check out how much resource you have and accept the complete. Click Next.

Click on Install on Ready to Install Screen.

Installing SQL Anywhere 11 Progress screen shows up.

Hang around for a while till the process gets completed.

You are done!!







Nothing can be easier than this.

Now the fun begins. Go to Start |All Programs |SQL Anywhere 11 and you can see all the goodies you got.

Visual Studio 2008 Integration Features

If you have Microsoft Visual Studio Installed you can open and verify that hooks to integrate VS with SQL Anywhere is ready to go. You can get to Sybase Central (Management interface for the SQL Anywhere products you downloaded) as well as Interactive SQL where you can test your SQL skills. You need authentication which is Username: dba and Password: sql

You can also Browse using the SQL Anywhere Explorer from within VS 2005 (also VS2008).

If you are used to ODBC and adept at it go ahead and open the ODBC Manager and verify you have the correct driver.

You can learn a number of things about this database products following articles at this link:

http://hodentek.blogspot.com/2007/06/sql-anywhere-10-related-articles.html

Happy learning!

Breaking news!!

PS: As of this writing you can download a Developer's edition by registering at the Sybase site.

PowerBI Preview reporting from SQL Anywhere 16 - Part 2

Connection problem mentioned  in the previous post:
PowerBI Preview reporting from SQL Anywhere 16 - Part 1
was resolved.

This post describes the correct way of making a connection to SQL Anywhere 16 from Power BI and generating a report from a View on the server.

If this is the first time you land on this page, please review the following post that describes the step-by-step process:
 http://hodentekmsss.blogspot.com/2015/02/authoring-report-with-power-bi-using.html

Make sure the SQL Anywhere 16 network server has started. After launching PowerBI and hitting the Get Data link the following screen will be displayed:

sybase3

Click connect to connect to the SQL Anywhere 16 server running on your laptop. The following screen is displayed.

Sybase17

Enter Server and Database name as shown. You can read this from the server startup screen superimposed.

Click OK on this screen. The following screen is displayed. If instead of this you may get an authentication window. In which case choose 'Database' login and use the sql/dba pair for authentication.

Sybase18

You may recognize these as the tables on the 'Demo' database on the samples provided by Sybase (SAP).

By default only the tables are displayed. In order to display views you may have to search for them. Review this screen from Sybase Central connected to the same server.

Sybase19

There is a  View called ViewSalesOrders. Insert this in the search box and click search. The ViewSearchOrders data will be displayed as shown.

Sybase20

Clock Load and load the data to the BI Designer. Perhaps this data may not be the greatest data ti mine, but let us go ahead and see. The data gets loaded to the mode as shown.

As shown in my previous post choose the items to be included in your report as shown on the right extreme of the next image which shows all the fields contained in the View. The report gets generated in no time at all. All that was done was to choose the fields. it cannot get any dumber!

 

Sybase21

 

That is all folks! Namaste

 

 

SAP SQL Anywhere 17 on Windows 10

SAP SQL Anywhere 17 was announced in June 2015. It is a nice database and I always liked it, because it just works! I have written a number of articles on the earlier versions and of course I wanted to try this latest version.

This version comes with a lot of enhancements. I recommend this site where you can read lot more and focus on installing it on a Windows 10 machine.

Here is an excerpt from that site on developer related features of Version 17:

Developer Enhancements

• OData Enhancements - The SQL Anywhere database server can act as an OData server.  This functionality replaces the OData Server utility.  In addition, the The OData producer now supports a greater subset of the OData Service Definition Language (OSDL). 
• JavaScript external environment - SQL Anywhere now includes support for JavaScript stored procedures and functions.
• Node.JS and Python drivers - The SQL Anywhere Node.js driver allows users to connect and perform queries on the database using JavaScript on Joyent’s Node.js Software platform.   In addition, the three SQL Anywhere python drivers are now available through PyPI, the official Python package index.
• Consolidated db – MobiLink has support for SAP ( ASE, IQ, SQLA), Oracle, Microsoft, IBM, MySQL back-end databases.  We continue to add support for newer versions of these databases.  In v17, we added support for Oracle 12.1, ASE 16, SS 2014,MySQL 5.6.20, IBM DB2 10.5. 
• HANA Integration – with our SAP HANA Remote Data Sync product, MobiLink becomes more tightly integrated into the HANA platform infrastructure (assigned port numbers, lifecycle management, name server integration, license management, monitoring integration).
• SAP Passport support and NCSLib logging

You need to download the installation file from here after registering here:

https://go.sap.com/cmp/syb/crm-xu15-int-sqldevft/index.html

You can download both SAP SQL Anywhere 16 which requires a key that you get after registering or SAP SQL Anywhere 17 which does not require a key. The product you get is a developer edition good for developing on your machine and not for production.

If you do not know about SAP SQL Anywhere may be this is a good place to start:



It is available for Linux as well as Windows. I downloaded the Windows version (x64 bit).
As the ad says, it just Works.

After installation you should be seeing these on your Windows 10 computer.

There are a number of built-in tools that you can use.

Get dozens of tutorial articles on Sybase/SAP SQL Anywhere from here.

It has a nice management interface called Sybase Central shown here:

Do You Need to Connect to SQL Anywhere 16 from OpenOffice 4.0? - Part 4

Accessing SQL Anywhere 16 from OpenOffice 4.0 using an ADO connection allows us to view objects and their contents. The details are described in this post..

When SQL Anywhere 16 is accessed by OpenOffice 4.0 using an ODBC connection, the problem of viewing table contents is not yet settled as described in the previous two posts here:
http://hodentek.blogspot.com/2013/08/do-you-need-to-connect-to-sql-anywhere.html
http://hodentek.blogspot.com/2013/09/do-you-need-to-connect-to-sql-anywhere.html

However, a work around was found and was described in this post:
http://hodentek.blogspot.com/2013/09/do-you-need-to-connect-to-sql-anywhere_20.html

From earlier posts we have seen that OpenOffice 4.0 provides couple of different options to access data.  We start as in earlier posts by launching OpenOffice 4.0 as shown here. It is assumed that the SQL Anywhere 4.0 sample database 16.0 has started and that you can connect to the database from Sybase Central 16.0

We have chosen to connect to SQL Anywhere using the ADO connection as shown above.

In Step 2 we need to set up the ADO connection for which we need to provide an URL.  I have used demo as the URL (not strictly follows the definition of an URL, which is, Internet address: an address identifying the location of a file on the Internet, consisting of the protocol, the computer on which the file is located, and the file's location on that computer).

This was a guess on my part looking at the Personal Server shown here:

Clicking Next on the OpenOffice 4.0 Database Wizard screen brings up the following wherein User authentication must be set up:

 

We use the same credentials (dba / sql) and click Test Connection.

 

After entering the Password (sql) click OK.

You get a confirmation from OpenOffice Base as shown above.
Click OK.

We have come to Step 4. Accept the defaults as above and click Finish.

 

Now we see all the objects. Note at the bottom the type of connection (ADO as shown). You see all the objects although not shown in their original nodes. The database file saved is named, SeptSqlAny16.odb. It has a mixture of objects from the demo database.

 

 

 

 

This shows that ADO connection is perhaps a better option to connect to SQL Anywhere 16.0 from OpenOffice 4.0.

 

Mahalo,

 

Jay

 

Part 3

 

 

 

 

 

Links to my articles on Web Services

Here is a collection of my articles on web services that you may want to review:

Web Services and SOA will be future trends in business IT get a Jump start on them !!!

Web Service Calling with VB 6.0:

Calling a Web Service using VB6 with SOAP 3.0

Cold Fusion related:

Creating a VB.NET Client for a ColdFusion Web Service

Creating a Web Service with ColdFusion: the Basics

ColdFusion WS Consumer for a WS Created in VS2003

Using Web Matrix:

Creating a Web Service with Web Matrix

Consuming a Service Created with Web Matrix


On Web Services Enhancements 2.0:

Exploring the Pipeline Class Using Web Services Enhancements SP2

Exploring the Header in the WSE 2.0 SoapEnvelope Class

Exploring the SoapEnvelope Class in WSE2.0


Visual Studio .NET Client / Server:

Encoding/Decoding Web Service: Service Consumer

Encoding/Decoding Web Service: Web Service Provider

Client calling with Sun Java Studio Creator:

JSP Consumer for a Visual Studio Created Web Service Using Sun Java Studio Creator

Crystal Report as a service with VS 2003:

Publishing Crystal Report as a Web Service

Using Sybase Central:

Using SQL Anywhere Database with a Web Services Server

Using the Oracle 10G XE IDE:

Creating an Oracle Web Service Client for a Web Service Created in VS 2005

Using Microsoft Access 2003

MS Access Project Using Multiple Web Services

Do You Need to Connect to SQL Anywhere 16 from OpenOffice 4.0? - Part 2

In the following post,

http://hodentek.blogspot.com/2013/08/do-you-need-to-connect-to-sql-anywhere.html

An ODBC connection to SQL Anywhere sample database ‘demo’ was established in OpenOffice 4.0.

Using an ODBC connection presented some problem as described in the above post. Specifically the main problem was you could connect to SQL Anywhere 16’s demo database and see the objects in OpenOffice 4.0, but it was not possible to display the data in the tables. It was assumed that probably the driver was wrong. This assumption may not be correct.

In order to sort out this problem the following test was conducted:

As the connectivity did not throw any exceptions and the demo database objects were visible a table Sept11 with DBA Schema was created (in OpenOffice 4.0) as shown. This assumes that a database in OpenOffice 4.0 has been created using the ODBC connection and that the SQL Anywhere 16’s Personal Server Sample (x64) is running.

It was then populated with some simple data as shown and the database aug31.odb was refreshed.

Sept11 was then opened in Sybase Central to see if the changes are updated. The result is shown here.

Double clicking Sept11 table displays the details as shown.

Clicking Data displays the sample data crated in OpenOffice 4.0 as shown.

Looks like this works from OpenOffice 4.0 to SQL Anywhere 16.
However, going back to OpenOffice 4.0 to reopen Sept11 resulted in an error. The main problem appears to be in OpenOffice 4.0.

Part 2