MSSSQL crashes on SQLSelect

  1. 3 years ago

    Sascha S

    20 Feb 2014 Pre-Release Testers, Xojo Pro Germany/W'haven

    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. :-(

    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).

  2. Patrick D

    20 Feb 2014 Pre-Release Testers, Xojo Pro ');DROP TABLE tbl_Person;--

    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

  3. Patrick D

    20 Feb 2014 Pre-Release Testers, Xojo Pro ');DROP TABLE tbl_Person;--

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

    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'
  4. Sascha S

    20 Feb 2014 Pre-Release Testers, Xojo Pro Germany/W'haven

    Thank you Patrick, but it still crashes. It also crashes on the 2nd statement if i split them...
    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

  5. Bob K

    20 Feb 2014 Pre-Release Testers, Xojo Pro Answer Kansas City

    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).

  6. Patrick D

    20 Feb 2014 Pre-Release Testers, Xojo Pro ');DROP TABLE tbl_Person;--

    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 *.

  7. Bob K

    20 Feb 2014 Pre-Release Testers, Xojo Pro Kansas City

    @Patrick D That would be a major bug

    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.

  8. Patrick D

    20 Feb 2014 Pre-Release Testers, Xojo Pro ');DROP TABLE tbl_Person;--

    Quick feedback check.

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

    Feedback Case #16732

  9. Sascha S

    20 Feb 2014 Pre-Release Testers, Xojo Pro Germany/W'haven

    @Patrick D 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 *.

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

    Thank you Patrick! :-)

    I will write a Feedback if there's no such

  10. Patrick D

    20 Feb 2014 Pre-Release Testers, Xojo Pro ');DROP TABLE tbl_Person;--

    It was Bob who spotted the Select * thing not me - but hey, I'll take the credit ;)

    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 * ?

  11. Sascha S

    20 Feb 2014 Pre-Release Testers, Xojo Pro Germany/W'haven

    @Patrick D It was Bob who spotted the Select * thing not me - but hey, I'll take the credit ;)

    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 * ?

    It is the Select * Issue: Feedback Case #16702

  12. Sascha S

    20 Feb 2014 Pre-Release Testers, Xojo Pro Germany/W'haven

    @Bob K 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).

    Thank you Bob! :-)

  13. Patrick D

    20 Feb 2014 Pre-Release Testers, Xojo Pro ');DROP TABLE tbl_Person;--

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

  14. Sascha S

    20 Feb 2014 Pre-Release Testers, Xojo Pro Germany/W'haven

    @Patrick D 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* :-)

  15. Patrick D

    20 Feb 2014 Pre-Release Testers, Xojo Pro ');DROP TABLE tbl_Person;--

    Have you tried the MBS Plugin?

  16. Sascha S

    20 Feb 2014 Pre-Release Testers, Xojo Pro Germany/W'haven

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

  17. Patrick D

    20 Feb 2014 Pre-Release Testers, Xojo Pro ');DROP TABLE tbl_Person;--

    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.

  18. Patrick D

    20 Feb 2014 Pre-Release Testers, Xojo Pro ');DROP TABLE tbl_Person;--

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

  19. Sascha S

    20 Feb 2014 Pre-Release Testers, Xojo Pro Germany/W'haven

    Thank you Patrick! :)

  20. Christian S

    20 Feb 2014 Pre-Release Testers, Xojo Pro Europe (Germany)

    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.

  21. Newer ›

or Sign Up to reply!