MSSQL SQL Server library

[agraham]

This library, a tidied up version of http://www.b4x.com/forum/additional-libraries/3082-ms-sql-library.html allows access to SQL Server databases.

A rudimentary help file is included that describes the available methods and properties but it is expected that you already know what do do in order to connect to a SQL Server instance because I don't! However you do need System.Data.SqlClient.Dll from post #8 of this thread on your device.

EDIT :- Version 1.7 posted. See post #8 for details.

EDIT :- Version 1.8 posted. See post #10 for details.

EDIT :- Version 1.9 posted. See post #13 for details.

EDIT :- Version 2.0 posted. See post #32 for details.

 

[georgea]

Great job Andrew! and nice documentation.

Thank you.

 

[georgea]

Hi, i am back just to ask something.

Is there any chance to add a property (String or Number) e.g. "RowCount", to know the rows that the query returned before reading the data ?

I don't know if function ReadRemainingRows is for this use, but i can't work with it.

Thanks.

 

[agraham]

Is there any chance to add a property (String or Number) e.g. "RowCount", to know the rows that the query returned before reading the data ?

I looked to do that but there doesn't seem to be any way of finding that out without reading to the end of the table. I think it is the same with SQLite.

I don't know if function ReadRemainingRows is for this use, but i can't work with it.

It's a convenience to read the entire table at once and the length of the returned array will tell you how many rows. Why can't you use it?

 

[georgea]

Sometimes you may need to know how many rows the query returns, before you read the query. So, if you read all the data till the end of the table just to count the rows then if you re-read the Mssql.Advance it returns False and you can't read it.

As for the ReadRemainingRows, i tryied to test it but it always returns ArrayLen = 1 and when i try to read it, it seems to be empty or null.

One (dump) solution is before the main query you plan to execute, to execute a "Select Count({field}) As Rowcount From {table}" and then ReadField("Rowcount") to know how many rows you 'll get. I know that you have to execute 2 queries everytime but i see it as the only way.

 

[agraham]

As for the ReadRemainingRows, i tryied to test it but it always returns ArrayLen = 1 and when i try to read it, it seems to be empty or null.

There's probably a bug in it then . What does LastError return after you have executed ReadRemainingRows? The array should also contain the error string at index (0,0).

 

[georgea]

The following is the test code which returns me normally 8 rows:

mssql1.ExecuteQuery("Select nickname From Employees")
array2() = Mssql1.ReadRemainingRows
Msgbox(ArrayLen(array2()))
Msgbox(array2(0,0))

The first MsgBox returns 1 and the second MsgBox i get System.IndexOutOfRangeException: Index Out Of Bounds.MSSQL.MSSQL.ReadRemainingRows()

Also, after ReadRemaingRows the Mssql.Advance returns always False and i can't use ReadField().

 

[agraham]

Version 1.7 now posted which (hopefully) fixes the the bug in ReadRemainingRows.

 

[georgea]

Goodmorning.

I tested Version 1.7 and i noticed that RemainingRows now return as ArrayLen the number of rows retrieved + 1. However the main issue is that after executing RemainingRows, the Advance method returns false and you can't read the data. Also, Array(0,0) returns the same error but this is not an issue.

Thank you.

 

[agraham]

RemainingRows now return as ArrayLen the number of rows retrieved + 1.

There was another bug that I missed in ReadRemainingRows which was using a wrong property to size the array. Corrected in version 1.8.

However the main issue is that after executing ReadRemainingRows, the Advance method returns false and you can't read the data.

That is correct behaviour. It is the same as you doing multiple ReadRows. The data is in the two dimensional array that is returned by ReadRemainingRows. You can use ReadRemainingRows immediately after ExecuteQuery to get the whole table or use Advance and ReadRow to get the data one row at a time. Using ReadRemainingRows after using Advance and ReadRow one or more times will return only the data in the unread part of the table. It seems to be common to SQL databases that tables returned from queries can only be read once in a forward direction.

Also, Array(0,0) returns the same error but this is not an issue.

I don't understand this unless you are trying to execute ReadRemainingRows again in which case it is expected behaviour as the table has been read to the end.

 

[georgea]

