flat assembler
Message board for the users of flat assembler.

Index > Windows > [EXAMPLE] connecting to ORACLE DB via ODBC

Author
Thread Post new topic Reply to topic
bzdashek



Joined: 15 Feb 2012
Posts: 147
Location: Tolstokvashino, Russia
bzdashek
Hello,

Sorry for the ugly conversion from double to int using FPU.

Please change connection properties before running, and if connecting to other db than oracle, be sure to fix the SQL statement.

Code:

format PE console
entry start
include 'win32a.inc'
include 'sql.inc'

section '.text' code readable executable

start:
     ;Allocating Environment handle
      invoke  SQLAllocHandle,SQL_HANDLE_ENV,SQL_NULL_HANDLE,henv
  cmp     ax,SQL_ERROR            ; !!NOTE that sizeof(SQLRETURN) = 2, that is 'dw'
 je      .fail

   ;Setting ODBC version
       invoke  SQLSetEnvAttr,[henv],SQL_ATTR_ODBC_VERSION,SQL_OV_ODBC3,0
   cmp     ax,SQL_ERROR
        je      .fail

   ;Allocating DatabaseConnection Handle
       invoke  SQLAllocHandle,SQL_HANDLE_DBC,[henv],hdbc
   cmp     ax,SQL_ERROR
        je      .fail

   ;Connection Attributes - 15 sec login timeout
       invoke  SQLSetConnectAttr,[hdbc],SQL_ATTR_LOGIN_TIMEOUT,[_login_timeout],0

      ;Connect to database via ODBC
       invoke  SQLConnect,[hdbc],_sql_server, SQL_NTS, _sql_login, SQL_NTS, _sql_password, SQL_NTS
 cmp     ax,SQL_ERROR
        je      .fail

   ;Allocating statement handle
        invoke  SQLAllocStmt,[hdbc],hstmt
   cmp     ax,SQL_ERROR
        je      .fail

   ;Executing SQL statement
    invoke  SQLExecDirect,[hstmt],_sql_stmt,SQL_NTS
     cmp     ax,SQL_SUCCESS
      jne     .fail

   ;fetching data from result
  .fetch_next:
    invoke  SQLFetch,[hstmt]
    cmp     ax,SQL_NO_DATA
      je      .end_fetch
  cmp     ax,SQL_SUCCESS
      jne     .fail

   invoke  SQLGetData,[hstmt],1,SQL_C_DOUBLE,dblcnt,0,cbcnt

        finit
       fld     qword[dblcnt]
       fist    dword[ddCnt]

    invoke  wsprintf,buf,fmt,[ddCnt]
    mov     esi,buf
     call    print
       jmp     .fetch_next
      
  .end_fetch:
  mov     esi,_fetched
        call    print

   jmp     .quit

.fail:
     stdcall dbError,[henv],[hdbc],[hstmt]
.quit:
 cmp     [hstmt],0
   jz      @f
  invoke  SQLFreeHandle,SQL_HANDLE_STMT,[henv]
        @@:
     cmp     [hdbc],0
    jz      @f
  invoke  SQLFreeHandle,SQL_HANDLE_DBC,[henv]
 @@:
     cmp     [henv],0
    jz      @f
  invoke  SQLFreeHandle,SQL_HANDLE_ENV,[henv]
 @@:

 invoke  ExitProcess,0

print:
     push    ebp eax esi edi ecx
 push    STD_OUTPUT_HANDLE
   call    [GetStdHandle]
      mov     ebp,eax
     mov     edi,esi
     or      ecx,-1
      xor     al,al
       repne   scasb
       neg     ecx
 sub     ecx,2
       push    0
   push    bytes_count
 push    ecx
 push    esi
 push    ebp
 call    [WriteFile]
 pop     ecx edi esi eax ebp
 ret


proc dbError henv:dword,hdbc:dword,hstmt:dword
   invoke  SQLError,[henv],[hdbc],[hstmt],sqlstate,0,buf,250,0
 mov     esi,buf
     call    print
       ret
endp



section '.data' data readable writable

  ;
  ; Connection properties:
  ;
  _sql_server                db 'ORCL',0           ; ODBC Name
  _sql_login             db 'imuser',0         ; Database user
  _sql_password      db 's',0              ; Database password

  _sql_stmt          db 'SELECT user_id FROM ALL_USERS WHERE rownum <=10 ORDER BY user_id',0

  _fetched          db 'End of data',13,10,0

  _login_timeout      dd 15
  fmt                  db 'User ID: %d',13,10,0

  
  bytes_count       dd ?
  ;
  ; Handles
  ;
  henv     dd ?            ;SQLHENV
  hdbc      dd ?
  hstmt dd ?

  ;
  ;Output data
  ;
  dblcnt    dq ?
  cbcnt         dd ?
  ddCnt         dd ?

  ;Buffers
  sqlstate    rb 15
  buf          rb 250

