Support for Sybase DB


#1

Hello DreamFactory,

Thanks for simplifying our development task. :blush:
My concern is to know whether there are any plans to add support for SYBASE DB connection in DSP.
If I’m right, currently it is not possible? Since lot of enterprise applications still uses SYBASE DB, it would be great addition in your Dreamfactory enterprise package. please consider. thanks


#2

Hi PJ. It may be possible already to connect to Sybase databases.

If you are using a linux or mac package, the connections for MS SQL use the FreeTDS driver, which is the driver you would need for Sybase.
So in your service config, you would just select MS SQL as the type of connection but put in all your Sybase connection info.
You may need to modify the file /etc/freetds/freetds.conf to reflect the correct version #. It is probably set to tds version = 8.0 for use with Microsoft. I would suggest trying version 5.0 for Sybase.
Please note that changing this setting will prevent you from using the driver to connect to MS SQL databases.


Connecting DreamFactory to ODBC database sources, particularly FileMaker
#3

Thanks, I will try it out. Though I was working on windows server but if your solution works i would be willing to switch on linux.
Just let me know in case there is a workaround for windows package too. ty:)


#4

@drewpearce I tried your solution on linux but without 100% success. I’m able to connect to sybase through tsql. So I’m guessing there is some kind of query format mismatch that DF uses, since we select Sql server type to connect to sybase. This is the error from the log. Can you help me out here. thanks

CDbCommand::fetchAll() failed: SQLSTATE[HY000]: General error: 208 General SQL Server error: Check messages from the SQL Server [208] (severity 16) [(null)]. The SQL statement executed was: SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN
(‘INFORMATION_SCHEMA’, ‘sys’, ‘db_owner’, ‘db_accessadmin’, ‘db_securityadmin’,
‘db_ddladmin’, ‘db_backupoperator’, ‘db_datareader’, ‘db_datawriter’,
‘db_denydatareader’, ‘db_denydatawriter’).


#5

PJ, which call were you making that generated this error? Do you know what the correct Sybase SQL call would be?


#6

I got the error while trying getTables() in live api, though none of the other calls are working either.
Sybase query to get list of user tables would be Select name from sysobjects where type="U" or there is also an inbuilt stored procedure "sp_tables"
I think the error means DSP failing while trying to get list of all user defined schemas which could be the first step of any DSP call.
Any chance of finding where its getting executed so we can try alternate query to see if this is the issue?


#7

I don’t believe this will work then.
We don’t officially support Sybase, but I had thought it MIGHT work due to the same driver being used for it and MS SQL. It seems the syntax of calls between the two systems are too disparate for this to work.


#8

It should still be possible, just not with the existing SQL model and Swagger implementation. The driver probably supports the connection, but needs to be implemented in the PHP source code differently than the current SQL DB service implementation.

If you’re committed to contributing Sybase integration to the project, @Im_PJ, you will likely need to modify and/or add to SqlDbSvc.php and SqlDbSvc.swagger.php to add the calls that Sybase is expecting and handle the response that Sybase provides.


#9

Thanks for clarifying that Jeffrey.

I should have said that it appears that this won’t work “out of the box.”

@Im_PJ, you are of course welcome to edit the underlying system, and we welcome your contributions.


#10

Thanks @drewpearce & @jeffreystables for pointing me in the right direction. I was able to make getTables method work in live api (but it took me a while to find where the db call is being made in df-sqldb-core). Other API methods are still not working which means I need to edit their queries as well. This doesn’t seem good to me editing the underlying system directly as I couldn’t figure out a way to make changes which will allow DF to work with sybase and sql server both. My current job role doesn’t doesn’t allow me to explore this further. Sybase is not popular DB but its still used in enterprise legacy systems. So hoping somebody would add this integration or better DFv2.0 comes with ODBC support but its still a long wait for production env. :pensive:


#11

Hello All,

After a long wait, I could try DFv2.0 finally. thanks.
I see there is an option to create service for mssql/sybase which means DF now supports sybase connectivity out of the box. But I got following error "Failed to update resource: Driver 'mssql' is not supported by this software."
I’m on linux machine and able to connect to sybase server through tsql utility. Which driver I’m supposed to install? Pls help as there is lack of documentation for DF2 at the moment.


#12

The driver is sqlsrv, not mssql. This is a confirmed bug that’s already fixed in the dev branch of 2.0.


2012 SQL Server Express troubles
#13

@jeffreystables corect me if im wrong but sqlsrv is used to connect to sql server on windows pc. I am trying to connect to sybase. Which driver tag i shud use?


#14

I’m not aware of a driver that we’ve implemented called mssql. That’s all I was pointing out: that in a Windows context the DF base should be looking for a driver called sqlsrv and not one called mssql, which I’m not aware of us having implemented anywhere.


#15

@jeffreystables Agreed. and I could connect to SQL server with your suggestion. But my requirement is to connect with Sybase server. I can see driver select option called “MS SQL Server/Sybase” under create service which im sure means DF2 now supports sybase natively. Can you help me out how to make it work. There is lack of documentation at the moment and so I’m stuck.


#16

@Im_PJ this is actually a 2.0 bug that should be resolved soon. All you have to do is change mssql to dblib in the connection string.
I do not know if this will work for connecting to sybase though. You may have to edit the freetds config.


#17

Yes using dblib I can connect to SQL server but not sybase. :pensive:
I see two driver options “MS SQL Server” & "MS SQL Server/Sybase"
What’s the difference between them? Seeing Sybase in driver option I thought it would just work but it doesn’t. I have tried editing freetds config changing tds version to 5.0 both on linux and windows, but not able to make it work.
Can your team provide live support or webex? We can give teamviewer access,if needed. Our app launch is stuck at this point. We are also looking forward to get DF enterprise but can’t wait for its release time.


#18

Hi @Im_PJ, we can certainly provide those with active support plans support by screenshare and phone. Please contact sales@dreamfactory.com for information on the support plans we have available.

However since Sybase isn’t explicitly supported yet, and you’re testing the beta version of our new release, and you’re adding a new feature to the open source codebase yourself, I’m not sure support will be your best resource. We’re more geared toward break/fix issues and supporting the official existing features of the product.


#19

Sybase isn’t supported yet? I thought the driver option “MS SQL Server/Sybase” means DF supports sybase now. Still didn’t get a satisfactory answer as to what this option does specifically.
Also referring to this post, I thought there is some sort of DB enhancement being done in DF2. Could you pls clarify this with your DB team and let me know. Am I trying in vain? :confused:


#20

@Im_PJ, the difference between the driver options “MS SQL Server” & “MS SQL Server/Sybase” is that one is for Windows hosts and one is Linux/Mac hosts.
These might be better labeled as MS SQL - sqlsrv & MS SQL - dblib.
Still waiting to hear back if Sybase is supported “out of the box” (I suspect not.)
The ODBC driver integration you referenced has not to my knowledge been implemented yet.