Author Topic: Sql Server & Masm32  (Read 8219 times)

mabdelouahab

  • Member
  • ***
  • Posts: 476
Sql Server & Masm32
« on: April 22, 2016, 01:31:31 AM »
In this topic I will try to put up a way to deal with SQL Server using MASM32, step by step I'll try making the procedures and functions in order to facilitate the work
And just for clarification, I use Microsoft® SQL Server® 2012 Express, and Microsoft .NET Framework 4RadASM IDE, on Windows 8.1 x64, means that the code is not tested on another system.

To deal with databases we need System.Data library, and to get them we can use the tool Masm32Ref
Open the tool and then choose :"List Registred .Net Assembly", Then choose :"System.Data, Version=x.x.x.x" , Then choose then output folder and OK.
Go back to the IDE, open a new project name SqlExp.
so
First we must make sure the work of the .Net using the function __InitCLR
Quote
__UNICODE__ equ 1
   
   include System.Data.INC
   include Masm32RefMacro.INC
   include masm32rt.inc

   .code
start:
   .if rv(__InitCLR)
      ;
   .else
      invoke crt_wprintf,BSTR$(13,10," unable to Initialise CLR")
   .endif

   inkey
   exit
end start


Note the necessary files should be loaded :

   Masm32Ref.INC to "\Masm32\include"
   Masm32Ref.lib to "\Masm32\lib"
   Masm32RefMacro.INC to "\Masm32\include"

mabdelouahab

  • Member
  • ***
  • Posts: 476
Sql Server & Masm32 - ExistSqlDataBase
« Reply #1 on: April 22, 2016, 01:43:11 AM »
Check if exist a database
Method:
    ExistSqlDataBase 
Arg:
    dbName : the name of database
Return :
    eax == TRUE if database exist
    eax == FALSE if database not exist
    eax == 2 if Eror

Quote
   ExistSqlDataBase PROC   dbName
      LOCAL l_SqlConnection
      LOCAL l_SqlCommand
      LOCAL l_SqlDataReader
      LOCAL l_Bool
      LOCAL create_Str   [255]:word
      LOCAL create_StrB
      __New_Null l_SqlConnection
      __New_Null l_SqlCommand
      __New_Null l_SqlDataReader
      
      New_System_Data_SqlClient_SqlConnection_2               l_SqlConnection
      .if eax
         System_Data_SqlClient_SqlConnection_CreateCommand          l_SqlConnection,l_SqlCommand
         System_Data_SqlClient_SqlConnection_ConnectionString_Set   l_SqlConnection,__String(" Data Source=.\SQLEXPRESS;Integrated Security=True;")
         System_Data_SqlClient_SqlConnection_Open               l_SqlConnection
         .if eax
            invoke RtlZeroMemory ,addr create_Str,255*2
            invoke lstrcat,addr create_Str,BSTR$(" IF EXISTS",28h,"SELECT * FROM sys.sysdatabases where name= ",27h)
            invoke lstrcat,addr create_Str,dbName
            invoke lstrcat,addr create_Str,BSTR$(27h,29h,13,10," SELECT ",27h,"OK",27h,"; ")
            invoke SysAllocString,addr create_Str
            mov create_StrB,eax
            
            System_Data_SqlClient_SqlCommand_CommandText_Set         l_SqlCommand,__String( create_StrB)
            
            __New_Null l_Bool
      
            System_Data_SqlClient_SqlCommand_ExecuteReader            l_SqlCommand,l_SqlDataReader
            System_Data_SqlClient_SqlDataReader_Read               l_SqlDataReader,l_Bool
            
            System_Data_SqlClient_SqlDataReader_HasRows_Get            l_SqlDataReader,l_Bool
            
            System_Data_SqlClient_SqlConnection_Close               l_SqlConnection
            invoke SysFreeString,create_StrB
            
            mov ecx,l_Bool
            movzx eax,[ecx].VARIANT.boolVal
         .else
            invoke crt_wprintf,BSTR$(13,10," unable to Open SqlConnection")
            mov eax ,2
         .endif
      .else
         invoke crt_wprintf,BSTR$(13,10," unable to create new SqlConnection")
         mov eax ,2
      .endif
      
      ret
   ExistSqlDataBase endp

« Last Edit: April 22, 2016, 06:46:51 AM by mabdelouahab »

