PB.Ex MSSQL (Windows)

Applications, Games, Tools, User libs and useful stuff coded in PureBasic
User avatar
RSBasic
Moderator
Moderator
Posts: 1218
Joined: Thu Dec 31, 2009 11:05 pm
Location: Gernsbach (Germany)
Contact:

PB.Ex MSSQL (Windows)

Post by RSBasic »

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: Select all

      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:
      • 1: The process was successful.
    • Example:

      Code: Select all

      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")
        Prototype GetDatabaseBlobEx(ConnectionString.p-Unicode, Table.p-Unicode, Column.p-Unicode, Where.p-Unicode, Memory, MemoryLength, ErrorOutput)
        Global GetDatabaseBlobEx.GetDatabaseBlobEx = GetFunction(PBEx_MSSQL, "GetDatabaseBlobEx")
        Prototype SetDatabaseBlobEx(ConnectionString.p-Unicode, Table.p-Unicode, Column.p-Unicode, Where.p-Unicode, Memory, MemoryLength, ErrorOutput)
        Global SetDatabaseBlobEx.SetDatabaseBlobEx = GetFunction(PBEx_MSSQL, "SetDatabaseBlobEx")
        
        Define Output$ = Space(1000000)
        Define ErrorOutput$ = Space(1024)
        
        ;-Please change this
        Define Server$ = "YourServer"
        Define Database$ = "YourDatabase"
        Define User$ = "YourUsername"
        Define Password$ = "YourPassword"
        
        ;====================================================================================================
        
        ;-Get records from table
        If ExecuteSQLQuery("Server=" + Server$ + ";Database=" + Database$ + ";User Id=" + User$ + ";Password=" + Password$ + ";", "SELECT * FROM MyTable", 0, @Output$, @ErrorOutput$)
          ParseXML(1, Output$)
          FormatXML(1, #PB_XML_WindowsNewline | #PB_XML_ReFormat | #PB_XML_ReIndent)
          Debug ComposeXML(1)
        Else
          Debug "Error: " + ErrorOutput$
        EndIf
        
        ;====================================================================================================
        
        ;-Create new record in table
      ;   If ExecuteSQLQuery("Server=" + Server$ + ";Database=" + Database$ + ";User Id=" + User$ + ";Password=" + Password$ + ";", "INSERT INTO MyTable (Prename, Lastname, Age, IsEnabled) VALUES ('Max', 'Mustermann', 18, 1) ", 0, @Output$, @ErrorOutput$)
      ;     Debug "Successful"
      ;   Else
      ;     Debug "Error: " + ErrorOutput$
      ;   EndIf
        
        ;====================================================================================================
        
        ;-Import file to database
        ;1. Load the file into memory.
      ;   Define length
      ;   Define *MemoryID
      ;   Define bytes
      ;   If ReadFile(1, "YourImage.png")
      ;     length = Lof(1)
      ;     *MemoryID = AllocateMemory(length)
      ;     If *MemoryID
      ;       bytes = ReadData(1, *MemoryID, length) 
      ;     EndIf
      ;     CloseFile(1)
      ;   EndIf
      ;   
      ;   ;2. Send the memory to database.
      ;   If SetDatabaseBlobEx("Server=" + Server$ + ";Database=" + Database$ + ";User Id=" + User$ + ";Password=" + Password$ + ";", "MyTable", "BinText", "ID = 2", *MemoryID, bytes, @ErrorOutput$)
      ;     Debug "Successful"
      ;   Else
      ;     Debug "Error: " + ErrorOutput$
      ;   EndIf
        
        ;====================================================================================================
        
        ;-Export file from database
      ;   ;1. Determine the size of the file to be created.
      ;   Define FileLength
      ;   FileLength = GetDatabaseBlobEx("Server=" + Server$ + ";Database=" + Database$ + ";User Id=" + User$ + ";Password=" + Password$ + ";", "MyTable", "BinText", "ID = 2", 0, 0, @ErrorOutput$)
      ;   If FileLength = 0
      ;     Debug "Error: " + ErrorOutput$
      ;   EndIf
      ;   
      ;   ;2. Import and save file.
      ;   Define *MemoryID2 = AllocateMemory(FileLength)
      ;   If GetDatabaseBlobEx("Server=" + Server$ + ";Database=" + Database$ + ";User Id=" + User$ + ";Password=" + Password$ + ";", "MyTable", "BinText", "ID = 2", *MemoryID2, FileLength, @ErrorOutput$)
      ;     If CreateFile(1, "YourImageFromDB.png")
      ;       WriteData(1, *MemoryID2, FileLength)
      ;       CloseFile(1)
      ;     EndIf
      ;     Debug "Successful"
      ;   Else
      ;     Debug "Error: " + ErrorOutput$
      ;   EndIf
        
        
        
        CloseLibrary(PBEx_MSSQL)
      EndIf
      
    • GetDatabaseBlobEx()
      • Syntax:

        Code: Select all

        Result = GetDatabaseBlobEx(ConnectionString$, Table$, Column$, Where$, Memory, MemoryLength, @ErrorOutput)
      • Description: Exports a file from a table column of a record entry.
      • Parameter:
        1. ConnectionString$: Connection data for the server and for the database.
        2. Table$: Name of the table.
        3. Column$: Column name.
        4. Where$: Condition to find a unique record entry.
        5. Memory: Destination memory for the exporting file. If 0, then the length of the file is returned.
        6. MemoryLength: Length of the target memory. The length must be determined first.
        7. @ErrorOutput: If an error occurs, the error message is saved to the string variable.
      • Return value:
        • 1: The process was successful. Or the length of the file.
      • SetDatabaseBlobEx()
        • Syntax:

          Code: Select all

          Result = SetDatabaseBlobEx(ConnectionString$, Table$, Column$, Where$, Memory, MemoryLength, @ErrorOutput)
        • Description: Imports a file into a table column of a record entry.
        • Parameter:
          1. ConnectionString$: Connection data for the server and for the database.
          2. Table$: Name of the table.
          3. Column$: Column name.
          4. Where$: Condition to find a unique record entry.
          5. Memory: Memory of a loaded file.
          6. MemoryLength: Length of memory.
          7. @ErrorOutput: If an error occurs, the error message is saved to the string variable.
        • Return value:
          • 1: The process was successful.
      System requirements:
      • .NET Framework 4.7.2 or higher
      • Unicode activation (standard from PB 5.50)
      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:
      Copyright © 2019 RSBasic.de
      Download: https://www.rsbasic.de/downloads/downlo ... _MSSQL.zip
      Image

      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 :)
