SQL library was updated - V1.4

[Erel]

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.

[Filippo]

very good

[Elrick]

Erel, it don't work on the device... Following code work on the desktop, but raises many errors on the device.

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

[Erel]

It should work properly on the device.
Make sure you are using the updated library.
You can add:

Con.New1
Msgbox(Con.DLLVersion)

It should show 1.4.

[Elrick]

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

[Erel]

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:

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

[Elrick]

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

[Hennell]

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! (Teach me to code when I should be in bed )

Anyway, for anyone else with such problems:

cmd.CommandText = "Update [Table] SET Col1 = @Value1, Col2 = @Value2 WHERE col3 = [whatever]"

works fine, just don't forget to watch the commas!

[steedj]

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

[steedj]

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.