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:
- ConnectionString$: Connection data for the server and the database.
- SQL$: SQL code to execute a query.
- Timeout: Time in seconds, how long the process should be waited for. If 0 is specified, it waits until the operation is complete.
- @Output: The result of the query is stored in XML format in the string variable.
- @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:
- ConnectionString$: Connection data for the server and for the database.
- Table$: Name of the table.
- Column$: Column name.
- Where$: Condition to find a unique record entry.
- Memory: Destination memory for the exporting file. If 0, then the length of the file is returned.
- MemoryLength: Length of the target memory. The length must be determined first.
- @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:
- ConnectionString$: Connection data for the server and for the database.
- Table$: Name of the table.
- Column$: Column name.
- Where$: Condition to find a unique record entry.
- Memory: Memory of a loaded file.
- MemoryLength: Length of memory.
- @ErrorOutput: If an error occurs, the error message is saved to the string variable.
- Return value:
- 1: The process was successful.
- Syntax:
- .NET Framework 4.7.2 or higher
- Unicode activation (standard from PB 5.50)
Licence: This DLL file is free of charge and may be used both privately and commercially.
The following copyright texts must be provided:
Download: https://www.rsbasic.de/downloads/downlo ... _MSSQL.zipCopyright © 2019 RSBasic.de
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 - Syntax:
- Syntax: