Error when accessing MS SQL Views

Connecting to SQL
1

Hi,

I’d like to thank you all for the great work you’ve done in developing an excellent open-source product.

I’ve set up a few services in dreamfactory that access an MS SQL Server database. Everything was working well, but I realized I needed to performs joins in my queries. It appears that the best way to do this is create views (please correct me if there is currently a better way to perform joins).

I’ve created a couple of views and tried to access them from the API and I’ve receiving the following error:

Failed to retrieve records from ‘usercontact’.\nCDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 102 General SQL Server error: Check messages from the SQL Server [102] (severity 15) [(null)]

I then tried to access the views from the Data tab in the Admin. After selecting the service, the views appears in the drop down with the tables in the database. After selecting the view and clicking the “Set Service” button, I see the following errors pop-up:

API Error
Failed to retrieve records from ‘usercontact’.
CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 102 General SQL Server error: Check messages from the SQL Server [102] (severity 15) [(null)]

and

DreamFactory Table Module
DreamFactory Table Module: Failed to retrieve records from ‘usercontact’.
CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 102 General SQL Server error: Check messages from the SQL Server [102] (severity 15) [(null)]

I’m able to perform select statements on these views when I log into SSMS under the same user account, so I do not believe this to be a permissions issue.

Please advise.

Thanks,

Sam

2

We are testing calling views from the DSP for MySQL and SQL Server DBs now and will report back with details.

What is logged in your DSP’s log files when these errors are thrown? And what version of SQL Server are you connecting to?

3

I’ve encountered the same error.

   {
  "error": [
    {
      "context": null,
      "message": "Failed to retrieve records from 'Sicht_Buchungsliste'.\nCDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 1038 General SQL Server error: Check messages from the SQL Server [1038] (severity 15) [(null)]",
      "code": 500
    }
  ]
}

I’m using the hosted version of Dreamfactory and MSSQL Version:

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64)   Jun 11 2012 16:41:53   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
4

Could you let me know the name of your sandbox DSP and the contents of the SQL view you’re calling?

5

The DSP name is

stundenbuchungsapp

The view I’m trying to load is Sicht_Buchungsliste and I’d have uploaded an image of what’s in it, but I’m a new user and can’t upload pics. :\

6

I think I’ve found my problem.
As soon as the results have one one field with an Umlaut in the value, the whole request seems to fail.

7

It may indeed be a formatting or encoding problem due to the special character. However I’m unable to find any sandbox DSP at dsp-stundenbuchungsapp.cloud.dreamfactory.com or stundenbuchungsapp.cloud.dreamfactory.com so I can’t say what is being logged. Are you certain this is the name of your free hosted DSP?

8

Oh sorry, it’s dsp-ks.cloud.dreamfactory.com.

Stundenbuchungsapp is the app inside the dsp.

9

I’m glad you’ve identified the issue. Please let me know if you require any further assistance.

10

Does that mean it will not be possible to use Umlauts with your service? Since our business is located in Switzerland, that’s pretty much a necessary requirement for us.

11

Sorry, I thought you said you had identified the issue.

We have tested storing and retrieving data with the umlaut character in SQL Server and not encountered an error.

First, I recommend trying to pull the same data through same view in another SQL Server client. See if the same error is given.

Next, try storing and then retrieving some data with umlauts, both through DSP. Create the records through DSP and then retrieve them, to see if the error is prevented if the DSP is the one that creates the records.

If neither of these gives any insight, could you provide a dump of the records returned from the view so we can see it and test on our own? (Or at least a sample of the data that causes this error.) Additionally, what are the collation and charset settings on the data in question?