mabdelouahab

  • Member
  • ***
  • Posts: 476
Sql Server & Masm32 - CreateSQLDataBase
« Reply #2 on: April 22, 2016, 02:16:50 AM »
Create new database
Method:
    CreateSQLDataBase
Arg:
    dbName : the name of database
Return:
    eax==True : if the database created
    eax==False :error
Quote
   CreateSQLDataBase   PROC   dbName
      LOCAL l_SqlConnection
      LOCAL l_SqlCommand
      LOCAL create_Str   [255]:word
      LOCAL create_StrB
      __New_Null l_SqlConnection
      __New_Null l_SqlCommand
      
      New_System_Data_SqlClient_SqlConnection_2               l_SqlConnection
      .if eax
         System_Data_SqlClient_SqlConnection_CreateCommand          l_SqlConnection,l_SqlCommand
         
         System_Data_SqlClient_SqlConnection_ConnectionString_Set   l_SqlConnection,__String(" Data Source=.\SQLEXPRESS;Integrated Security=True;")
         System_Data_SqlClient_SqlConnection_Open               l_SqlConnection
         .if eax
            invoke RtlZeroMemory ,addr create_Str,255*2
            invoke lstrcat,addr create_Str,BSTR$(" CREATE DATABASE ")
            invoke lstrcat,addr create_Str,dbName
            invoke SysAllocString,addr create_Str
            mov create_StrB,eax
            System_Data_SqlClient_SqlCommand_CommandText_Set         l_SqlCommand,__String( create_StrB)
            
            System_Data_SqlClient_SqlCommand_ExecuteNonQuery         l_SqlCommand,0
            push eax
            invoke SysFreeString,create_StrB
            System_Data_SqlClient_SqlConnection_Close               l_SqlConnection         
            pop eax
         .endif
      .endif
      ret
   CreateSQLDataBase endp


Example:

Quote
   __UNICODE__ equ 1
   
   include System.Data.INC
   include Masm32RefMacro.INC
   include masm32rt.inc
   
   include SqlExp.Inc
   
   .data

   .code

start:
   .if rv(__InitCLR)
      .if _rv( ExistSqlDataBase,BSTR$("DBTest")) != 2
         .if eax   
            invoke crt_wprintf,BSTR$(13,10," Database exist",13,10)
         .else
            invoke crt_wprintf,BSTR$(13,10," Database not Found",13,10)
            invoke CreateSQLDataBase,BSTR$("DBTest")
         .endif
      .endif
   .else
      invoke crt_wprintf,BSTR$(13,10," unable to Initialise CLR",13,10)
   .endif
   inkey
   exit
end start

« Last Edit: April 22, 2016, 07:04:36 AM by mabdelouahab »

mabdelouahab

  • Member
  • ***
  • Posts: 476
Sql Server & Masm32 - Drop DataBase
« Reply #3 on: April 22, 2016, 07:03:07 AM »
Drop DataBase
Method:
    DropSQLDataBase
Arg:
    dbName : the name of database
Return:
    eax==True : if OK
    eax==False :error

Quote
      DropSQLDataBase   PROC   dbName
      LOCAL l_SqlConnection
      LOCAL l_SqlCommand
      LOCAL create_Str   [255]:word
      LOCAL create_StrB
      __New_Null l_SqlConnection
      __New_Null l_SqlCommand
      
      New_System_Data_SqlClient_SqlConnection_2               l_SqlConnection
      .if eax
         System_Data_SqlClient_SqlConnection_CreateCommand          l_SqlConnection,l_SqlCommand
         
         System_Data_SqlClient_SqlConnection_ConnectionString_Set   l_SqlConnection,__String(" Data Source=.\SQLEXPRESS;Integrated Security=True;")
         System_Data_SqlClient_SqlConnection_Open               l_SqlConnection
         .if eax
            invoke RtlZeroMemory ,addr create_Str,255*2
            invoke lstrcat,addr create_Str,BSTR$("  Drop DATABASE  ")
            invoke lstrcat,addr create_Str,dbName
            invoke SysAllocString,addr create_Str
            mov create_StrB,eax
            System_Data_SqlClient_SqlCommand_CommandText_Set         l_SqlCommand,__String( create_StrB)
            
            System_Data_SqlClient_SqlCommand_ExecuteNonQuery         l_SqlCommand,0
            push eax
            invoke SysFreeString,create_StrB
            System_Data_SqlClient_SqlConnection_Close               l_SqlConnection
            pop eax
         .endif
      .endif
      ret
   DropSQLDataBase endp

