SQL library was updated - V1.4

Discussion in 'Official Updates' started by Erel, Jun 14, 2008.

  1. Erel

    Erel Administrator Staff Member Licensed User

    The SQL library now supports parameterized queries.
    Parameterized queries have several advantages over regular queries:
    - Easy to use.
    - No need to escape special characters.
    - Protect against SQL injections.
    - Significantly faster.

    See Command.AddParameter and Command.SetParameter for more information.


    Setup instructions:
    Download and extract the attached file.
    Copy all files to the libraries folder:
    C:\Program Files\Anywhere Software\Basic4ppc Desktop\Libraries

    Make sure to replace existing copies of SQLDesktop.dll and SQLDevice.dll with the new files.
     

    Attached Files:

    • SQL.zip
      File size:
      32.7 KB
      Views:
      613
  2. Filippo

    Filippo Expert Licensed User

    very good :)

    :sign0188:
     
  3. Elrick

    Elrick Member Licensed User

    Erel, it don't work on the device... Following code work on the desktop, but raises many errors on the device.
    Code:
    Sub Globals
       
    'Declare the global variables here.

    End Sub

    Sub App_Start
       Con.New1
       Cmd.New1(
    "",con.Value)
        Con.Open(
    "Data Source = " & AppPath & "\Northwind.sl3")
       Text=
    "CREATE TABLE if not exists table1 (col1,col2)"
       cmd.CommandText=Text
       cmd.ExecuteNonQuery
       
       cmd.AddParameter(
    "value1")
       cmd.AddParameter(
    "value2")
       cmd.CommandText = 
    "INSERT INTO [table1] (col1,col2) VALUES (@value1,@value2)"
       Con.BeginTransaction
       cmd.SetParameter(
    "value2","SomeValue")
       
    For i = 0 To 10
          cmd.SetParameter(
    "value1",i * 2)
          cmd.ExecuteNonQuery
       
    Next
       Con.EndTransaction
       Con.Close
    End Sub
     
  4. Erel

    Erel Administrator Staff Member Licensed User

    It should work properly on the device.
    Make sure you are using the updated library.
    You can add:
    Code:
    Con.New1
    Msgbox(Con.DLLVersion)
    It should show 1.4.
     
  5. Elrick

    Elrick Member Licensed User

    hmm... yes, it shows "1.4" but still don't work... I've attached screenshot with the error. On the desktop, as i said, everything works fine...
     

    Attached Files:

    • 1.jpg
      1.jpg
      File size:
      26.4 KB
      Views:
      87
  6. Erel

    Erel Administrator Staff Member Licensed User

    This error (which is not related to the new version) will only happen if you don't show any form.
    You can fix it by explicitly disposing of the command and connection objects:
    Code:
    Sub App_Start
        Con.New1
        Cmd.New1(
    "",con.Value)
        Con.Open(
    "Data Source = " & AppPath & "\Northwind.sl3")
        Text=
    "CREATE TABLE if not exists table1 (col1,col2)"
        cmd.CommandText=Text
        cmd.ExecuteNonQuery
        
        cmd.AddParameter(
    "value1")
        cmd.AddParameter(
    "value2")
        cmd.CommandText = 
    "INSERT INTO [table1] (col1,col2) VALUES (@value1,@value2)"
        Con.BeginTransaction
        cmd.SetParameter(
    "value2","SomeValue")
        
    For i = 0 To 10
            cmd.SetParameter(
    "value1",i * 2)
            cmd.ExecuteNonQuery
        
    Next
        Con.EndTransaction
        Con.Close
    [B]     cmd.Dispose
        Con.Dispose[/B]
    End Sub
     
  7. Elrick

    Elrick Member Licensed User

    wow... I didn't know about an error "when i don't show any form" :) , i've just started testing the new library. Yes, it works now, thanks Erel, and sorry for bothering...
     
  8. Hennell

    Hennell Member Licensed User

    Is there anyway to use these parameter things with an UPDATE Sql command or do they only help with Inserts?

    Edit: Got it! Turns out I'd got a comma in the wrong place which is why it didn't seem to be working! :sign0161: (Teach me to code when I should be in bed ;) )

    Anyway, for anyone else with such problems:
    Code:
    cmd.CommandText = "Update [Table] SET Col1 = @Value1, Col2 = @Value2 WHERE col3 = [whatever]"
    works fine, just don't forget to watch the commas!
     
    Last edited: Aug 18, 2008
  9. steedj

    steedj Member Licensed User

    I had a project I started with SQL v1.3.
    I updated the SQL files from the zip above in program files but I realized that it wasn't enough for my project.

    I had to:
    - remove all the objects related to SQL (connection, datareader, Command)
    - remove the libraries (desktop and device) form the project
    - assign again the SQL libraries (updated)
    - recreate the objects

    After that the projects was using SQL v1.4
     
  10. steedj

    steedj Member Licensed User

    I just realized that, instead of removing all the objects and recreate them with the new component, it is maybe sufficient to copy directly the dll to the folder where the project is stored and replace the existing ones.
     
  11. Erel

    Erel Administrator Staff Member Licensed User

    Yes. It should be enough.
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice