Array to SQL Insert Command

[n3wbie]

Hello everyone.
I'm new to programming.
I was wondering if it was possible to directly generate SQL commands for inserting into SQLITE.
I read some of UDF and later came up with my own.
Requirement:
1. The top row of the array should be the column header.

 

expandcollapse popup

#include <array.au3>
#include <SQLite.au3>


; #FUNCTION# ====================================================================================================================
; Name ..........: _vf_ArrayToSqlStatement
; Description ...:
; Syntax ........: _vf_ArrayToSqlStatement($array, $tablename)
; Parameters ....: $array               - an 2d array with header in first row to make insert command
;                  $tablename           - Table Name of Already Created Table(Insert into <$tablename>)
; Return values .: A String Containing insert Command(Insert into )
; Author ........: Vinit
; Modified ......: 01/01/2023
; Remarks .......:
; Related .......:
; Link ..........:
; Example .......: No
; ===============================================================================================================================
Func _vf_ArrayToSqlStatement($array, $tablename)
    $statement = "INSERT INTO " & $tablename & " ("
    $header = _ArrayExtract($array, 0, 0)
    _ArrayTranspose($header)
    $headercount = UBound($header) - 1
    For $i = 0 To $headercount
        If $i <> 0 Then
            $statement &= ', '
        EndIf
        $statement &= "'" & StringReplace(StringReplace($header[$i][0], " ", "_"), '$', '') & "'"
    Next
    $statement &= ") values "

    _ArrayDelete($array, 0)
    $rows = UBound($array) - 1
    $cols = UBound($array, 2) - 1
    $cols1 = $cols + 1
    For $r = 0 To $rows
        For $c = 0 To $cols
            If $c = UBound($array, 2) Then

                $statement &= ''
            ElseIf $c <> 0 Then
                $statement &= ', '
            Else
                $statement &= '('
            EndIf
            If $array[$r][$c] = '' Or $array[$r][$c] = 'null' Or StringLen($array[$r][$c]) = 0 Then
                $statement &= "'" & "'"
            Else

                $statement &= "" & _SQLite_FastEscape($array[$r][$c]) & ""
            EndIf
        Next
        If $r <> $rows Then
            $statement &= '),'
        Else
            $statement &= ');'
        EndIf
        $statement &= @CRLF
    Next
    Return $statement
EndFunc   ;==>_vf_ArrayToSqlStatement

Please take note that the UDF is very rough and still in the development stage.
Additionally, some experienced and senior members may improve this udf still further.

[SOLVE-SMART]

Hi @n3wbie,

first of all, I like that you try to simplify things and come up with this UDF 👍 . Sure, there is potential for improvements but please provide a example call of your function first.

Depending on what your data will/should look like as input parameters, we can provide proper suggestions 🤝 .

Best regards
Sven

Edited January 7, 2023 by SOLVE-SMART

[n3wbie]

#include <Arraytosql.au3>
#include <array.au3>
#include <File.au3>
local $data
_FileReadToArray('test.csv',$data,0,',')
_ArrayDisplay($data)

$sql_String=_vf_ArrayToSqlStatement($data, 'tablenamexyz')
MsgBox(0,0,$sql_String)

@SOLVE-SMART Please Find Attached File To Run the Test

TEST.csv

Edited January 7, 2023 by n3wbie
Forgot to mention user

[jchd]

@n3wbiein SQL[ite] DDL names are best enclosed in "" or `` or [].That works for tables and columns names. So you don't have to change spaces into _ or the like.

Also beware that AutoIt arrays can contain various datatypes, which not always match SQLite datatypes.

Local $a = [ _
    ['int', 'real', 'text', 'blob', 'keyword or other'], _
    [3, .45, 'Hello', Binary('World'), Null], _
    [4, 1.2e-8, "O'Connor", Binary(17), True], _
    [6, -.7e12, '', Binary(123456789), False], _
    [7, 0., 'qwe"rty', Binary(-1), Default] _
]

ConsoleWrite(_vf_ArrayToSqlStatement($a, 'My pet table') & @LF)