mabdelouahab

  • Member
  • ***
  • Posts: 476
Re: Sql Server & Masm32
« Reply #4 on: April 25, 2016, 02:37:55 AM »
Create Table
Method:
    CreateSQLTable
Arg:
    dbName: the name of database
    tableName: the name of table
    Primary_Key: True :create primary key identity,False: nothing
Return:
    eax==True : if OK
    eax==False :error
Quote

   CreateSQLTable   PROC   dbName,tableName,Primary_Key
      LOCAL l_SqlConnection
      LOCAL l_SqlCommand
      LOCAL create_Str   [512]:word
      LOCAL create_StrB
      __New_Null l_SqlConnection
      __New_Null l_SqlCommand
      
      New_System_Data_SqlClient_SqlConnection_2               l_SqlConnection
      .if eax
         System_Data_SqlClient_SqlConnection_CreateCommand          l_SqlConnection,l_SqlCommand
         invoke RtlZeroMemory ,addr create_Str,255*2
         invoke lstrcat,addr create_Str,BSTR$(" Data Source=.\SQLEXPRESS; Initial Catalog=")
         invoke lstrcat,addr create_Str,dbName
         invoke lstrcat,addr create_Str,BSTR$("; Integrated Security=True;")
         invoke SysAllocString,addr create_Str      
         mov create_StrB,eax
         System_Data_SqlClient_SqlConnection_ConnectionString_Set   l_SqlConnection,__String(create_StrB)
         invoke SysFreeString,create_StrB
         System_Data_SqlClient_SqlConnection_Open               l_SqlConnection
         .if eax
            invoke RtlZeroMemory ,addr create_Str,512*2
            invoke lstrcat,addr create_Str,BSTR$(" CREATE TABLE ")
            invoke lstrcat,addr create_Str,tableName
            .if Primary_Key
               invoke lstrcat,addr create_Str,BSTR$(" ",28h,"Id  INT PRIMARY KEY IDENTITY",29h)
            .endif
            invoke SysAllocString,addr create_Str
            mov create_StrB,eax
      
            System_Data_SqlClient_SqlCommand_CommandText_Set         l_SqlCommand,__String(create_StrB)      
            System_Data_SqlClient_SqlCommand_ExecuteNonQuery         l_SqlCommand,0
            push eax
            invoke SysFreeString,create_StrB
            System_Data_SqlClient_SqlConnection_Close               l_SqlConnection         
            pop eax            
         .endif
      .endif
      ret
   CreateSQLTable endp
   

Example:
Quote
            
    __UNICODE__ equ 1
    include System.Data.INC
    include Masm32RefMacro.INC
    include masm32rt.inc
    include SqlExp.Inc
   
    .code
start:
    .if rv(__InitCLR)
        .if _rv( ExistSqlDataBase,chr$("Masm32_DataBase")) != 2
            .if eax   
                invoke crt_wprintf,cfm$("\n Database exist \n")
            .else
                .if _rv(CreateSQLDataBase,chr$("Masm32_DataBase"))
                    invoke CreateSQLTable,chr$("Masm32_DataBase"),chr$("Masm32_Table1")    ,TRUE               
                    invoke CreateSQLTable,chr$("Masm32_DataBase"),chr$("Masm32_Table2")    ,TRUE               
                    invoke CreateSQLTable,chr$("Masm32_DataBase"),chr$("Masm32_Table3")    ,TRUE               
                    invoke crt_wprintf,cfm$("\n Operation Complet \n")
                .else
                    invoke crt_wprintf,cfm$("\n unable to create new database \n")
                .endif
            .endif
        .endif
    .else
        invoke crt_wprintf,cfm$("\n unable to Initialise CLR \n")
    .endif
    inkey
    exit
end start


Zen

  • Member
  • ****
  • Posts: 962
  • slightly red-shifted
Re: Sql Server & Masm32
« Reply #5 on: April 29, 2016, 05:47:34 AM »
MABDELOUAHAB,
This is a terrific concept,...I have never learned how to program SQL in any variation. I will study this intensely,...thanks for posting all the example code.
Zen

