MSSSQL crashes on SQLSelect

I try to access a MSSQL Database from Windows by using the following statement:

Dim rs As RecordSet = WaWi_DB.SQLSelect("SELECT DEB.CRM_AdressenID, CRM.* " + _ "FROM [SL_MARCHE].[dbo].[DEBITOREN] AS DEB, [SL_MARCHE].[dbo].[CRM_ADRESSEN] AS CRM " + _ "WHERE DEB.Nummer='" + Kunde + "' AND DEB.CRM_AdressenID=CRM.ID")

“Kunde” is a string like “12345”

It works fine within an app called “Database explorer” on the same computer. In this “Database explorer” the statement is:

SELECT DEB.CRM_AdressenID, CRM.* FROM [SL_MARCHE].[dbo].[DEBITOREN] AS DEB, [SL_MARCHE].[dbo].[CRM_ADRESSEN] AS CRM WHERE DEB.Nummer='12345' AND DEB.CRM_AdressenID=CRM.ID

If i access only one of both tables in a statement, it seems to work fine. It looks like the MSSQL Plugin is very unstable. :frowning:

Initial thoughts are you should be using JOINS as they are the ANSI standard whereas the method you’ve used is older. Assuming DEB.CRM_AdressenID and CRM.ID are the foriegn key columns I’d rewrite your query

SELECT
DEB.CRM_AdressenID,
CRM.*
FROM
[SL_MARCHE].[dbo].[DEBITOREN] AS DEB
INNER JOIN
[SL_MARCHE].[dbo].[CRM_ADRESSEN] AS CRM ON
DEB.CRM_AdressenID=CRM.ID
WHERE
DEB.Nummer=‘12345’

Not sure if this is the problem with the plugin but worth a go…

Pat

damned syntax indenting - I had it nicely laid out :frowning:

Let’s see if this improves it!

SELECT
     DEB.CRM_AdressenID,
     CRM.*
FROM
     [SL_MARCHE].[dbo].[DEBITOREN] AS DEB
          INNER JOIN
               [SL_MARCHE].[dbo].[CRM_ADRESSEN] AS CRM ON
                    DEB.CRM_AdressenID=CRM.ID
WHERE
     DEB.Nummer='12345'

Thank you Patrick, but it still crashes. It also crashes on the 2nd statement if i split them…

[code] Dim rs As RecordSet = WaWi_DB.SQLSelect(“SELECT CRM_AdressenID FROM [SL_MARCHE].[dbo].[DEBITOREN] WHERE Nummer=’” + Kunde + “’”)

If rs<>Nil And Not rs.EOF Then
  Dim AdressID As String = rs.IdxField(1).StringValue
  
  rs = WaWi_DB.SQLSelect("SELECT * FROM [SL_MARCHE].[dbo].[CRM_ADRESSEN] WHERE ID='" + AdressID + "'")
  If rs<>Nil And Not rs.EOF Then
    Break // Missing code
  End If
End If

[/code]

There are a number of bug reports related to the SQL Server db plugin.

Rather than do the Select *, what happens if you pull only the fields you want? (I vaguely remember an issue like this).

I use the MBS SQL plugin rather than the native plugin. That would be a major bug if SELECT * is the problem.

Though, strictly speaking, it’s more efficient to only pull the columns you want than do a SELECT *.

Ya. For last years Training Day I discovered 3 or 4 major bugs like that. They might be fixed for 2014 R1 but since I don’t use SQL Server I’ve not checked them. SQL Server just isn’t used much in Xojo apps.

Quick feedback check.

Is one of the Select * columns a datatype Text? If so and one row is NULL you may get a crash

<https://xojo.com/issue/16732>

[quote=66593:@Patrick Delaney]I use the MBS SQL plugin rather than the native plugin. That would be a major bug if SELECT * is the problem.

Though, strictly speaking, it’s more efficient to only pull the columns you want than do a SELECT *.[/quote]

replacing the CRM.* with a CRM.Anrede f.e. fixes the issue…

Thank you Patrick! :slight_smile:

I will write a Feedback if there’s no such

It was Bob who spotted the Select * thing not me - but hey, I’ll take the credit :wink:

So, do you have a NULL text field column in [SL_MARCHE].[dbo].[CRM_ADRESSEN] causing the issue (as per 16732) or is it indeed Select * ?

[quote=66604:@Patrick Delaney]It was Bob who spotted the Select * thing not me - but hey, I’ll take the credit :wink:

So, do you have a NULL text field column in [SL_MARCHE].[dbo].[CRM_ADRESSEN] causing the issue (as per 16732) or is it indeed Select * ?[/quote]

It is the Select * Issue: <https://xojo.com/issue/16702>

[quote=66592:@Bob Keeney]There are a number of bug reports related to the SQL Server db plugin.

Rather than do the Select *, what happens if you pull only the fields you want? (I vaguely remember an issue like this).[/quote]

Thank you Bob! :slight_smile:

That’s nasty! As I said, better to explicitly define the columns to be retrieved but that’s naaaaasty!

It is, because we are talking of dozens of columns in my case. Dozens of columns in dozens of tables = hundreds of not allowed to say here :slight_smile:

Have you tried the MBS Plugin?

No, not yet. Is the syntax very different from what i have posted above?
Thank you.

I can send you (off list) a test app I put together for a presentation I gave to the London User Group in 2012! You can reverse engineer that to see how it hangs together.

It connected to SQL Azure (Microsoft Cloud MS SQL). The native plugin, at that stage, didn’t support Azure - don’t know if that’s still the case.

The database itself is offline so the app won’t run but may prove useful.

Just found the DDL for the test app so you can test the app :slight_smile:

Thank you Patrick! :slight_smile:

If you have questions for MBS Plugin, you can email me directly.
With SQLDatabaseMBS the move to our plugin may only need a few lines changes.