ODBC and creating DSN's

[captain_skank]

Is it possible to create a DSN from within PB ??

My app uses mysql through ODBC but I have to set up DSN's for each workstation and I wondered if anyone has a way to do this automatically.

Cheers

[srod]

Code: Select all

#ODBC_ADD_DSN    = 1    
#ODBC_CONFIG_DSN = 2 
#ODBC_REMOVE_DSN = 3 


;/////////////////////////////////////////////////////////////////////////////////
;The following takes a connection string in which the attributes are separated by semi-colons.
;We must switch these for null terminators and then terminate the whole string with a double null.
Procedure.i AddConnection(driver$, connectString$) 
  Protected result, base, *mem.CHARACTER, *ptr.CHARACTER, len, i
  If driver$ And connectString$
    len = Len(connectString$)
    base = AllocateMemory((len + 2)<<(SizeOf(CHARACTER)-1))
    If base
      *mem = base
      *ptr = @connectString$
      ;Switch all ; for nulls.
        For i = 1 To len
          If *ptr\c <> ';'
            *mem\c = *ptr\c
          EndIf
          *mem + SizeOf(CHARACTER)
          *ptr + SizeOf(CHARACTER)
        Next
      result = SQLConfigDataSource_(0, #ODBC_ADD_DSN, driver$, base) 
      FreeMemory(base)
      If result 
        ProcedureReturn 1 
      EndIf 
    EndIf
  EndIf
EndProcedure 
;/////////////////////////////////////////////////////////////////////////////////


;/////////////////////////////////////////////////////////////////////////////////
Procedure.i RemoveConnection(Driver.s,DSN.s) 
    Result=SQLConfigDataSource_(0,#ODBC_REMOVE_DSN,Driver,"DSN="+DSN) 
    If Result 
      ProcedureReturn 1 
    EndIf 
EndProcedure 
;/////////////////////////////////////////////////////////////////////////////////

Example useage :

Code: Select all

result = AddConnection("Microsoft Access Driver (*.mdb)","Server=127.0.0.1; Description=MyDescription;DSN=MyDSN;DBQ=customers.mdb;UID=;PWD=;") 

[gnozal]

I usually use this code to create DSNs : http://www.purebasic.fr/english/viewtop ... 12&t=16474

[captain_skank]

Thanks guys, very usefull info.

Funnily enough a search on DSN returned no result

[jamba]

the code that I use is here:

http://www.purebasic.fr/english/viewtop ... 12&t=41680

although it does essentially the same thing as srod's and gnozal's code.

[captain_skank]

Sorry to revive an old topic....

I've been playing around with this recently and I just cant get this to work correctly with mysql.

I can't get srods create dsn code working correctly but simplified it for myself for test purposes.

Code: Select all

#ODBC_ADD_DSN    = 1   
#ODBC_CONFIG_DSN = 2
#ODBC_REMOVE_DSN = 3 

Result = SQLConfigDataSource_(0, #ODBC_ADD_DSN, "MySQL ODBC 5.1 driver", "DSN=mydsn" + Chr(0) + "DESCRIPTION=MyDescription" + Chr(0) + "SERVER=localhost" + Chr(0) + "PORT=3306" + Chr(0) + "UID=root" + Chr(0) + "PWD=password" + Chr(0) + "DATABASE=test_db" + Chr(0)+ Chr(0))

This will create a DSN called mydsn of the mysql type but thats it. It doesn't set up any of the config details such as user or password etc.

Anybody suggest where i'm going wrong ??

Thanks for looking.

[Rings]

Code: Select all

Chr(0)

thats bad style for strings.
in my routines, i replaced all those ; in memory with the NULL'S
to get a DSN String correctly

[captain_skank]

Thanks for the reply rings - makes no difference with nulls - still gives the same end result, a not correctly set up dsn.

I only posted the simplified code so as not to repeat all of srod's code with my few changes.

for the sake of completenes though

Code: Select all

#ODBC_ADD_DSN    = 1   
#ODBC_CONFIG_DSN = 2
#ODBC_REMOVE_DSN = 3 

Procedure.i RemoveConnection(Driver.s,DSN.s)
  Result=SQLConfigDataSource_(0,#ODBC_REMOVE_DSN,Driver,"DSN="+DSN)
  If Result
    ProcedureReturn 1
  EndIf
EndProcedure 

;/////////////////////////////////////////////////////////////////////////////////
;The following takes a connection string in which the attributes are separated by semi-colons.
;We must switch these for null terminators and then terminate the whole string with a double null.
Procedure.i AddConnection(driver$, connectString$)

  Protected Result, base, *mem.CHARACTER, *ptr.CHARACTER, len, i
  
  If driver$ And connectString$
    len = Len(connectString$)
    base = AllocateMemory((len + 2)<<(SizeOf(CHARACTER)-1))
    If base
      *mem = base
      *ptr = @connectString$
      ;Switch all ; for nulls.
      For i = 1 To len
        If *ptr\c <> ';'
          *mem\c = *ptr\c
        EndIf
        *mem + SizeOf(CHARACTER)
        *ptr + SizeOf(CHARACTER)
      Next
      Result = SQLConfigDataSource_(0, #ODBC_ADD_DSN, driver$, base)
      FreeMemory(base)
      If Result
        ProcedureReturn 1
      EndIf
    EndIf
  EndIf
  
  
EndProcedure
;/////////////////////////////////////////////////////////////////////////////////

Result = AddConnection("MySQL ODBC 5.1 driver","DSN=mydsn; DESCRIPTION=MyDescription; SERVER=localhost; PORT=3306; USER=root; PASSWORD=password; DATABASE=test_db;") 

;Result = SQLConfigDataSource_(0, #ODBC_ADD_DSN, "MySQL ODBC 5.1 driver", "DSN=mydsn" + Chr(0) + "DESCRIPTION=MyDescription" + Chr(0) + "SERVER=localhost" + Chr(0) + "PORT=3306" + Chr(0) + "UID=root" + Chr(0) + "PWD=password" + Chr(0) + "DATABASE=test_db" + Chr(0)+ Chr(0))

;Result = SQLConfigDataSource_(0, #ODBC_ADD_DSN, "MySQL ODBC 5.1 driver", "DSN=mydsn\0, DESCRIPTION=MyDescription\0, SERVER=localhost\0, PORT=3306\0, UID=root\0, PWD=password\0, DATABASE=test_db")

MessageRequester(Chr(0),"Created")

Result = RemoveConnection("MySQL ODBC 5.1 driver","mydsn") 

MessageRequester("","Deleted")

[gnozal]

Sorry to revive an old topic....
...
This will create a DSN called mydsn of the mysql type but thats it. It doesn't set up any of the config details such as user or password etc.

According to www.connectionstrings.com, if you are using MySQL ODBC, your connection string should look like this :

Code: Select all

Driver={MySQL ODBC 5.1 Driver};Server=myServerAddress;Port=3306;Database=myDataBase;User=myUsername; Password=myPassword;Option=3;

Note the User / Password instead of your Uid / Pwd.

[captain_skank]

Yeah - srods original used uid and pwd and wouldn't work so I chnaged to user and password and then it did work but still doesn't create a complete dsn.

I've looked at variuos microsoft examples on the web and can't figure why this won't work, cos it looks ok to me.

Cheers

[gnozal]

Maybe it's an issue with the 5.1 driver, or some security feature.

Did you try with the MySQL ODBC 3.51 Driver ?

[captain_skank]

Yep - works fine with 3.51. so it must be the connection string ??

I'll have another trawl over mysql's docs and see if I can track this down as I need to use the 5.1 connector.

Thanks for the help

[gnozal]

I'll have another trawl over mysql's docs and see if I can track this down as I need to use the 5.1 connector.

The connection string looks fine to me.
Imho it's a bug / issue / feature with the 5.1 driver.

[Rings]

to be clear (sometimes my brains looks like those of KCC ):

Code: Select all

Chr(0)

is not allowed, as NULL ist the string terminator.

so YOU CAN't use CHR(0) in any PB Code.

[gnozal]

Rings, he doesn't use CHR(0) in the latest code he posted.