Now ArrayLen of the array that ReadRemainingRows returns is correct and also it is full of data. My problem now is how the data are stored into this array and how can i manipulate them, but this has nothing to do with the library.

Thank you Andrew!

 

[georgea]

I try to read the array that ReadRemaingRows return with this code:

fields() = Mssql.FieldInfo
data() = Mssql.ReadRemainingRows
For i = 0 To ArrayLen(data()) - 1
  For j = 0  To ArrayLen(fields()) - 1
    Msgbox(data(i,j))
  Next
Next


ArrayLen(fields()) returns 7
ArrayLen(data()) returns 17

The result is that i always get the data of the last row of the table. Is this my mistake?

 

[agraham]

Version 1.9 posted should now work properly.

I am afraid that yet another bug in ReadRemainingData involving array references returned the last row of data for all the rows in the table. It's a bit difficult writing code that you can't test yourself It should now work OK.

 

[georgea]

Now it works, i must say, perfectly!

I can realize how hard is to create something without having the ability to test and debug it yourself and waiting for another person to tell you what goes wrong. Sometimes i got a little annoying i think, but i hope i gave you some clues with my feedbacks. I wish i knew how to create libraries by myself and not disturbing you, but it's only a week i use Basic4PPC.

I think this library will help many people and the support of MSSQL will help Basic4PPC to be even more complete product.

Thank you, for one more time, Andrew!

 

[Moroz]

On my PDA can not run the program with the library.
Error: Can not find filename or assembly "System.Data.SqlClient, versioned = 3.0.3600.0, Culture = neutral, PublicKeyToken =" or one of its dependencies.
Tell me what to do.

 

[agraham]

You only need to post once about a problem. I have replied to your other post.

 

[Moroz]

You only need to post once about a problem. I have replied to your other post.

I use version 1.9 and have wthis problem.
System.Data.SqlClient.dll copy to MSSQL.dll

Windows Mobile 5 - my operation system, maybe this problem?

 

[Moroz]

Thank you for your reply.
I solved the problem differently.
Need is superfluous to install SQL Client 2.0 does not need to copy your DLL.
I attach a file to install.

 

[sbruno]

MS SQL on Windows CE.NET 5.0 Device

First, as a :sign0104: I am only lost for the moment... I hope...

Reading through all of the threads can get anyone confused. I hope, at some point, the treads can be cleaned up to remove the older dated information so we can find the correct solution. If one exists...

I am looking for the answer to Moroz's post on 9-30-2010: where he stated...

"On my PDA can not run the program with the library.
Error: Can not find filename or assembly "System.Data.SqlClient, versioned = 3.0.3600.0, Culture = neutral, PublicKeyToken =" or one of its dependencies.
Tell me what to do."

When using MSSQL.DLL ver 1.1 and adding the component to Basic4PPC I can compile and run my program on the desktop, but not on the Windows CE device. I tried to use the newer versions of the MSSQL.DLL up to 1.9 as listed in other posts but have other problems with either linking or adding the dlls. I am not sure at this point, which dll's should be added to the projects or how this is done with the current release.

Any information would greatly reduce the over confused path I have taken.

Thanks in advance for your assistance! :sign0085:

See post for updated information: http://www.b4x.com/forum/questions-...ibrary-component-configuration.html#post40976

 

[jnltw]

Been reading about MSSQL a bit since this afternoon - I got all of my MSSQL apps running fine on my desktop but after buying Basic4PPC none of them is willing to run on my WM6.5Pro smartphone.

Didn't find this problem here yet, my smartphone tells me:

"This application requires a newer version of the Microsoft(R) .NET Compact Framework than the version installed on this device."

Next message:

".NET CF Initialization Error
The application failed to load required components. If the .NET Compact Framework is installed on a storage card, please ensure that this card is in place and launch the application again. If this fails, a re-installation of the .NET Compact Framework is recommended. Support info: -2147483643 (80000005)"

Any solutions?

Update: Updated to newest version of the DLL (Found I didn't have the last one), now I'm still getting a message but a different one:

"Could not load type 'MSSQL.MSSQLRemote' from assembly 'MSSQL, Version=1.0.3857.19677, Culture=neutral, PublicKeyToken=null"
I don't understand what's wrong?