ODBC and Unicode

[Motu]

I finally got ODBC to work and can now read from excel files. Anyway, I have a huge problem with Unicode files. When I read from them, all characters are only "?". But when I set Compiler Settings to Unicode, non of the SQL commands work anymore

Example:
Result=Makeconnection("Microsoft Excel Driver (*.xls)","DSN=PureBasic_DSN;Description=Description For Purebasic Excel;FileType=Excel97;DBQ="+File$+";")

Anyone got an idea to solve this problem?

Do the Purebasic Database commands support Unicode

[netmaestro]

For your SQL commands, try: PeekS(@"SOMESQLCOMMAND", #PB_Ascii) to force the string to be read as Ascii while in unicode mode. I can't test anything these days but it seems like it should work.

[ABBKlaus]

i have absolutely no problems using SQL / unicode and Excel files.
Could you try this Example program : http://www.purebasicpower.de/downloads/DSN-Test.zip

Regards Klaus

[Motu]

String1.s = "Microsoft Excel Driver (*.xls)"
String2.s = "DSN=PureBasic_DSN;Description=Description For Purebasic Excel;FileType=Excel97;DBQ="+File$+";"

Result=Makeconnection(PeekS(@String1,Len(String1),#PB_Ascii),PeekS(@String2,Len(String2),#PB_Ascii))

does work without unicode compiler setting, but does not work with unicode compiler setting

[Motu]

any different ideas?

[ABBKlaus]

String1.s = "Microsoft Excel Driver (*.xls)"
String2.s = "DSN=PureBasic_DSN;Description=Description For Purebasic Excel;FileType=Excel97;DBQ="+File$+";"

Result=Makeconnection(PeekS(@String1,Len(String1),#PB_Ascii),PeekS(@String2,Len(String2),#PB_Ascii))

does work without unicode compiler setting, but does not work with unicode compiler setting

most likely your problem lies in the Makeconnection routine, if you could post it here we could tell you that

Regards Klaus

[Motu]

#ODBC_ADD_DSN = 1 ; Add Data source
#ODBC_CONFIG_DSN = 2 ; Configure (edit) Data source
#ODBC_REMOVE_DSN = 3 ; Remove Data source
Procedure Makeconnection(Driver.s,strAttributes.s)
Result=OpenLibrary(1,"ODBCCP32.DLL")
If Result
lpszDriver.s=Driver
MyMemory=AllocateMemory(Len(strAttributes))
CopyMemory(@strAttributes,MyMemory,Len(strAttributes))
For l=1 To Len(strAttributes )
If PeekB(MyMemory +l-1)=Asc(";"):PokeB(MyMemory +l-1,0): EndIf
Next l
Result = CallFunction(1, "SQLConfigDataSource", 0,#ODBC_ADD_DSN,lpszDriver.s,MyMemory )
NewResult=SQLConfigDataSource_(0,#ODBC_ADD_DSN,lpszDriver.s,MyMemory )

FreeMemory(MyMemory)
CloseLibrary(1)
If Result
ProcedureReturn 1
EndIf
EndIf
EndProcedure

Procedure DeleteConnection(Driver.s,DSN.s)
Result=OpenLibrary(1,"ODBCCP32.DLL")
If Result
lpszDriver.s=Driver
strAttributes.s = "DSN="+DSN
Result = CallFunction(1, "SQLConfigDataSource", 0,#ODBC_REMOVE_DSN,lpszDriver.s,strAttributes )
CloseLibrary(1)
If Result
ProcedureReturn 1;MessageRequester("Info","DSN Delete",0)
EndIf
EndIf
EndProcedure


MeinPointer.l
Procedure GetDBHandle()
Shared MeinPointer.l
!EXTRN _PB_DataBase_CurrentObject;_PB_DataBase_CurrentObject
!MOV dword Eax,[_PB_DataBase_CurrentObject]
!MOV dword [v_MeinPointer], Eax
ProcedureReturn MeinPointer
EndProcedure

[Motu]

Here you go - I copy and pasted it from some guy's source somewhere from this board.

[Motu]

; English forum:
; Author: Rings (updated for PB3.92+ by Lars)
; Date: 05. May 2003

; Notes: This example works fine for Access, if you want to make a DSN connection to a MsSQL 2000 Server
; then create your dsn by hand or read the docu about M$-sql-server carefully.

; Enhanced Database example
;by Siegfried Rings (CodeGuru)
;File$ = OpenFileRequester("PureBasic - Open", "C:\*.mdb", "Microsoft Access (*.mdb)|*.mdb;*.bat|Microsoft Excel (*.xls)|*.xls", 1)

Here is Author information I could not find, sorry for that.

[Motu]

You where right, ABBKlaus, the bug was in the MakeConnection procedure. Now I got finaly a working code (without all the console commands). If it's usefull to somebody, here it is:

#ODBC_ADD_DSN = 1 ; Add Data source
#ODBC_CONFIG_DSN = 2 ; Configure (edit) Data source
#ODBC_REMOVE_DSN = 3 ; Remove Data source

Procedure Makeconnection(Driver.s,Attributes.s)
Result=OpenLibrary(1,"ODBCCP32.DLL")
If Result <> 0
Result = CallFunction(1, "SQLConfigDataSource", 0,#ODBC_ADD_DSN,Driver.s,@Attributes)
NewResult=SQLConfigDataSource_(0,#ODBC_ADD_DSN,Driver.s,@Attributes)
CloseLibrary(1)
EndIf
EndProcedure

Procedure DeleteConnection(Driver.s,DSN.s)
Result=OpenLibrary(1,"ODBCCP32.DLL")
If Result
strAttributes.s = "DSN="+DSN
Result = CallFunction(1, "SQLConfigDataSource", 0,#ODBC_REMOVE_DSN,Driver,strAttributes )
CloseLibrary(1)
If Result
ProcedureReturn 1
EndIf
EndIf
EndProcedure

File$ = "C:\ME2\Ressources\Text\Legend_TextGer.xls"
File$ = "C:\ME2\Ressources\Text\Legend_TextRus2.xls"

UseODBCDatabase()

Makeconnection("Microsoft Excel Driver (*.xls)","DSN=PureBasic_DSN;Description=Description For Purebasic Excel;FileType=Excel97;DBQ="+File$+";")

Dim DatabaseType.s(4)
DatabaseType(0) = "Unknown"
DatabaseType(1) = "Numeric"
DatabaseType(2) = "String"
DatabaseType(3) = "Float"

User$=""
Password$=""
#Database=1
Result = OpenDatabase(#Database, "PureBasic_DSN", User$, Password$)
If Result
Browse$= "SELECT * FROM [Legend_Text$]"
If DatabaseQuery(#Database,Browse$)
NbColumns = DatabaseColumns(#Database)
CreateFile(0,"Temp2.txt")
While NextDatabaseRow(#Database)
WriteStringN(0,GetDatabaseString(#Database,0)+" ; " +GetDatabaseString(#Database,1),#PB_Unicode)
Wend
CloseFile(0)
EndIf
Else
MessageRequester("Info", "Operation canceled", 0)
EndIf

;and delete:
DeleteConnection("Microsoft Access Driver (*.mdb)","PureBasic_DSN")

[ABBKlaus]

Your routine is not unicode compatible !

Use this one :

Code: Select all

#ODBC_ADD_DSN                       =   1 ; Add Data source
#ODBC_REMOVE_DSN                    =   3 ; Remove Data source
Procedure.l MakeKeywordValuePairs(Attributes$)
  ; ConfigDSN Function from M$
  ; http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcconfigdsn.asp
  ; Each pair is terminated with a null byte, and the entire list is terminated with a null byte.
  ; (That is, two null bytes mark the End of the list.) 
  While Right(Attributes$,2)<>";;"
    Attributes$+";"
  Wend
  
  ; Allocate enough memory in both Ascii and Unicode mode + space for the terminating zero character
  *LPAttribMem=AllocateMemory(Len(Attributes$)*SizeOf(character)+SizeOf(character))
  
  ; Copy string to memory
  PokeS(*LPAttribMem,Attributes$,Len(Attributes$))
  
  ; Replace each ';' with zero character
  For L=1 To Len(Attributes$)
    CompilerIf #PB_Compiler_Unicode
      If PeekW(*LPAttribMem + (l-1) * SizeOf(character))=Asc(";")
        PokeW(*LPAttribMem + (l-1) * SizeOf(character),0)
      EndIf
    CompilerElse
      If PeekB(*LPAttribMem + l -1)=Asc(";")
        PokeB(*LPAttribMem + l -1,0)
      EndIf
    CompilerEndIf
  Next
  
  ProcedureReturn *LPAttribMem
EndProcedure

Procedure.b MakeConnection(Driver$,Attributes$)
  *KVPBuffer=MakeKeywordValuePairs(Attributes$)
  
  Result=SQLConfigDataSource_(0,#ODBC_ADD_DSN,Driver$,*KVPBuffer)
  
  FreeMemory(*KVPBuffer)
  
  ProcedureReturn Result
EndProcedure

Procedure.b DeleteConnection(Driver$,DSN$)
  DSN$="DSN="+DSN$
  
  *KVPBuffer=MakeKeywordValuePairs(DSN$)
  
  Result=SQLConfigDataSource_(0,#ODBC_REMOVE_DSN,@Driver$,*KVPBuffer)
  
  FreeMemory(*KVPBuffer)
  
  ProcedureReturn Result
EndProcedure

and the code for peeking the DB-Handle :

Code: Select all

CompilerSelect #PB_Compiler_Version
CompilerCase 410
  UseODBCDatabase() ; new in PB4.10
  ;Peeking SQL_HANDLE PureBasic Version 4.10 Beta 4 
  Macro SQL_HANDLE_ENV(Database) 
    PeekL(PeekL(IsDatabase(Database))-4) 
  EndMacro 
  
  Macro SQL_HANDLE_STMT(Database) 
    PeekL(PeekL(IsDatabase(Database)+4)+4) 
  EndMacro 
  
  Macro SQL_HANDLE_DBC(Database) 
    PeekL(PeekL(IsDatabase(Database)+4)) 
  EndMacro 
CompilerCase 4.02
  InitDatabase()
  ;Peeking SQL_HANDLE PureBasic Version 4.02
  Macro SQL_HANDLE_ENV(Database) 
    0
  EndMacro 
  
  Macro SQL_HANDLE_STMT(Database) 
    PeekL(IsDatabase(Database)+4)
  EndMacro 
  
  Macro SQL_HANDLE_DBC(Database) 
    PeekL(IsDatabase(Database))
  EndMacro 
CompilerEndSelect

Code for getting the tablenames :

Code: Select all

#SQL_SUCCESS                        =   0
#SQL_SUCCESS_WITH_INFO              =   1
Procedure.l GetDatabaseTables(Database) 
  SQLCancel_(SQL_HANDLE_STMT(Database))
  res.w=SQLTables_(SQL_HANDLE_STMT(Database),0,0,0,0,0,0,0,0)
  If res = 0 Or res = 1 ; #SQL_SUCCESS / #SQL_SUCCESS_WITH_INFO
    ProcedureReturn 1
  EndIf
EndProcedure 

Procedure.l GetDatabaseDBNames(Database) 
  SQLCancel_(SQL_HANDLE_STMT(Database))
  res.w=SQLTables_(SQL_HANDLE_STMT(Database),"%",-3,"",-3,"",-3,"",-3) 
  If res = 0 Or res = 1 ; #SQL_SUCCESS / #SQL_SUCCESS_WITH_INFO
    ProcedureReturn 1
  EndIf
EndProcedure 

Example Nr.1 for Access MDB :

Code: Select all

Driver$="Microsoft Access Driver (*.mdb)"
DSN$="DB-Test"
File$=GetCurrentDirectory()+"DB-Test.mdb"
Attrib$="DSN="+DSN$+";DBQ="+File$
If MakeConnection(Driver$,Attrib$)
  Debug "MakeConnection ok"
  If OpenDatabase(1,"DB-Test","Admin","")
    Debug "OpenDatabase ok"
    Debug "ID="+Str(IsDatabase(1))
    
    Debug "SQL_HANDLE_ENV="+Hex(SQL_HANDLE_ENV(1))
    Debug "SQL_HANDLE_STMT="+Hex(SQL_HANDLE_STMT(1))
    Debug "SQL_HANDLE_DBC="+Hex(SQL_HANDLE_DBC(1))
    
    If GetDatabaseTables(1)
      While NextDatabaseRow(1)
        Debug "Database: " + GetDatabaseString(1,0)
        Debug "Table:" + GetDatabaseString(1,2)
        Debug "Type: " + GetDatabaseString(1,3)
        Debug "-----------------------------------------------------------------"
      Wend 
    Else
      GetSQLMessages(1)
    EndIf
    Debug "*******"
    If GetDatabaseDBNames(1)
      While NextDatabaseRow(1)
        Debug "Database: " + GetDatabaseString(1,0)
        Debug "Table:" + GetDatabaseString(1,2)
        Debug "Type: " + GetDatabaseString(1,3)
        Debug "-----------------------------------------------------------------"
      Wend 
    Else
      GetSQLMessages(1)
    EndIf
    
    Query$="SELECT * FROM TBL_Fabriknr"
    If DatabaseQuery(1,Query$)
      Debug "Query ok"
      Columns=DatabaseColumns(1)
      While NextDatabaseRow(1)
        If columns
          For i=1 To columns
            Debug GetDatabaseString(1,i-1)
          Next
        EndIf
      Wend
    Else
      Debug "Query failed"
      Debug DatabaseError()
    EndIf
    
    CloseDatabase(1)
  EndIf 
  If DeleteConnection(Driver$,DSN$)
    Debug "DeleteConnection ok"
  EndIf
EndIf

Example Nr.2 for MS-Excel :

Code: Select all

Driver$="Microsoft Excel Driver (*.xls)"
DSN$="DB-Test"
File$=GetCurrentDirectory()+"Excel97.xls"
Attrib$="DSN="+DSN$+";Description=Description For Purebasic Excel;FileType=Excel97;DBQ="+File$+";"
Result=Makeconnection(Driver$,Attrib$) 
If MakeConnection(Driver$,Attrib$)
  Debug "MakeConnection ok"
  If OpenDatabase(1,"DB-Test","Admin","")
    Debug "OpenDatabase ok"
    Debug "ID="+Str(IsDatabase(1))
    
    Debug "SQL_HANDLE_ENV="+Hex(SQL_HANDLE_ENV(1))
    Debug "SQL_HANDLE_STMT="+Hex(SQL_HANDLE_STMT(1))
    Debug "SQL_HANDLE_DBC="+Hex(SQL_HANDLE_DBC(1))
    
    If GetDatabaseTables(1)
      While NextDatabaseRow(1)
        Debug "Database: " + GetDatabaseString(1,0)
        Debug "Table:" + GetDatabaseString(1,2)
        Debug "Type: " + GetDatabaseString(1,3)
        Debug "-----------------------------------------------------------------"
      Wend 
    Else
      GetSQLMessages(1)
    EndIf
    Debug "*******"
    If GetDatabaseDBNames(1)
      While NextDatabaseRow(1)
        Debug "Database: " + GetDatabaseString(1,0)
        Debug "Table:" + GetDatabaseString(1,2)
        Debug "Type: " + GetDatabaseString(1,3)
        Debug "-----------------------------------------------------------------"
      Wend 
    Else
      GetSQLMessages(1)
    EndIf
    
    Query$="SELECT * FROM [Tabelle1$]"
    If DatabaseQuery(1,Query$)
      Debug "Query ok"
      Columns=DatabaseColumns(1)
      While NextDatabaseRow(1)
        If columns
          For i=1 To columns
            Debug GetDatabaseString(1,i-1)
          Next
        EndIf
      Wend
    Else
      Debug "Query failed"
      Debug DatabaseError()
    EndIf
    
    CloseDatabase(1)
  EndIf
  
  If DeleteConnection(Driver$,DSN$)
    Debug "DeleteConnection ok"
  EndIf
EndIf

Regards Klaus

[Motu]

The program will output every Data in the "C:\ME2\Ressources\Text\Legend_TextRus2.xls" file into a unicode text file named "temp2.txt".

Thanks for the help

[Kwai chang caine]

@ABBKlaus

Is it normal this part of code not works for me in your zip code ?
http://www.purebasic.fr/english/viewtop ... 26#p217726

Code: Select all

If GetDatabaseTables(1)
    
    While NextDatabaseRow(1)
     Debug "Database: " + GetDatabaseString(1,0)
     Debug "Table:" + GetDatabaseString(1,2)
     Debug "Type: " + GetDatabaseString(1,3)
     Debug "-----------------------------------------------------------------"
    Wend 
    
   Else
    
    Debug GetSQLMessages(1)
    
   EndIf
   
   Debug "*******"
 
   If GetDatabaseDBNames(1)
    
    While NextDatabaseRow(1)
     
     Debug "Database: " + GetDatabaseString(1,0)
     Debug "Table:" + GetDatabaseString(1,2)
     Debug "Type: " + GetDatabaseString(1,3)
     Debug "-----------------------------------------------------------------"
     
    Wend 
    
   Else
    
    Debug GetSQLMessages(1)
    
   EndIf

Code: Select all

res.w=SQLTables_(SQL_HANDLE_STMT(Database),0,0,0,0,0,0,0,0)

Res.w return -2