qWord

  • Member
  • *****
  • Posts: 1476
  • The base type of a type is the type itself
    • SmplMath macros
Re: Sql Server & Masm32
« Reply #6 on: April 29, 2016, 06:08:09 AM »
next step: hibernate + ObjAsm32   :biggrin:
MREAL macros - when you need floating point arithmetic while assembling!

mabdelouahab

  • Member
  • ***
  • Posts: 476
Re: Sql Server & Masm32
« Reply #7 on: April 29, 2016, 06:46:56 AM »
Hi Zen,qWord, I thank you for reply

next step: hibernate + ObjAsm32   :biggrin:
There are a lot  :biggrin:, I expected that no one  cares for the topic

mabdelouahab

  • Member
  • ***
  • Posts: 476
Re: Sql Server & Masm32
« Reply #8 on: April 29, 2016, 07:52:28 AM »
Add Column To Table
Method:
    AddColumnToSQLTable
Arg:
    dbName: the name of database
    tableName: the name of table
    columnName:  the name of table
    SqlDbTypeStr: Data Type [,length][,Default Value]

              Data Type:
                 BigInt, Binary, Bit, Char, DateTime, Decimal, Float, Image, Int, Money,
                 NChar, NText, NVarChar, Real, UniqueIdentifier, SmallDateTime, SmallInt, SmallMoney, Text, Timestamp,
                 TinyInt, VarBinary, VarChar, Variant, Xml, Udt, Structured, Date, Time, DateTime2, DateTimeOffset


Return:
    eax==True : if OK
    eax==False :error
Quote

    AddColumnToSQLTable    PROC    dbName,tableName,columnName,__SqlDbTypeStr
        LOCAL l_SqlConnection
        LOCAL l_SqlCommand
        LOCAL create_Str    [512]:word
        LOCAL create_StrB
        __New_Null l_SqlConnection
        __New_Null l_SqlCommand
       
        New_System_Data_SqlClient_SqlConnection_2                    l_SqlConnection
        .if eax
            System_Data_SqlClient_SqlConnection_CreateCommand             l_SqlConnection,l_SqlCommand
            invoke RtlZeroMemory ,addr create_Str,255*2
            invoke lstrcat,addr create_Str,BSTR$(" Data Source=.\SQLEXPRESS; Initial Catalog=")
            invoke lstrcat,addr create_Str,dbName
            invoke lstrcat,addr create_Str,BSTR$("; Integrated Security=True;")
            invoke SysAllocString,addr create_Str       
            mov create_StrB,eax
            System_Data_SqlClient_SqlConnection_ConnectionString_Set    l_SqlConnection,__String(create_StrB)
            invoke SysFreeString,create_StrB
            System_Data_SqlClient_SqlConnection_Open                    l_SqlConnection
            .if eax
                invoke RtlZeroMemory ,addr create_Str,512*2
                invoke lstrcat,addr create_Str,BSTR$(" ALTER TABLE ")
                invoke lstrcat,addr create_Str,tableName
                invoke lstrcat,addr create_Str,BSTR$(" ADD ")
                invoke lstrcat,addr create_Str,columnName
                invoke lstrcat,addr create_Str,BSTR$(" ")
                invoke lstrcat,addr create_Str,__SqlDbTypeStr
                invoke SysAllocString,addr create_Str
                mov create_StrB,eax
       
                System_Data_SqlClient_SqlCommand_CommandText_Set            l_SqlCommand,__String(create_StrB)       
                System_Data_SqlClient_SqlCommand_ExecuteNonQuery            l_SqlCommand,0
                push eax
                invoke SysFreeString,create_StrB
                System_Data_SqlClient_SqlConnection_Close                    l_SqlConnection
                pop eax                           
            .endif
        .endif
        ret
    AddColumnToSQLTable endp
Examples:
Quote

    invoke AddColumnToSQLTable ,chr$("Masm32_DataBase"),            
                                chr$("Masm32_Table2"),
                                chr$("Masm32_Colomn1"),
                                chr$(" Int ")               
    invoke AddColumnToSQLTable ,chr$("Masm32_DataBase"),
                                chr$("Masm32_Table2"),
                                chr$("Masm32_Colomn2"),
                                chr$(" NVarChar(20) Not Null DEFAULT 'Abc' ")               
    invoke AddColumnToSQLTable ,chr$("Masm32_DataBase"),
                                chr$("Masm32_Table2"),
                                chr$("Masm32_Colomn3"),
                                chr$(" Int Not Null DEFAULT 19 ")               



