MS SQL Library

Discussion in 'Additional Libraries' started by citywest, Oct 4, 2008.

  1. citywest

    citywest Member Licensed 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
     

    Attached Files:

    shahroura likes this.
  2. agraham

    agraham Expert Licensed User

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

    citywest Member Licensed 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
     
  4. UnknownArt

    UnknownArt Member Licensed 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: Dec 12, 2008
  5. javcone

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

    citywest Member Licensed 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.
     
  7. soneinsa

    soneinsa Member Licensed 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
     
  8. Erel

    Erel Administrator Staff Member Licensed User

    This library depends on another dll: System.Data.SqlClient.Dll

    I've attached it.
    You will need to copy it to the device together with MSSQL.dll.
     

    Attached Files:

  9. soneinsa

    soneinsa Member Licensed 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.

    Code:
    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
     
  10. Erel

    Erel Administrator Staff Member Licensed User

    Did you try to see the value of cnDatos.LastError?
     
  11. soneinsa

    soneinsa Member Licensed 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

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

    Thank you very very much,

    Rafael Liriano
    SONEINSA
     
  12. Erel

    Erel Administrator Staff Member Licensed User

  13. soneinsa

    soneinsa Member Licensed 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
     
  14. Erel

    Erel Administrator Staff Member Licensed User

  15. aleonv

    aleonv New Member Licensed 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.
     
  16. Erel

    Erel Administrator Staff Member Licensed User

    Try to install the attached cab which is required for WM2003.
     
  17. aleonv

    aleonv New Member Licensed 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
     
  18. aleonv

    aleonv New Member Licensed 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
     
  19. soneinsa

    soneinsa Member Licensed 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
     
  20. aleonv

    aleonv New Member Licensed 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
     
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