PureBasic Forums - English

Posted: **Wed Apr 28, 2010 9:36 am**

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

Posted: **Wed Apr 28, 2010 10:14 am**

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=;")

Posted: **Wed Apr 28, 2010 10:15 am**

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

Posted: **Wed Apr 28, 2010 10:52 am**

Thanks guys, very usefull info.

Funnily enough a search on DSN returned no result

Posted: **Wed Apr 28, 2010 11:33 am**

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.

Posted: **Thu Mar 17, 2011 12:44 pm**

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.

Posted: **Thu Mar 17, 2011 12:54 pm**

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

Posted: **Thu Mar 17, 2011 1:05 pm**

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

Posted: **Thu Mar 17, 2011 1:17 pm**

captain_skank wrote: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.

Posted: **Thu Mar 17, 2011 1:31 pm**

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

Posted: **Thu Mar 17, 2011 1:59 pm**

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

Did you try with the MySQL ODBC 3.51 Driver ?

Posted: **Thu Mar 17, 2011 3:11 pm**

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

Posted: **Thu Mar 17, 2011 3:14 pm**

captain_skank wrote: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.

Posted: **Thu Mar 17, 2011 3:16 pm**

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.

Posted: **Thu Mar 17, 2011 3:56 pm**

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

PureBasic Forums - English

ODBC and creating DSN's

ODBC and creating DSN's

Re: ODBC and creating DSN's

Re: ODBC and creating DSN's

Re: ODBC and creating DSN's

Re: ODBC and creating DSN's

Re: ODBC and creating DSN's

Re: ODBC and creating DSN's

Re: ODBC and creating DSN's

Re: ODBC and creating DSN's

Re: ODBC and creating DSN's

Re: ODBC and creating DSN's

Re: ODBC and creating DSN's

Re: ODBC and creating DSN's

Re: ODBC and creating DSN's

Re: ODBC and creating DSN's