MS SQL Library

C

citywest

Member
Licensed User
Longtime User
MSSQLRemote is a basic library which allows you to access a corporate MS SQL Server. Why would you want to do this?, well in my case it's for wireless hand held devices used in a warehouse environment.

:sign0104: Warning This is not currently in production and as it is my first library there are likely bugs and probably there are better ways of doing things. In particular the BeginTransaction and related functions are not fully tested.

Usage as follows:

LastError - Returns the last exception as string

New - Sets up the Connection and returns True or False
Open(ConnString) - Opens a database where ConnString is something like "Persist Security Info=False;Integrated Security=False;Server=CWS-MARK, 1433; initial catalog=ArrowDemo; user id=sa; password=pass;" Returns True or False depending on success.

Close - Closes the current connection

ExecuteNonQuery(QueryString) - Returns True or False. QueryString like "Update Invoices Set StatusFlag = 'X' Where Reference = '1234'"

ExecuteQuery(QueryString) - Returns True or False. QueryString like "Select * From Invoices"

After ExecuteQuery the following are valid:

  • Data - Returns True or False. Step Through result set one row at at a time ie. (Do While Data - i = ReadRow - Loop)
  • Read(FieldName) - Returns single field as string from current row
  • ReadRow - Returns string array of current row

OR

  • BuildResultSet - Will read entire result set and return True or False if data.
  • GetResultSet - Returns entire result set as Array
Fields - Returns number of fields in current result set as integer
Rows - Return number of rows in current result set (only after BuildResultSet)

FieldInfo - Returns Array of field name and field type for current result set

BeginTransaction - Returns True or False
Commit - returns True or False
RollBack - returns True or False
TransactionDispose - returns True or False

Use BuildResultSet and GetResultSet with care as they use Array Redim. Large record sets may/will cause performance problems. Unfortunately CF and also basic4pcc do not appear to support multi dimensional arraylists? If any has any further information or ideas it would be most appreciated.

Requires System.Data.SqlClient.Dll. Tested on SQL Server 2005 should work on 2000 and Express.

Cheers

Mark S
 

Attachments

  • MSSql.zip
    2.9 KB · Views: 424
agraham

agraham

Expert
Licensed User
Longtime User
citywest said:
Unfortunately CF and also basic4pcc do not appear to support multi dimensional arraylists? If any has any further information or ideas it would be most appreciated.
Click to expand...
The .NET Common Language Runtime does not support multi-dimensional arraylists. I've looked at your code and in fact you are doing the same as what an Arraylist does when it runs out of space. An Arraylist is based on an underlying fixed length array and, like you it,allocates a new longer array and copies the existing items into it. Unlike you it usually doubles the array length each time. The underlying .NET array copy mechanism appears to be a native code function call and is probably a memory block copy so your implementation is probably reasonably efficient. You could only get better if you knew how many rows the query returned but that's getting out of my depth as I don't do database work.
 
C

citywest

Member
Licensed User
Longtime User
Thanks for the information agraham.

The sqldatareader is a forward reader only, so you have to read through the entire result set before you can determine the number of rows.

I guess a better way might be to allow the array size to be specified by the caller program.

Regards

Mark S
 
UnknownArt

UnknownArt

Member
Licensed User
Longtime User
Can you provide any basic4ppc sample code?

I am a newbie for basic4ppc. Can you provide any basic4ppc sample code about using MSSQL.DLL?
Thanks,
Mike
 
Last edited:
J

javcone

New Member
Licensed User
Conect to MS SQL server

Alguien tiene algun ejemplo de conectar a un sqlserver 2000 usando System.Data.SqlClient.dll. Gracias a todos
 
C

citywest

Member
Licensed User
Longtime User
Been very busy lately so have only just seen this :sign0013:

A small code snippet that should get you going:

'Select All Records From Table CodMast and in Database ArrowDemo. Display the Fields named Record_Type and Description

msql1.New1
msql1.Open("Persist Security Info=False;Integrated Security=False;Server=CWS-MARK, 1433; initial catalog=ArrowDemo; user id=sa; password=1234;")

msql1.ExecuteQuery("Select * From CodMast Where Record_Type = 'LS' Order By Code")

Do While msql1.Data
Msgbox(msql1.Read("Record_Type") & " " & msql1.Read("Description"))
Loop

msql1.Close

'Update Table CodMast in Database ArrowDemo

msql2.New1
msql2.Open("Persist Security Info=False;Integrated Security=False;Server=CWS-MARK, 1433; initial catalog=ArrowDemo; user id=sa; password=1234;")

