It is currently Tue Dec 01, 2020 3:11 pm

All times are UTC + 1 hour




Post new topic Reply to topic  [ 6 posts ] 
Author Message
 Post subject: PB.Ex MSSQL (Windows)
PostPosted: Fri Mar 09, 2018 10:35 pm 
Offline
Moderator
Moderator

Joined: Thu Dec 31, 2009 11:05 pm
Posts: 1113
Location: Gernsbach (Germany)
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:
Copyright © 2019 RSBasic.de


Download: http://www.rsbasic.de/downloads/downloa ... _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 :)

_________________
ImageImageImageImage Image


Top
 Profile  
Reply with quote  
 Post subject: Re: PB.Ex MSSQL (Windows)
PostPosted: Sat Mar 10, 2018 9:54 am 
Offline
Enthusiast
Enthusiast

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!


Top
 Profile  
Reply with quote  
 Post subject: Re: PB.Ex MSSQL (Windows)
PostPosted: Sat Mar 10, 2018 6:22 pm 
Offline
Moderator
Moderator

Joined: Thu Dec 31, 2009 11:05 pm
Posts: 1113
Location: Gernsbach (Germany)
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.

_________________
ImageImageImageImage Image


Top
 Profile  
Reply with quote  
 Post subject: Re: PB.Ex MSSQL (Windows)
PostPosted: Sat Mar 10, 2018 6:47 pm 
Offline
Moderator
Moderator

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

_________________
ImageImageImageImage Image


Top
 Profile  
Reply with quote  
 Post subject: Re: PB.Ex MSSQL (Windows)
PostPosted: Tue Mar 13, 2018 7:55 pm 
Offline
Moderator
Moderator

Joined: Thu Dec 31, 2009 11:05 pm
Posts: 1113
Location: Gernsbach (Germany)
PB.Ex MSSQL 1.0.1.0 was published.

Changelog:
  • Bugfix: XML-Code

_________________
ImageImageImageImage Image


Top
 Profile  
Reply with quote  
 Post subject: Re: PB.Ex MSSQL (Windows)
PostPosted: Fri Sep 04, 2020 9:29 am 
Offline
Addict
Addict
User avatar

Joined: Wed Apr 30, 2003 8:15 am
Posts: 1054
Location: Germany
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..."


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 6 posts ] 

All times are UTC + 1 hour


Who is online

Users browsing this forum: No registered users and 13 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  

 


Powered by phpBB © 2008 phpBB Group
subSilver+ theme by Canver Software, sponsor Sanal Modifiye