mabdelouahab

  • Member
  • ***
  • Posts: 476
Re: Sql Server & Masm32
« Reply #9 on: May 07, 2016, 03:01:31 AM »
The second part: Use a database
I. Execute Non Query
UPDATE, INSERT, DELETE

I think we will need many Methods  :biggrin:
Methods
    -------------------------------------------------------------------------------------------------------------------------------------
CreateSqlConnection:
     Return:
        eax== pointer to System.Data.SqlClient.SqlConnection Object (PTR to VARIANT )
        eax==False :error
    -------------------------------------------------------------------------------------------------------------------------------------
OpenSqlConnection:
     Arg
        pSqlConnection: System.Data.SqlClient.SqlConnection Object (from CreateSqlConnection)
        pConnectionString: Connection String (ConnectionString Property  /  SQL Server 2012 connection strings)
     Return
        eax==True : if OK
        eax==False :error
    -------------------------------------------------------------------------------------------------------------------------------------
CreateSqlCommand:
     Arg
        pSqlConnection: System.Data.SqlClient.SqlConnection Object (from CreateSqlConnection)
     Return
        eax==pointer to System.Data.SqlClient.SqlCommand Object (PTR to VARIANT )
        eax==False :error
    -------------------------------------------------------------------------------------------------------------------------------------
SetCommandText:
     Arg
        pSqlCommand: System.Data.SqlClient.SqlCommand Object (from CreateSqlCommand)
        queryString: String=Transact-SQL statement (Transact-SQL Reference  /  TSQL Tutorial)
     Return
        eax==True :OK
        eax==False :error
    -------------------------------------------------------------------------------------------------------------------------------------
ExecuteNonQuery: Executes a Transact-SQL statement
     Arg
        pSqlCommand: System.Data.SqlClient.SqlCommand Object (from CreateSqlCommand)
     Return
        eax== The number of rows affected.
        eax==False :error

Quote

;-------------------------------------------------------------------------------------------------------------------------------------
   CreateSqlConnection Proc   
      LOCAL lSqlConnection
      mov lSqlConnection,_rv(HeapAlloc,_rv(GetProcessHeap),0,16)
      New_System_Data_SqlClient_SqlConnection_2               lSqlConnection   
      .if eax
         mov eax,lSqlConnection
      .endif
      ret
   CreateSqlConnection endp
;-------------------------------------------------------------------------------------------------------------------------------------
   OpenSqlConnection Proc   pSqlConnection,pConnectionString
      LOCAL LString

      mov LString,_rv(SysAllocString,pConnectionString)
      System_Data_SqlClient_SqlConnection_ConnectionString_Set   pSqlConnection,__String(LString)
      invoke SysFreeString,LString
      System_Data_SqlClient_SqlConnection_Open               pSqlConnection
      ret
   OpenSqlConnection endp
;-------------------------------------------------------------------------------------------------------------------------------------
   CreateSqlCommand Proc pSqlConnection
      LOCAL lSqlCommand
      mov lSqlCommand,_rv(HeapAlloc,_rv(GetProcessHeap),0,16)
      
      System_Data_SqlClient_SqlConnection_CreateCommand          pSqlConnection,lSqlCommand
      .if eax
         mov eax,lSqlCommand
      .endif
      ret
   CreateSqlCommand endp
;-------------------------------------------------------------------------------------------------------------------------------------
   SetCommandText Proc   pSqlCommand,queryString
      LOCAL LString
      mov LString,_rv(SysAllocString,queryString)
      System_Data_SqlClient_SqlCommand_CommandText_Set         pSqlCommand,__String(LString)      
      PUSH EAX
      invoke SysFreeString,LString   
      POP EAX         
      ret
   SetCommandText endp
;-------------------------------------------------------------------------------------------------------------------------------------
   ExecuteNonQuery proc pSqlCommand
      LOCAL nbrows
      __New_Null nbrows
      System_Data_SqlClient_SqlCommand_ExecuteNonQuery         pSqlCommand,nbrows
      .if eax
         mov eax,nbrows
         mov eax,[eax].VARIANT.lVal
      .endif
      ret
   ExecuteNonQuery endp