Image
Image
HanPBF
Enthusiast
Enthusiast
Posts: 562
Joined: Fri Feb 19, 2010 3:42 am

Re: PB.Ex MSSQL (Windows)

Post by HanPBF »

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!
User avatar
RSBasic
Moderator
Moderator
Posts: 1218
Joined: Thu Dec 31, 2009 11:05 pm
Location: Gernsbach (Germany)
Contact:

Re: PB.Ex MSSQL (Windows)

Post by RSBasic »

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.
Image
Image
User avatar
RSBasic
Moderator
Moderator
Posts: 1218
Joined: Thu Dec 31, 2009 11:05 pm
Location: Gernsbach (Germany)
Contact:

Re: PB.Ex MSSQL (Windows)

Post by RSBasic »

Addendum: It's not a bug. I forgot to activate Unicode during testing. The output is working.
Image
Image
User avatar
RSBasic
Moderator
Moderator
Posts: 1218
Joined: Thu Dec 31, 2009 11:05 pm
Location: Gernsbach (Germany)
Contact:

Re: PB.Ex MSSQL (Windows)

Post by RSBasic »

PB.Ex MSSQL 1.0.1.0 was published.

Changelog:
  • Bugfix: XML-Code
Image
Image
dige
Addict
Addict
Posts: 1247
Joined: Wed Apr 30, 2003 8:15 am
Location: Germany
Contact:

Re: PB.Ex MSSQL (Windows)

Post by dige »

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
"Daddy, I'll run faster, then it is not so far..."
User avatar
jacdelad
Addict
Addict
Posts: 1431
Joined: Wed Feb 03, 2021 12:46 pm
Location: Planet Riesa
Contact:

Re: PB.Ex MSSQL (Windows)

Post by jacdelad »

How does it work with blobs...if it does?
PureBasic 6.04/XProfan X4a/Embarcadero RAD Studio 11/Perl 5.2/Python 3.10
Windows 11/Ryzen 5800X/32GB RAM/Radeon 7770 OC/3TB SSD/11TB HDD
Synology DS1821+/36GB RAM/130TB
Synology DS920+/20GB RAM/54TB
Synology DS916+ii/8GB RAM/12TB
User avatar
RSBasic
Moderator
Moderator
Posts: 1218
Joined: Thu Dec 31, 2009 11:05 pm
Location: Gernsbach (Germany)
Contact:

Re: PB.Ex MSSQL (Windows)

Post by RSBasic »

PB.Ex MSSQL 1.0.2.0 has been released.

