Looking for library to access MS SQL server

byo · Post by **byo** » Fri Aug 17, 2007 1:30 am

Hi, all!

I've been reading all these MySQL and MS Access examples but I can't seem to do it right with SQL Server 2000. I've tried modifying Flype's code to a DSN string for MSSQL but I think I'm doing something wrong.

Even the InitDatabase() command doesn't exist anymore.
Could you please post a minimal example showing how I can connect to SQL Server because I don't wanna use Delphi for that?

Code: Select all


Enumeration 1 
  #ODBC_ADD_DSN            ; Ajoute une source de données utilisateur. 
  #ODBC_CONFIG_DSN         ; Configure/Modifie une source de données utilisateur existante. 
  #ODBC_REMOVE_DSN         ; Supprime une source de données utilisateur existante. 
  #ODBC_ADD_SYS_DSN        ; Ajoute une source de données système. 
  #ODBC_CONFIG_SYS_DSN     ; Configure/Modifie une source de données système existante. 
  #ODBC_REMOVE_SYS_DSN     ; Supprime une source de données système existante. 
  #ODBC_REMOVE_DEFAULT_DSN ; Supprime la source de données définies par défaut. 
EndEnumeration 

#ODBC_DRIVER_MSACCESS = "SQL Server" 

Procedure.l SQLServer_AddConnection(name.s, database.s, hwnd.l = #Null) 
  
  Protected attrs.s 
  
  attrs + "UID="         + ";" 
  attrs + "PWD="         + ";" 
  attrs + "DSN="         + name + ";" 
  attrs + "DBQ="         + database + ";" 
  attrs + "FIL="         + "SQL Server;" 
  attrs + "Driver="      + "sqlsrv32.dll;" 
  attrs + "DefaultDir="  + GetPathPart(database) + ";" 
  attrs + "Description=" + FormatDate("Créé le %dd-%mm-%yyyy, %hh:%ii:%ss;", Date()) 
  
  ReplaceString(attrs, ";", #NULL$, 2) 
  
  ProcedureReturn SQLConfigDataSource_(hWnd, #ODBC_ADD_DSN, #ODBC_DRIVER_MSACCESS, attrs) 
  
EndProcedure

Is this wrong?

mskuma · Post by **mskuma** » Tue Aug 21, 2007 12:55 pm

Hi byo, I couldn't get your code to work, but here is a workaround:

Programmatically establish a DSN using the registry:

Code: Select all

; DSN setup code from http://support.microsoft.com/kb/184608
; registry code from http://www.purebasic.fr/english/viewtopic.php?t=25657 (thanks to Thorsten1867)
; PB4 port by mskuma

uKey = 0

; Specify the DSN parameters
 
dataSourceName.s = "groover"
database.s = "your_db"
description.s = "your_desc"
driverPath.s = "c:\windows\system32\sqlsrv32.dll"
lastUser.s = ""
server.s = "your_server"
driverName.s = "SQL Server"
trustedConnection.s = "Yes" ; added by mskuma (use Windows authentication)

; Create a new user DSN key - to create a system DSN, change #HKEY_CURRENT_USER to #HKEY_LOCAL_MACHINE

If RegCreateKey_(#HKEY_CURRENT_USER,"SOFTWARE\ODBC\ODBC.INI\" + dataSourceName,@uKey)=0 
  RegSetValueEx_(uKey,"Database",0,#REG_SZ,@database,Len(database)+1) 
  RegSetValueEx_(uKey,"Description",0,#REG_SZ,@description,Len(description)+1) 
  RegSetValueEx_(uKey,"Driver",0,#REG_SZ,@driverPath,Len(driverPath)+1) 
  RegSetValueEx_(uKey,"LastUser",0,#REG_SZ,@lastUser,Len(lastUser)+1) 
  RegSetValueEx_(uKey,"Server",0,#REG_SZ,@server,Len(server)+1) 
  RegSetValueEx_(uKey,"Trusted_Connection",0,#REG_SZ,@trustedConnection,Len(trustedConnection)+1) ; added by mskuma
  RegCloseKey_(uKey) 
EndIf 

; open ODBC Data Sources key to list the new DSN in the ODBC Manager
; specify the new value
; close the key

If RegCreateKey_(#HKEY_CURRENT_USER,"SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources",@uKey)=0   
  RegSetValueEx_(uKey,dataSourceName,0,#REG_SZ,@driverName,Len(driverName)+1) 
  RegCloseKey_(uKey)
EndIf

If the above goes well, you should see the new DSN entry in your Administration tools > Data sources dialog.

Now you can adapt your PB database code to use the DSN. You mentioned there is no InitDatabase() - note this was changed to UseODBCDatabase (assuming you're using PB4.10b3):

Code: Select all

If UseODBCDatabase() 
  
  DSN.s = "groover"  
    
    If OpenDatabase(0, DSN, "", "") 
       
      If DatabaseQuery(0, "SELECT * FROM your_table") 
        While NextDatabaseRow(0) 
          Debug GetDatabaseString(0, 0) 
          Debug GetDatabaseString(0, 1) 
          Debug GetDatabaseString(0, 2) 
        Wend 
      EndIf 

    Else
      Debug "open database failed"
    EndIf 
    
Else
  Debug "UseODBCDatabase failed"
EndIf

You can use the SQL server native client driver also (by changing the DLL reference). It seems a lot faster than the regular driver. I guess this is a SQL Server 2005 feature. See how you go.

pdwyer · Post by **pdwyer** » Tue Aug 21, 2007 2:31 pm

The DLL I have (and used with Powerbasic) is basically a wrapper for ODBC so not of much use in PB which has it's own ODBC support.

That said, you don't have to use DSNs to use ODBC, if you send your own connect string (like in VBS) it should work fine (haven't tried it in PB yet though).

If you want to see what a connect string looks like then what I do is create a file dsn then open the file up with a text editor and take a look, then I know how it's supposed to work, put each line in the DSN together with a ";" and you have a connect string!

It's called a "DSNless Connection"

A search on MS's page shows code like this: (for sql pwd or trusted (NT) access)

Code: Select all

    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
    Else
        '//WARNING: This will save the username and the password with the linked table information.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
    End If

Progi1984 · Post by **Progi1984** » Tue Aug 21, 2007 3:38 pm

Why are you not using PHP DLL for MsSQL ?

Name : php_mssql.dll
Functions : http://fr2.php.net/manual/en/ref.mssql.php

mskuma · Post by **mskuma** » Fri Aug 24, 2007 10:13 am

Progi1984 wrote:Why are you not using PHP DLL for MsSQL ?

Hi Progi1984 - do you know if there is a wrapper for PB available? Also is there any merit to use this over ODBC with PB, particularly when using the native client driver? Thanks.

Progi1984 · Post by **Progi1984** » Fri Aug 24, 2007 11:05 am

Personnally, i would prefer use DLL in the case of user which don't have the good driver for ODBC...

mskuma · Post by **mskuma** » Fri Aug 24, 2007 11:18 am

Personally I'm inclined to go with the microsoft native client - because it's bound to be the fastest method to access their own product. The difference between the SQL server driver and native client is like chalk & cheese. I'm not sure however if it can be downloaded & installed independently of SQL Server tools, so in that case your suggestion is more convenient for generic deployments (if there's no issue with a wrapper).

pdwyer · Post by **pdwyer** » Fri Aug 24, 2007 12:44 pm

So was the ODBC built in to PB actually tested and found to be slow? The built-in way would be the path of least resistance and from what I've seen SQL server do over ODBC I'm very interested in what you need to achieve that needs THAT much power!

Secondly, what is it that you are doing that makes you so sure that it's teh driver that's going to be the neck and not the network or the server or the schema & indexing design or something else?

Personally if I was looking for a way to interface with SQL server and had performance in mind I think of them as two different topics. DB design, power, App power and design, network, on the performance side then features, support and cost on the driver interface side.

It's like every time you have a perf issue in your app you say that you need a faster compiler! it's gotta be pretty rare that the compiler is the neck and theres no way around it.

How does that saying go "it's not the paintbrush it's the artist" (-MCM

)

blueb · Post by **blueb** » Fri Aug 24, 2007 2:54 pm

My God Paul.... let's not get that started here!

--blueb

pdwyer · Post by **pdwyer** » Sat Aug 25, 2007 2:39 am

I figured someone would recognise it

I don't recognise "blueb" though. Who's under the alias?

blueb · Post by **blueb** » Sat Aug 25, 2007 5:03 pm

blueb's alias = Bob Houle

I've been here since version 2.3 and haven't look back since.

--Bob

mskuma · Post by **mskuma** » Sun Aug 26, 2007 12:57 am

pdwyer wrote:So was the ODBC built in to PB actually tested and found to be slow?

I agree with you Paul. I think the combo of PB, ODBC & MS Native Client is the way to go for SQL Server - likely to be as fast as anyone else's 3rd party driver. Though I can't say that from rigorous testing - I have seen the difference between the old SQL Server driver & the newer native client via ODBC, and the difference is remarkable, so I'm personally satisfied this combo is probably fast enough.

Cheers, Michael.

Rook Zimbabwe · Post by **Rook Zimbabwe** » Mon Aug 27, 2007 4:13 am

I recall something about InitDatabase() being changed in the new version...

I owuld just have a Server program running on the HOST to access the database and then the CLIENTS just hit the SERVER with DATABASE QUERIES that are exectured and returned...

Whats this issue with that?

mskuma · Post by **mskuma** » Mon Aug 27, 2007 8:05 am

Rook Zimbabwe wrote:I recall something about InitDatabase() being changed in the new version...

There's no issue with that (see earlier posting above, i.e. UseODBCDatabase()) nor the scenario you mentioned AFAIK.

byo · Post by **byo** » Mon Aug 27, 2007 12:19 pm

Thanks, mskuma.

I entirely forgot about this topic.

That's exactly what I'm trying to do. But I need to supply the UID and PWD because OpenDatabase() seems to not work if I supply a username and password. I'll give it a go when I'm home. If Purebasic had a native support for MSSQLServer directly it would destroy Delphi.