MSSSQL crashes on SQLSelect

  1. ‹ Older
  2. 3 years ago

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

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

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

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

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

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

  8. 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! :-)

  9. 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!

  10. 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* :-)

  11. Patrick D

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

    Have you tried the MBS Plugin?

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

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

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

  15. Sascha S

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

    Thank you Patrick! :)

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

  17. Patrick D

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

    Good point Christian

    Sascha - the code I've sent you uses SQLConnectionMBS class which, correct me if I'm wrong Christian,

    • Offers more flexibility than SQLDatabaseMBS
    • Means you don't need a Xojo Database license
  18. Sascha S

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

    @ChristianSchmitz 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.

    Thank you Christian and Patrick for your kind help.
    Patricks code example and Christians Plugins working flawlessly!

    I am used to use RecordSets and the AsRecordSet functionality of Christians Plugin is a perfect solution.

    THANKY OU BOTH SO MUCH :)

      WaWi_DB_MBS = New SQLDatabaseMBS // Needed for my RecordSet
      dim con as new SQLConnectionMBS
      dim com as new SQLCommandMBS
      
      try
        con.Option("OLEDBProvider") = "SQLNCLI10" // connect to database
        con.Connect("192.168.98.31@SL_M", myLogin, myPassword, SQLConnectionMBS.kSQLServerClient)
        
        // Set the details of the command to execute(and run it!)
        com.Connection = con // Connection to use
        com.setCommandText ("SELECT DEB.CRM_AdressenID, CRM.* FROM dbo.DEBITOREN AS DEB INNER JOIN dbo.CRM_ADRESSEN AS CRM ON DEB.CRM_AdressenID=CRM.ID WHERE DEB.Nummer='" + Kunde + "'", 0)
        
        com.Execute // Run the command!
        
        Dim rs As RecordSet = com.AsRecordSet
        If rs <> Nil And Not rs.EOF Then
          // YES !!!!
          // FINALLY !!!
          // Enough now Sascha... go on with your work! ;-)
        End If
        
        con.Disconnect // Close The Connection
        
      catch r as SQLErrorExceptionMBS
        //Error Handling
        MsgBox r.message
      end try
  19. Patrick D

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

    Glad to help :)

  20. Christian S

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

    well, you could of course use the SQLDatabaseMBS class which is subclass of Database class. This way you have less code to change.

  21. 3 days ago

    Marco

    Apr 20 Micropedia s.r.l. Naples Italy

    @ChristianSchmitz 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.

    Hi Christian i've sent you a mail in the morning about SQLSelectAsRecordSet

    Thanks

or Sign Up to reply!