romaSQL

[rynow]

romaSQL

This autoIt UDF is built on the concept of Laravel Query & doctrine.
RomaSQL provides a new, comfortable and easy to use way for SQL-queries in autoIt.
Most of the common SQL-queries are supported already and more are coming soon.
All of your support is much appreciated.

Connections
For the connection the object ADODB is used. Therefore the connection string is based on ODBC.
You can also use OLEDB connection strings or other database connections.
In order for this to work your add-ons have to be installed in the function: __4ern_SQL_Connection.
I’d be very glad if you shared your modifications with me.

Currently supported connections
-       MySQL (odbc)
-       Microsoft SQL Server (odbc)
-       SQLite (odbc)
-       Microsoft Access (odbc)

Command reference

$SQL_connect; establishing connection
$SQL_returnType; return a Array or Dictionary ('oDict') Object (Default = Array)
$SQL_setDefaultTable; Default Tablename
$SQL_setDefaultKey; Default Colmn Key (Default = id)
$SQL_debug; if True, show SQL Statment in Console

$SQL_get
$SQL_update
$SQL_delete
$SQL_insertInto
$SQL_take
$SQL_limit
$SQL_table
$SQL_select
$SQL_distinct

$SQL_where
$SQL_orWhere
$SQL_whereBetween
$SQL_whereNotBetween
$SQL_whereIn
$SQL_whereNotIn
$SQL_whereNull
$SQL_whereNotNull
$SQL_having
$SQL_orHaving
$SQL_havingBetween
$SQL_havingNotBetween
$SQL_havingIn
$SQL_havingNotIn
$SQL_havingNull
$SQL_havingNotNull
$SQL_groupBy
$SQL_orderBy

 

Examples

establishing connection

;-----/
; SQLite Connection
;-----/
$SQL_setDatabase('sqlite')
$SQL_connect('C:\project.db')

;-----/
; Access Connection
; Database, User, Password
;-----/
$SQL_setDatabase('access')
$SQL_connect('C:\project.mdb')
;or as Admin
$SQL_connect('C:\project.mdb', '4ern', 'root')

;-----/
; SQLServer Connection
; Database, User, Password, Server, Driver
;-----/
$SQL_setDatabase('sqlserver')
$SQL_connect('myDB', '4ern', 'root', 'localhost')
;or with Driver
$SQL_connect('myDB', '4ern', 'root', 'localhost', 'SQL Server')

;-----/
; MySQL Connection
; Database, User, Password, Server, Driver
;-----/
$SQL_setDatabase('mysql')
$SQL_connect('myDB', '4ern', 'root', 'localhost')
;or with Driver
$SQL_connect('myDB', '4ern', 'root', 'localhost', 'MySQL ODBC 5.2 UNICODE Driver')

 

simple SQL query

$SQL_table('albums')
$aRet = $SQL_get()

if IsArray($aRet ) then
    _ArrayDisplay($aRet )
else
    ConsoleWrite('Keine Ergebnisse' & @LF)
endif

 

Select

$SQL_table('albums')
$SQL_select('id', 'Name', 'Artist', 'Song')

;or pass to an Array
Local $aSelect = ['id', 'Name', 'Artist', 'Song']
$SQL_select($aSelect)

$aRet = $SQL_get()
if IsArray($aRet ) then
    _ArrayDisplay($aRet )
else
    ConsoleWrite('Keine Ergebnisse' & @LF)
endif

where

$SQL_table('albums')
$SQL_select('id', 'Name', 'Artist', 'Song', 'Votes')
$SQL_where('Artist', 'adele')
$SQL_where('Votes', '>=' ,'9')
$SQL_orWhere('Artist', '=' ,'Rag'n'Bone Man')

;or pass to an 2dArray
Local $aSelect = [['Artist','adele'],['Votes', '>=' ,'9']]
$SQL_where($aSelect)

$aRet = $SQL_get()
if IsArray($aRet ) then
    _ArrayDisplay($aRet )
else
    ConsoleWrite('Keine Ergebnisse' & @LF)
endif

 

If you need more examples, then tell me exactly what you need.

I hope you like my UDF and find some use for it.

---

->DONWLOAD romaSQL

 

 

[mLipok]

Interesting coding approach.

 

EDIT:
Added here:
https://www.autoitscript.com/wiki/User_Defined_Functions#Databases_and_web_connections

 

Edited March 1, 2017 by mLipok

[jchd]

I beg to strongly differ.

While this may seem simple to use for SQL newbies, I doubt you can create "serious" SQL queries this way, I mean in real-world applications. Simple queries don't need any of these glue functions and routine non-trivial queries become an horrible mess almost impossible to understand.

Further, it tends to teach newcomers an extremely limited grammar/dialect, instead of exposing the full power of SQL. In my book I call this totally counter-productive and utterly deceptive in the long run.

For instance, imagining you have all Laravel wrapped as AutoIt functions, how would the following (still simple and pretty readable) query translate?

create table T(date integer,test char);
insert into T
values (1,'clim'),(3,'clim'),(7,'amb'),(10,'amb'),(12,'xxx'),
(13,'clim'),(15,'clim'),(20,'clim'),(22,'amb'),(25,'amb');

select
    min(date) as fromdate,
    max(date) as enddate,
    test
from (--get closest preceeding different key
    select
        t.*,
        max(t2.date) as key2
    from
        t
        left join
        t t2
    on t2.date < t.date and t2.test != t.test
    group by t.date
)
group by key2

Nom think of 4 or 8 complex subqueries on 7-table joins...

Laziness never pays long term!

[rynow]

@mLipok  thx

@jchd Thank you for your feedback and forgive me my google Translater english, but I am a different opinion.

On 2.3.2017 at 10:25 AM, jchd said:

While this may seem simple to use for SQL newbies, I doubt you can create "serious" SQL queries this way, I mean in real-world applications. 

 

What are the reputable SQL query and what are simplistic? In my real applications, more than 90% of all SQL queries are rather simple.

On 2.3.2017 at 10:25 AM, jchd said:

Simple queries don't need any of these glue functions and routine non-trivial queries become an horrible mess almost impossible to understand.

I do not think so. I find the laravel prinizip much more clearly and for my SQL queries it is sufficient.

On 2.3.2017 at 10:25 AM, jchd said:

Further, it tends to teach newcomers an extremely limited grammar/dialect, instead of exposing the full power of SQL. In my book I call this totally counter-productive and utterly deceptive in the long run.

For instance, imagining you have all Laravel wrapped as AutoIt functions, how would the following (still simple and pretty readable) query translate?

I agree with you. Newcomers should first learn sql before using these udf. This is similar to Javascript frameworks. But why can not I make life easier if I can sql already.

For subquery I have so far no solution, so you would have to actually execute a $ SQL_raw ('..').

[jchd]

You're telling me that it is simpler and clearer to write something like

$Sentence.Myself.Subject()
$Sentence.Verb("to be").Present.Person(1)
$Sentence.Negation.Absolute()
$Sentence.Verb("to convince").Participle.Past()
$Sentence.FinalPoint()
ConsoleWrite(SentenceBuild($Sentence) & @LF)

rather than

ConsoleWrite("I am not convinced." & @LF)

That may fit your mood or models but I dare to insist that this is counter-productive.

A non-trivial application can be a typical simple invoicing system, for instance. There you're likely to find a table of clients, a table of delivery locations, a table of invoicing addresses, a table of orders/invoices, a table of items, a table of items ordered (some delivered and some still pending delivery), a table of paiements associated to orders and invoices, and most probably some more tables to fit the real world (suppliers, stock management, ...)

I don't buy the idea that such a common application can use 90% of flat queries.