Jump to content

Array to SQL Insert Command


Recommended Posts

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.

 

#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.

Link to post
Share on other sites

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 by SOLVE-SMART

Stay innovative!

Spoiler

🌍 Au3Forums

📊 AutoIt limits/defaults

💎 Code Katas: [...] (comming soon)

🎭 Collection of GitHub users with AutoIt projects

🐞 False-Positives

🔍 Forum search

🔮 Me on GitHub

💬 Opinion about new forum sub category

 📑 UDF wiki list

✂ VSCode-AutoItSnippets

📑 WebDriver FAQs

👨‍🏫 WebDriver Tutorial (coming soon)

Link to post
Share on other sites

@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', , );

 

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to post
Share on other sites
Posted (edited)
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 by n3wbie
Forget mention once again
Link to post
Share on other sites

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.

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to post
Share on other sites
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

All my code provided is Public Domain... but it may not work. ;) Use it, change it, break it, whatever you want.

Spoiler

My Humble Contributions:
Personal Function Documentation - A personal HelpFile for your functions
Acro.au3 UDF - Automating Acrobat Pro
ToDo Finder - Find #ToDo: lines in your scripts
UI-SimpleWrappers UDF - Use UI Automation more Simply-er
KeePass UDF - Automate KeePass, a password manager

Link to post
Share on other sites
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.

Link to post
Share on other sites
; #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

Link to post
Share on other sites
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.

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to post
Share on other sites
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

Link to post
Share on other sites

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

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...