Example:

Quote
        .data
            _SqlConnection    dd    0
            _SqlCommand    dd    0
            __ConnectionString   dd   BSTR$(" Data Source=.\SQLEXPRESS; Integrated Security=True; Initial Catalog=Masm32_DataBase")
        .code
            mov _SqlConnection,_rv( CreateSqlConnection)
            .if eax
                .if _rv(OpenSqlConnection,_SqlConnection,__ConnectionString)
                    mov _SqlCommand,_rv(CreateSqlCommand,_SqlConnection)
                    invoke SetCommandText,_SqlCommand,chr$(" INSERT INTO Masm32_Table2 (Masm32_Colomn1)VALUES (1889); ")
                    invoke ExecuteNonQuery,_SqlCommand
                    invoke crt_wprintf,cfm$("\n The number of rows affected=%d \n"),eax
                    invoke SetCommandText,_SqlCommand,chr$(" INSERT INTO Masm32_Table2 (Masm32_Colomn2)VALUES ('Example A');",13,10," INSERT INTO Masm32_Table2 (Masm32_Colomn1)VALUES (1889);")
                    invoke ExecuteNonQuery,_SqlCommand
                    invoke crt_wprintf,cfm$("\n The number of rows affected=%d \n"),eax
                .endif
            .endif

Quote
  The number of rows affected=1

  The number of rows affected=2

mabdelouahab

  • Member
  • ***
  • Posts: 476
Re: Sql Server & Masm32
« Reply #10 on: May 12, 2016, 07:06:47 AM »
Add SQL Parameters
Methods
    -------------------------------------------------------------------------------------------------------------------------------------
For SqlDbType: BigInt:
    AddLongParameter:
         Arg: 
            pSqlCommand: System.Data.SqlClient.SqlCommand Object (from CreateSqlCommand method)
            _parameterName : ptr to parameter Name
            _parameterValue : the Value DWORD
    -------------------------------------------------------------------------------------------------------------------------------------
For SqlDbType: Bit:
    AddBoolParameter:
         Arg: 
            pSqlCommand: System.Data.SqlClient.SqlCommand Object (from CreateSqlCommand method)
            _parameterName : ptr to parameter Name
            _parameterValue : TRUE or FALSE
    -------------------------------------------------------------------------------------------------------------------------------------
For SqlDbType: Char, Text, VarChar, NChar, NText, NVarChar, Xml:
    AddStringParameter:
     Arg: 
        pSqlCommand: System.Data.SqlClient.SqlCommand Object (from CreateSqlCommand method)
        _parameterName : ptr to parameter Name
        _parameterValue :  ptr to text value
 
Quote
   AddParameter PROC pSqlCommand,_parameterName,_parameterValue
      LOCAL l_Parameter , LString , VString
      LOCAL VarValue:VARIANT
      
      mov LString,_rv(SysAllocString,_parameterName)

      __New_Null l_Parameter
      System_Data_SqlClient_SqlCommand_Parameters_Get pSqlCommand,l_Parameter
      System_Data_SqlClient_SqlParameterCollection_AddWithValue l_Parameter,__String(LString),_parameterValue,0      
      
      invoke SysFreeString,LString
      ret

   AddParameter endp
;----------------------------------------------------------------------------
    ; long: SqlDbType.BigInt:
   AddLongParameter PROC pSqlCommand,_parameterName,_parameterValue
      LOCAL VarValue:VARIANT
      mov    VarValue.vt,VT_I4
      mov    eax,_parameterValue
      mov    VarValue.lVal,eax
      invoke AddParameter,pSqlCommand,_parameterName,addr VarValue
      ret
   AddLongParameter endp
;----------------------------------------------------------------------------
    ; bool: SqlDbType.Bit:
   AddBoolParameter PROC pSqlCommand,_parameterName,_parameterValue:word
      LOCAL VarValue:VARIANT   
      mov    VarValue.vt,VT_BOOL
      mov    ax,_parameterValue
      mov    VarValue.boolVal,ax
      invoke AddParameter,pSqlCommand,_parameterName,addr VarValue
      ret
   AddBoolParameter endp