yields (incorrectly) this:

INSERT INTO My pet table ('int', 'real', 'text', 'blob', 'keyword_or_other') values ('3', '0.45', 'Hello', , ''),
('4', '1.2e-08', 'O''Connor', , ''),
('6', '-700000000000', '', , ''),
('7', '', 'qwe"rty', , );

 

[n3wbie]

2 hours ago, jchd said:

@n3wbiein SQL[ite] DDL names are best enclosed in "" or `` or [].That works for tables and columns names. So you don't have to change spaces into _ or the like.

Also beware that AutoIt arrays can contain various datatypes, which not always match SQLite datatypes.

Local $a = [ _
    ['int', 'real', 'text', 'blob', 'keyword or other'], _
    [3, .45, 'Hello', Binary('World'), Null], _
    [4, 1.2e-8, "O'Connor", Binary(17), True], _
    [6, -.7e12, '', Binary(123456789), False], _
    [7, 0., 'qwe"rty', Binary(-1), Default] _
]

ConsoleWrite(_vf_ArrayToSqlStatement($a, 'My pet table') & @LF)

yields (incorrectly) this:

INSERT INTO My pet table ('int', 'real', 'text', 'blob', 'keyword_or_other') values ('3', '0.45', 'Hello', , ''),
('4', '1.2e-08', 'O''Connor', , ''),
('6', '-700000000000', '', , ''),
('7', '', 'qwe"rty', , );

 

Great Observations @jchd

_ & $ stuff : I Made it because i personally dont like Spaces in Fields. Hence Always Create Table Names With _.

Also in my case Data was being appended after Dollar Sign While Getting new Fields hence Added that line to remove those things.

About Autoit Arrays and SQLITE datatypes, I am very new to this field.It would be great if you can draw my attention or show me a direction where can i start to deal with those.

I feel that i can add check for 

$statement &= "" & _SQLite_FastEscape($array[$r][$c]) & ""

IsInt(); This Can also be handled
IsString(); This is Simple
IsArray(); WE Need to either transfer it as csv or Json Or Idk How to process this
IsBool(); For This We Can Easily add Yes ,No Or 1,0 or true false

How to Add for Binary and Default Stuff?

Edited January 7, 2023 by n3wbie
Forget mention once again

[jchd]

Familiarize yourself with the SQLite documentation at https://www.sqlite.org/index.html

SQL deals with binary with the blob datatype. Lookup _SQLite_FastEncode() in the AutoIt help.

[seadoggie01]

On 1/7/2023 at 8:15 AM, n3wbie said:

_ & $ stuff : I Made it because i personally dont like Spaces in Fields. Hence Always Create Table Names With _.

IMHO, that's not a good reason to keep things the way they are. The idea of a UDF is that it's helpful for everyone (or as many people as possible). The more restrictions you put on it, the less useful it is. I'd especially take note when you have an MVP suggesting things. However, that's all opinion and it's your UDF so it's all up to you