section '.idata' import data readable writeable

  library KERNEL32.DLL,'KERNEL32.DLL',\
      USER32.DLL,'USER32.DLL',\
      ODBC32.DLL,'ODBC32.DLL'

  import KERNEL32.DLL,\
      ExitProcess,'ExitProcess',\
      GetStdHandle,'GetStdHandle',\
      WriteFile,'WriteFile'

  import USER32.DLL,\
      wsprintf,'wsprintfA'

  import ODBC32.DLL,\
      SQLAllocHandle,'SQLAllocHandle',\
      SQLAllocStmt,'SQLAllocStmt',\
      SQLConnect,'SQLConnectA',\
      SQLError,'SQLErrorA',\
      SQLExecDirect,'SQLExecDirectA',\
      SQLFetch,'SQLFetch',\
      SQLFreeHandle,'SQLFreeHandle',\
      SQLGetData,'SQLGetData',\
      SQLSetConnectAttr,'SQLSetConnectAttrA',\
      SQLSetEnvAttr,'SQLSetEnvAttr'             
    


sql.inc was taken from FASMW64 by madmatt (thanks, Matt), and is in attachment.


Description: SQL.INC from FASMW64 package by madmatt
Download
Filename: sql.inc.7z
Filesize: 10.57 KB
Downloaded: 81 Time(s)



Last edited by bzdashek on 25 Apr 2012, 15:17; edited 1 time in total
Post 25 Apr 2012, 14:42
View user's profile Send private message Reply with quote
AsmGuru62



Joined: 28 Jan 2004
Posts: 1409
Location: Toronto, Canada
AsmGuru62
Awesome stuff!!
I bet it is faster than any wrappers around ODBC, like OLE DB and such.
Post 25 Apr 2012, 15:02
View user's profile Send private message Send e-mail Reply with quote
bzdashek



Joined: 15 Feb 2012
Posts: 147
Location: Tolstokvashino, Russia
bzdashek
Fixed the commentary, sizeof(SQLRETURN) is 2, not 4.

Didn't run any benchmarks yet, I'll try to fetch 1 mln records tomorrow without the screen output, but I have nothing to compare to, only SQLPlus*, which still might be faster since it uses native libraries.
Post 25 Apr 2012, 15:24
View user's profile Send private message Reply with quote
typedef



Joined: 25 Jul 2010
Posts: 2913
Location: 0x77760000
typedef
Nice. I remember someone else did mysql too.
Post 25 Apr 2012, 17:26
View user's profile Send private message Reply with quote
bzdashek



Joined: 15 Feb 2012
Posts: 147
Location: Tolstokvashino, Russia
bzdashek
typedef wrote:
Nice. I remember someone else did mysql too.

This example can be used to connect to mysql also, all is needed - to install correct MySQL driver, and specify the correct user or system DSN in _sql_server variable. Well, I bet you are aware of that.
Post 25 Apr 2012, 18:30
View user's profile Send private message Reply with quote
Enko



Joined: 03 Apr 2007
Posts: 678
Location: Mar del Plata
Enko
typedef wrote:
Nice. I remember someone else did mysql too.

I think you refer to this one
http://board.flatassembler.net/topic.php?t=13009
Post 25 Apr 2012, 20:31
View user's profile Send private message Reply with quote
bzdashek



Joined: 15 Feb 2012
Posts: 147
Location: Tolstokvashino, Russia
bzdashek
Enko wrote:
typedef wrote:
Nice. I remember someone else did mysql too.

I think you refer to this one
http://board.flatassembler.net/topic.php?t=13009

Great one!
Post 25 Apr 2012, 20:34
View user's profile Send private message Reply with quote
typedef



Joined: 25 Jul 2010
Posts: 2913
Location: 0x77760000
typedef
@Enko

Yes. Looking at it now, I see that in your error checking code after calling mysql_connet you kept on calling other mysql functions instead of exiting. lol
Post 25 Apr 2012, 20:54
View user's profile Send private message Reply with quote
Display posts from previous:
Post new topic Reply to topic

Jump to:  


< Last Thread | Next Thread >
Forum Rules:
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
You cannot vote in polls in this forum
You cannot attach files in this forum
You can download files in this forum


Copyright © 1999-2020, Tomasz Grysztar. Also on YouTube, Twitter.

Website powered by rwasa.