;----------------------------------------------------------------------------
   ;  string: SqlDbType.Char,SqlDbType.Text,SqlDbType.VarChar,SqlDbType.NChar,SqlDbType.NText,SqlDbType.NVarChar,SqlDbType.Xml
   AddStringParameter PROC pSqlCommand,_parameterName,_parameterValue
      LOCAL VString
      LOCAL VarValue:VARIANT
      
      mov VString,_rv(SysAllocString,_parameterValue)

      mov    VarValue.vt,VT_BSTR
      mov    eax,VString
      mov    VarValue.bstrVal,eax

      invoke AddParameter,pSqlCommand,_parameterName,addr VarValue
      invoke SysFreeString,VString
      ret

   AddStringParameter endp

Example :

Quote

               invoke AddColumnToSQLTable ,chr$("MyDataBase"),
                                    chr$("MyTable"),
                                    chr$("ClmBigInt"),
                                    chr$(" BigInt ")            
               invoke AddColumnToSQLTable ,chr$("MyDataBase"),
                                    chr$("MyTable"),
                                    chr$("ClmBit"),
                                    chr$(" Bit ")            
               invoke AddColumnToSQLTable ,chr$("MyDataBase"),
                                    chr$("MyTable"),
                                    chr$("ClmNText"),
                                    chr$(" NText ")            
               invoke AddColumnToSQLTable ,chr$("MyDataBase"),
                                    chr$("MyTable"),
                                    chr$("ClmNVarChar"),
                                    chr$(" NVarChar(30) ")   
....
            invoke SetCommandText,_SqlCommand,chr$(" INSERT INTO MyTable (ClmBigInt,ClmBit,ClmNVarChar,ClmNText)VALUES (@v01,@v02,@v03,@v04); ")

            invoke AddLongParameter,_SqlCommand,chr$("@v01"),1477
            invoke AddBoolParameter,_SqlCommand,chr$("@v02"),TRUE
            invoke AddStringParameter,_SqlCommand,chr$("@v03"),chr$("strint example ")
            invoke AddStringParameter,_SqlCommand,chr$("@v04"),chr$("masm32 forum")

            invoke ExecuteNonQuery,_SqlCommand
         

Zen

  • Member
  • ****
  • Posts: 962
  • slightly red-shifted
Re: Sql Server & Masm32
« Reply #11 on: May 12, 2016, 07:56:49 AM »
Hi, MABDELOUAHAB,
Sorry, I've had other stuff to do lately, and didn't get to your SQL Project. But, I visited Sourceforge: Masm32Ref, Brought to You By: MABDELOUAHAB.
I didn't realize that you had posted a thread about: Masm32Ref, January 14, 2016. I was totally unaware that this program even existed.
THIS IS EXTREMELY IMPRESSIVE !!!
...I'm afraid to ask you how much sleep you lost coding this tool,...:bgrin:
Zen

jj2007

  • Member
  • *****
  • Posts: 10668
  • Assembler is fun ;-)
    • MasmBasic
Re: Sql Server & Masm32
« Reply #12 on: May 12, 2016, 04:27:08 PM »
THIS IS EXTREMELY IMPRESSIVE !!!

I fully agree :t

mabdelouahab

  • Member
  • ***
  • Posts: 476
Re: Sql Server & Masm32
« Reply #13 on: May 12, 2016, 06:27:13 PM »
thank you jj2007, Zen
...I'm afraid to ask you how much sleep you lost coding this tool,...:bgrin:
two years  :biggrin:
I do not forget that your topic Really Bizarre Question has helped me, and so the topic deleted by MSDN .NET Type Internals, but there is a copy in .NET Framework Internals to See How the CLR Creates Runtime Objects

mabdelouahab

  • Member
  • ***
  • Posts: 476
Re: Sql Server & Masm32
« Reply #14 on: May 12, 2016, 10:35:01 PM »
 
Add SQL Parameters
Methods
    -------------------------------------------------------------------------------------------------------------------------------------
For SqlDbType: Real:
    AddFloatParameter:
         Arg: 
            pSqlCommand: System.Data.SqlClient.SqlCommand Object (from CreateSqlCommand method)
            _parameterName : ptr to parameter Name
            _parameterValue : the Value REAL4
    -------------------------------------------------------------------------------------------------------------------------------------
