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
     cmd.Dispose
    Con.Dispose
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!