If I was looking at using this (I don't use SQLite too much), I'd suggest offering an option to have the column names split off in a new function parameter. Something like:

Func _vf_ArrayToSqlStatement($array, $tablename, $aColNames = Default)
    ; If column names were not supplied
    If $aColName = Default Then
        ; Split off the column names from $array, how you currently are
        ; Store column names into $aColumnNames
    EndIf
    
    ; [...] remaining code
EndFunc

[n3wbie]

1 hour ago, seadoggie01 said:

IMHO, that's not a good reason to keep things the way they are. The idea of a UDF is that it's helpful for everyone (or as many people as possible). The more restrictions you put on it, the less useful it is. I'd especially take note when you have an MVP suggesting things. However, that's all opinion and it's your UDF so it's all up to you

If I was looking at using this (I don't use SQLite too much), I'd suggest offering an option to have the column names split off in a new function parameter. Something like:

Func _vf_ArrayToSqlStatement($array, $tablename, $aColNames = Default)
    ; If column names were not supplied
    If $aColName = Default Then
        ; Split off the column names from $array, how you currently are
        ; Store column names into $aColumnNames
    EndIf
    
    ; [...] remaining code
EndFunc

With all due respect, sir, I'm taking into consideration everything @jchd mentioned.
I was merely explaining why I chose that strategy because I had never used data types before. However, in light of the suggestions made, I will undoubtedly endeavour to make it accessible to everyone by atleast attempting to understand how to manage various data types.
Thank you for your suggestions. I will also take the Default Tablename method into consideration.

[n3wbie]

expandcollapse popup

; #FUNCTION# ====================================================================================================================
; Name ..........: _vf_ArrayToSqlStatement
; Description ...:
; Syntax ........: _vf_ArrayToSqlStatement($array, $tablename)
; Parameters ....: $array               - an 2d array with header in first row to make insert command
;                  $tablename           - Table Name of Already Created Table(Insert into <$tablename>)
; Return values .: A String Containing insert Command(Insert into )
; Author ........: Vinit
; Modified ......: 01/01/2023
; Remarks .......:
; Related .......:
; Link ..........:
; Example .......: No
; ===============================================================================================================================
Func _vf_ArrayToSqlStatement($array, $tablename)
    $statement = "INSERT INTO `" & $tablename &  "` "&@CRLF&"("
    $header = _ArrayExtract($array, 0, 0)
    _ArrayTranspose($header)
    $headercount = UBound($header) - 1
    For $i = 0 To $headercount
        If $i <> 0 Then
            $statement &= ', '
        EndIf
        $statement &= "`" & ($header[$i][0]) & "`"
    Next
    $statement &= ") values " & @CRLF

    _ArrayDelete($array, 0)
    $rows = UBound($array) - 1
    $cols = UBound($array, 2) - 1
    $cols1 = $cols + 1
    For $r = 0 To $rows
        For $c = 0 To $cols
            If $c = UBound($array, 2) Then

                $statement &= ''
            ElseIf $c <> 0 Then
                $statement &= ', '
            Else
                $statement &= '('
            EndIf

            If $array[$r][$c] = Null Then
                $statement &= "'" & "'"
            Else

                If IsBool($array[$r][$c]) Then
;~                  MsgBox(0, 0, 0)
                    If $array[$r][$c] = True Then
                        $array[$r][$c] = 'True'

                    Else
                        $array[$r][$c] = 'False'
                    EndIf
                EndIf
                If IsBinary($array[$r][$c]) Then
                    $array[$r][$c] = _SQLite_FastEncode($array[$r][$c])
                EndIf
                $statement &= "" & _SQLite_FastEscape($array[$r][$c]) & ""

            EndIf
        Next
        If $r <> $rows Then
            $statement &= '),'
        Else
            $statement &= ');'
        EndIf
        $statement &= @CRLF
    Next
    Return $statement
EndFunc   ;==>_vf_ArrayToSqlStatement

Updated Code @jchd Please Check once

Also I Couldn't Figure out What to do with default Variable as Attached

also will update with column names as default thing in next update

[jchd]

8 hours ago, n3wbie said:

            If $array[$r][$c] = Null Then
                $statement &= "'" & "'"

AFAIK only Oracle will regard an empty string as SQL Null. This bogus choice is historical and way too old to change.

The correct SQL way would be to insert SQL Null. I currently have little time to dig much further.

[n3wbie]

On 1/12/2023 at 6:34 AM, jchd said:

AFAIK only Oracle will regard an empty string as SQL Null. This bogus choice is historical and way too old to change.

The correct SQL way would be to insert SQL Null. I currently have little time to dig much further.

May be this will require me to level up bit more with database and other things.

I wanted to ask one more question. 

there is character limit how much a variable can handle.

would this in anyway have effect on my udf

say a very large data is coming in array?

@jchd@seadoggie01 

Expert Opinions please.

Also if Answer is yes please  provide direction for resolution

[jchd]

For size, see Limits in AutoIt help and https://www.sqlite.org/search?s=d&q=limits for SQLite.

[Zedna]

AutoIt's limits:

https://www.autoitscript.com/autoit3/docs/appendix/LimitsDefaults.htm