Changelog:
  • Updated: .NET Framework 3.5 > .NET Framework 4.7.2
  • Bugfix: Characters (', <, >, &) are now escaped.
  • Added: GetDatabaseBlobEx()
  • Added: SetDatabaseBlobEx()
  • Changed: The return value is now 1 if successful and not 0.

jacdelad wrote: Tue Jan 31, 2023 11:07 pm How does it work with blobs...if it does?
I added GetDatabaseBlobEx() and SetDatabaseBlobEx().
See first post for information and example code.
Image
Image
User avatar
jacdelad
Addict
Addict
Posts: 1431
Joined: Wed Feb 03, 2021 12:46 pm
Location: Planet Riesa
Contact:

Re: PB.Ex MSSQL (Windows)

Post by jacdelad »

THANK YOU VERY MUCH!!! :D
PureBasic 6.04/XProfan X4a/Embarcadero RAD Studio 11/Perl 5.2/Python 3.10
Windows 11/Ryzen 5800X/32GB RAM/Radeon 7770 OC/3TB SSD/11TB HDD
Synology DS1821+/36GB RAM/130TB
Synology DS920+/20GB RAM/54TB
Synology DS916+ii/8GB RAM/12TB
Jagermeister
Enthusiast
Enthusiast
Posts: 136
Joined: Thu Nov 15, 2012 11:38 pm
Location: Los Angeles

Re: PB.Ex MSSQL (Windows)

Post by Jagermeister »

Hello, RSBasic,

Quick observation, if I use a query like (SELECT count(*) FROM myTable;) the XML doesn't reflect the result...

Code: Select all

<?xml version="1.0" encoding="UTF-16"?>

<Records>
  <Record/>
</Records>
...though it is in the raw output.

Code: Select all

<Records><Record><>87325</></Record></Records>

UPDATE: Disregard (or regard as a tip), I as able to use AS (SELECT count(*) AS cnt FROM myTable;) with success. I suppose SQL internal functions will output unlabeled tags and that messes with the XML.
User avatar
RSBasic
Moderator
Moderator
Posts: 1218
Joined: Thu Dec 31, 2009 11:05 pm
Location: Gernsbach (Germany)
Contact:

Re: PB.Ex MSSQL (Windows)

Post by RSBasic »

PB.Ex MSSQL 1.0.3.0 has been released.

Changelog:
  • Improved: ExecuteSQLQuery(): If no column is returned from the SQL server, then "NoColumn" is returned.


@Jagermeister
Done
Image
Image
Jagermeister
Enthusiast
Enthusiast
Posts: 136
Joined: Thu Nov 15, 2012 11:38 pm
Location: Los Angeles

Re: PB.Ex MSSQL (Windows)

Post by Jagermeister »

:shock: Amazing. Thank you!

I hate to ask (because you've done such an incredible job on it already), but would you have any hint as to why the debug output is fine, both formatted and straight, but if I do...

Code: Select all

If ExecuteSQLQuery("Persist Security Info=False;Trusted_Connection=True;TrustServerCertificate=True;database=" + Database$ + ";server=" + Server$ + ";Encrypt=false;", query$, 0, @Output$, @ErrorOutput$)
    ParseXML(#xml, Output$)
    ;     FormatXML(#xml, #PB_XML_WindowsNewline | #PB_XML_ReFormat | #PB_XML_ReIndent | #PB_XML_ReduceSpace)
    Output$ = ComposeXML(#xml)
  Else
    Debug "Error: " + ErrorOutput$
  EndIf
  CloseLibrary(PBEx_MSSQL)
EndIf

If Not InitCGI() Or Not ReadCGI()
  End
EndIf

WriteCGIHeader(#PB_CGI_HeaderContentType, "text/plain", #PB_CGI_LastHeader)
WriteCGIString(Output$)
...I get blank CGI output? <-- Appears to be the Output$=Space(1000000) from earlier in your example.

I have tried application/xml , text/xml , wrapping Output$ in html and body tags, but still nothing :[
User avatar
RSBasic
Moderator
Moderator
Posts: 1218
Joined: Thu Dec 31, 2009 11:05 pm
Location: Gernsbach (Germany)
Contact:

Re: PB.Ex MSSQL (Windows)

Post by RSBasic »

Hello Jagermeister

I started a web server and created a CGI application. It works for me.
I suspect that you forgot to insert PB.Ex_MSSQL_x64.dll and PB.Ex_MSSQL_x86.dll in the "cgi-bin" folder. Are these files in the folder?
Image
Image
Jagermeister
Enthusiast
Enthusiast
Posts: 136
Joined: Thu Nov 15, 2012 11:38 pm
Location: Los Angeles

Re: PB.Ex MSSQL (Windows)

Post by Jagermeister »

I have the dll and exe in the same folder with correct permissions. It's running on Windows Server 2022, IIS, and SQL 2022. Your code returns correctly, the parse returns 0, and debugging the variable just before WriteCGIString appears to be normal. The web page is built in SpiderBasic on the server, and the listener is built with PureBasic on the same server.

With your example code I will get the 1000000 space string. If I add something like "hello" to the string before WriteCGIString, the web page will only report "hello". Even if I encode to Base64, the web page receives only the end of the chunk "AA==". And that's not using your library at all! So weird.

Perhaps it all points to a bug compiling on Windows Server. Ah well, I'll start a VM in the same configuration but with Windows Pro and see how that does.

Thanks!
Jagermeister
Enthusiast
Enthusiast
Posts: 136
Joined: Thu Nov 15, 2012 11:38 pm
Location: Los Angeles

Re: PB.Ex MSSQL (Windows)

Post by Jagermeister »

Found my issue, RS: https://forums.spiderbasic.com/viewtopi ... 9544#p9544

Thanks for your excellent contributions!
Post Reply