PureBasic Forums - English

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
    
    
    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

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!

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.

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

PB.Ex MSSQL 1.0.1.0 was published.

Changelog:

• Bugfix: XML-Code

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

How does it work with blobs...if it does?

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.

THANK YOU VERY MUCH!!!

Hello, RSBasic,

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

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.

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

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...
...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 :[

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?

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!

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

Thanks for your excellent contributions!