msql2.ExecuteNonQuery("Update CodMast Set Description = 'TEST DESCRIPTION' Where Record_Type = 'LS' And Code = 'EF')
msql2.Close

Cheers,

Mark S.
 
S

soneinsa

Member
Licensed User
Longtime User
.NET Compact Framework

Hi,

I download the MSSQL Library and made a litter program to test it. What it does is create a new connection and try to open it, but before the form appear in the Device (HTC Touch, WM6 Professional) an error arise saying that it require a new .Net Compact Framework, it has installed the 2.0 CF, any way I install the 3.5 CF and the error continue.

What is wrong?

Rafael Liriano
SONEINSA
 
S

soneinsa

Member
Licensed User
Longtime User
Can not Open Remote wireless SQL Server

Hi,

Thanks for the answer, now the application run after copy the System.Data.SqlClient.Dll file and the MSSQL.Dll.!!!

Now the my problem is that the following code does not open the connection to the SQL Server, I try SQL Server 2005 and SQL Server 2000, I try to connect wireless from a laptop to XPS server and the connection is Ok, but when try to do it from the Device (HTC Touch, WM6 Professional) to the same XPS Server then cnDatos.open return False.

When I click the button the .Open return False. What I'm doing wrong?, What I'm missing?. Please help me, because connecting to SQL Server is very necessary to my project. If I connect to the PC that has the XPS SQL Server via the file explorer from the Device I see the sharing folder from the PC, I mean that the wireless connection is ok with the PC.

B4X: 
Sub Globals
    'Declare the global variables here.

End Sub

Sub App_Start
    Form1.Show
    cnDatos.New1
End Sub

Sub Button1_Click
    If cnDatos.Open("Persist Security Info=False;Integrated Security=False;Server=XPS,1433; initial catalog=windip; user id=sa; password=12345;") Then
        Msgbox("Conexion exitosa!!!","My First Program",cMsgboxYesNo,cMsgboxHand)
    Else    
        Msgbox("Conexion ERRONEA!!!","My First Program",cMsgboxYesNo,cMsgboxHand)
    End If

End Sub


Thanks in advanced

Rafael Liriano
SONEINSA
 
S

soneinsa

Member
Licensed User
Longtime User
Hi Erel,

Ok, let tell you that the application running at the desktop it connect very well to MS SQL Server 2005, but the same app in the Divice report the following in LastError

System.Data.SqlClient.SqlException: SqlException
en System.Data.SqlClient.SqlConnection.OnError()
en System.Data.SqlClient.SqlInternalConnection.OnError()
en System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
en System.Data.SqlClient.TdsParser.Connect()
en System.Data.SqlClient.SqlInternalConnection.OpenAndLogin()
en System.Data.SqlClient.SqlInternalConnection..ctor()
en System.Data.SqlClient.SqlConnection.Open()
en MSSQL.MSSQLRemote.Open()
en Dbasic.b4p.__main_button1_click()
en Dbasic.EnhancedControls.CEnhancedButton.ClickEvent()
en System.Windows.Forms.Control.OnClick()
en System.Windows.Forms.Button.OnClick()
en System.Windows.Forms.ButtonBase.WnProc()
en System.Windows.Forms.Control._InternalWnProc()
en Microsoft.AGL.Forms.EVL.EnterMainLoop()
en System.Windows.Forms.Application.Run()
en Dbasic.b4p..ctor()
en Dbasic.b4p.Main()
Click to expand...

I hope this error report could help understand what is happend,

Thank you very very much,

Rafael Liriano
SONEINSA
 
Erel

Erel

B4X founder
Staff member
Licensed User
Longtime User
S

soneinsa

Member
Licensed User
Longtime User
Hi Erel,


Thank you very much for the link!!!!. :sign0060:

The problem was very simple to resolve. The connection string to connect is as follow:

cnDatos.Open("Data Source=192.168.1.100,1433;Initial Catalog=windip;User ID=sa;Password=1234;")

the problem is that I have to use the IP address insted the server name.

Thank you, now let's begin to work!!!! :icon_clap:

Rafael Liriano
SONEINSA
 
A

aleonv

New Member
Licensed User
Longtime User
The library MS SQL was use on Windows Mobile 6 or 2003, because i try to connect on WM6 and i don't have problem, all work ok, but on WM2003 send a error from "Can't find PInvoke DLL dbnetlib.dll."
I installed framework 2 and 3.5 but the problem is the same.
Can I work with this library on WM 2003 or this is only for work on WM6?

Regards.
 
A

aleonv

New Member
Licensed User
Longtime User
OK

Thank you very much, i installed and all it's working ok.

The people from Symbol(Motorola) said me, than wm2003 can't connect to PC on my net with the cradle to , but with your help all ok

thanks, best regards
 
A

aleonv

New Member
Licensed User
Longtime User
exec sql query

I have a little problem with executing sql query with this library, on WM6 I don't have problem but on WM2003 the results are different.
For example Select Max(Id) From Table1 Where Id_Floor = AA02.
On WM6 I get 18, that rigth but on WM2003 I get 16 little problem.

Do you know what could be the problem?

I put the msql1.Lasterror but is empty and i install sql.ppc.wce4.armv4.CAB than you send after, but the problem go on

It's possible work without problem on wm2003 or that is impossible?

Thanks.
Best regards
 
S

soneinsa

Member
Licensed User
Longtime User
exec sql query

Hi,

As you are trying different device and maybe different driver version, try to use the command Select Max(Id) From Table1 Where Id_Floor = 'AA02' NOTE the quoted in AA02.

Regards,

Rafael Liriano
SONEINSA
 
A

aleonv

New Member
Licensed User
Longtime User
exec sql query

the command have the quoted in aa02, i just forget it put in the example.

About the driver, what could be? because are two MSSQL.dll and sql.ppc.wce4.armv4.CAB. On WM6 is installed Net CF V2 and i install the same on WM2003

Regards
 
You must log in or register to reply here.
Top