PB.Ex MSSQL (Windows)

RSBasic · **Posted:** Fri Mar 09, 2018 10:35 pm

Hello,

with this library it is possible to access MSSQL databases and execute SQL queries (e. g. Select, Insert Into, Update, Drop Table,...) without ODBC.

Functions:

ExecuteSQLQuery()
- Syntax:
  Code:
  Result = ExecuteSQLQuery(ConnectionString$, SQL$, Timeout, @Output, @ErrorOutput)
- Description: Executes an SQL query.
- Parameter:
  1. ConnectionString$: Connection data for the server and the database.
  2. SQL$: SQL code to execute a query.
  3. Timeout: Time in seconds, how long the process should be waited for. If 0 is specified, it waits until the operation is complete.
  4. @Output: The result of the query is stored in XML format in the string variable.
  5. @ErrorOutput: If an error occurs, the error message is stored in the string variable.
- Return value:
  - 0: The process was successful.
- Example:
  Code:
  EnableExplicit
  
  Global PBEx_MSSQL
  
  CompilerIf #PB_Compiler_Processor = #PB_Processor_x86
  PBEx_MSSQL = OpenLibrary(#PB_Any, "PB.Ex_MSSQL_x86.dll")
  CompilerElseIf #PB_Compiler_Processor = #PB_Processor_x64
  PBEx_MSSQL = OpenLibrary(#PB_Any, "PB.Ex_MSSQL_x64.dll")
  CompilerEndIf
  
  If PBEx_MSSQL
  Prototype ExecuteSQLQuery(ConnectionString.p-Unicode, SQL.p-Unicode, Timeout, Output, ErrorOutput)
  Global ExecuteSQLQuery.ExecuteSQLQuery = GetFunction(PBEx_MSSQL, "ExecuteSQLQuery")
  
  Define Output$ = Space(1000000)
  Define ErrorOutput$ = Space(1024)
  
  If ExecuteSQLQuery("Server=YourServer;Database=YourDatabase;User Id=YourUsername;Password=YourPassword;", "SELECT * FROM MyTable", 0, @Output$, @ErrorOutput$) = 0
  ParseXML(1, Output$)
  FormatXML(1, #PB_XML_WindowsNewline | #PB_XML_ReFormat | #PB_XML_ReIndent)
  Debug ComposeXML(1)
  Else
  Debug "Error: " + ErrorOutput$
  EndIf
  
  CloseLibrary(PBEx_MSSQL)
  EndIf

System requirements:

Windows XP or higher
.NET Framework 3.5 or higher
Unicode activation (standard from PB 5.50)

Since the required. NET Framework is preinstalled by default from Windows 7 and can be installed on XP and Vista, this shouldn't be a problem to use this version.
No assembly registration with regasm.exe with administrator rights is necessary as with COMatePLUS.

Licence: This DLL file is free of charge and may be used both privately and commercially.
The following copyright texts must be provided:

Quote:

Download: http://www.rsbasic.de/downloads/downloa ... _MSSQL.zip

I would be happy to receive feedback, suggestions for improvement, bug reports or requests. If you want to support me, you can also donate me a little something. Thank you

HanPBF · **Joined:** Fri Feb 19, 2010 3:42 am **Posts:** 544

Hello RSBasic!

Thanks a lot for the great DLL and the example!

I had the problem that one PC has drivers (as shown in ODBC)
"SQL Server"
"SQL Server Native Client 11.0"

Access to MSSQL worked.

The other PC I tested the software, only has
"SQL Server"
There, the access to MSSQL did not work.

Does Your code need "SQL Server Native Client 11.0"?

Will give it a try and see what XML is given back.

Thanks a lot!

RSBasic · **Posted:** Sat Mar 10, 2018 6:22 pm

I have installed SQL Server 2008 in a new VM and after creating a database with SQLCMD I can create a new table, insert a data record and read it out.
But I found a bug (output is always empty) in the x86 version. Update coming later.

RSBasic · **Posted:** Sat Mar 10, 2018 6:47 pm

Addendum: It's not a bug. I forgot to activate Unicode during testing. The output is working.

RSBasic · **Posted:** Tue Mar 13, 2018 7:55 pm

PB.Ex MSSQL 1.0.1.0 was published.

Changelog:

Bugfix: XML-Code

dige · **Posted:** Fri Sep 04, 2020 9:29 am

I just wanted to try the Dll with MSSQL 10.50, because suddenly the access via ODBC is not possible anymore because of SSL errors.
But unfortunately it does not work with the Dll either. The debugger just crashes on ExecuteSQLQuery() without error message.
Tested with PB 5.72 x86 and x64 Window 10 / .NET 4.8

PureBasic Forum

PB.Ex MSSQL (Windows)

Who is online