For SqlDbType: Float:
    AddDoubleParameter:
         Arg: 
            pSqlCommand: System.Data.SqlClient.SqlCommand Object (from CreateSqlCommand method)
            _parameterName : ptr to parameter Name
            _parameterValue :  the Value REAL8
    -------------------------------------------------------------------------------------------------------------------------------------
For SqlDbType: Int:
    AddIntParameter:
     Arg: 
        pSqlCommand: System.Data.SqlClient.SqlCommand Object (from CreateSqlCommand method)
        _parameterName : ptr to parameter Name
        _parameterValue : dword

    -------------------------------------------------------------------------------------------------------------------------------------
For SqlDbType: SmallInt:
    AddShortParameter:
     Arg: 
        pSqlCommand: System.Data.SqlClient.SqlCommand Object (from CreateSqlCommand method)
        _parameterName : ptr to parameter Name
        _parameterValue : word value

 
Quote
   
   ;float: SqlDbType.Real
   AddFloatParameter PROC pSqlCommand,_parameterName,_parameterValue:REAL4
      LOCAL VarValue:VARIANT
      mov    VarValue.vt,VT_R4
      lea    ecx,VarValue.fltVal
      fld    _parameterValue
      fstp   REAL4 PTR [ecx]
      invoke AddParameter,pSqlCommand,_parameterName,addr VarValue
      ret
   AddFloatParameter endp
;----------------------------------------------------------------------------
    ;double: SqlDbType.Float:
   AddDoubleParameter PROC pSqlCommand,_parameterName,_parameterValue:REAL8
      LOCAL VarValue:VARIANT

      mov    VarValue.vt,VT_R8
      lea    ecx,VarValue.dblVal
      fld    _parameterValue
      fstp   REAL8 PTR [ecx]

      invoke AddParameter,pSqlCommand,_parameterName,addr VarValue
      ret

   AddDoubleParameter endp
;----------------------------------------------------------------------------
    ;int: sqlDbType.Int:
   AddIntParameter PROC pSqlCommand,_parameterName,_parameterValue
      LOCAL VarValue:VARIANT
      mov    VarValue.vt,VT_INT
      mov    eax,_parameterValue
      mov    VarValue.lVal,eax
      invoke AddParameter,pSqlCommand,_parameterName,addr VarValue
      ret
   AddIntParameter endp
;----------------------------------------------------------------------------
    ;short; SqlDbType.SmallInt:
   AddShortParameter PROC pSqlCommand,_parameterName,_parameterValue:word
      LOCAL VarValue:VARIANT
      mov    VarValue.vt,VT_I2
      mov    ax,_parameterValue
      mov    VarValue.iVal,ax
      invoke AddParameter,pSqlCommand,_parameterName,addr VarValue
      ret
   AddShortParameter endp



Example :

Quote

               invoke AddColumnToSQLTable ,chr$("MyDataBase"),
                                    chr$("MyTable"),
                                    chr$("ClmRealt"),
                                    chr$(" Real")            
               invoke AddColumnToSQLTable ,chr$("MyDataBase"),
                                    chr$("MyTable"),
                                    chr$("ClmFloat"),
                                    chr$(" Float ")            
               invoke AddColumnToSQLTable ,chr$("MyDataBase"),
                                    chr$("MyTable"),
                                    chr$("ClmInt"),
                                    chr$(" Int")            
               invoke AddColumnToSQLTable ,chr$("MyDataBase"),
                                    chr$("MyTable"),
                                    chr$("ClmSmallInt"),
                                    chr$(" SmallInt ")   
....
                     .data
                        prmPIr4   REAL4   3.14159265358979323846264338327
                        prmPIr8   REAL8   3.14159265358979323846264338327
                     .code

            invoke SetCommandText,_SqlCommand,chr$(" INSERT INTO MyTable (ClmReal,ClmFloat,ClmInt,ClmSmallInt)VALUES (@v01,@v02,@v03,@v04); ")

            invoke AddFloatParameter,_SqlCommand,chr$("@v01"),prmPIr4
            invoke AddDoubleParameter,_SqlCommand,chr$("@v02"),prmPIr8
            invoke AddIntParameter,_SqlCommand,chr$("@v03")1012
            invoke AddShortParameter,_SqlCommand,chr$("@v04")511

            invoke ExecuteNonQuery,_SqlCommand