AutoHotKey & SQL in a separate file...

[Sergio]

I use a MySQL database as the backend for a particularly large AHK software. I used to declare a query & run it in-line, but to make life easier for myself I created a separate document and just included it into my code. It's a good idea in theory, but it created nightmares in practice. The biggest nightmare is that the query is dynamic. So it's a variable which is comprised of some static data, but also of several other variables.

Example:

Code: Select all

dummySalesPlaceholderSQL = 
(
	INSERT INTO ``site_Sale`` (``mainID``, ``partialSiteID``, ``siteSalePrice``, ``siteSaleQty``, ``siteSaleDate``, ``dateCollected``) 
	VALUES('%mainID%', 'NoSalesFound','0.00','0', NOW(),NOW());
)

So, when it was compiled in-line, it was not a problem but when the variable is created upon startup and none of it's child values are yet present, it becomes useless.

Is there any way to get the variable to recreate itself when I need it, thereby discovering the newly created data? I tried these two, but no luck.

Code: Select all

insertQrySQL = %insertQrySQL%

Code: Select all

insertQrySQL := insertQrySQL

jethrow: added code tags ... because we can now

[Zelio]

I am not sure to understand, you mean insertQrySQL := %insertQrySQL% ? like a double deref in expresion as % %insertQrySQL%

[jethrow]

If you want an answer, you will prolly need to ask again & give more detail. For instance, and it may just be my ignorance, but the following statement doesn't make sence outside of your mind:

Is there any way to get the variable to recreate itself when I need it, thereby discovering the newly created data?

[just me]

You might be looking for Transform, Deref, .... You have to escape the %-signs in this case.

Or you might use an unique literal value instead of the variable and StringReplace/RegExReplace().

[Sergio]

I did not explain it properly. Actually, I do not need SQL to explain this. Lets say my code looks like this

File1:

Code: Select all

endVar := "Var 1 is: |" . var1 . "|`nVar2 is: |" . var2 . "|" 

File2:

Code: Select all

#Include, File1.ahk
var1 := "ABC"
var2 := "123"
MsgBox, % endVar

File 1 contains the final variable (endVar) but it is composed of other variables (var1 & var2) which were not yet defined. So the end result looks like this

Var 1 is: ||
Var2 is: ||

I would prefer to have all of my SQL statements on a separate file. So what I'm asking is if there is a way to make endVar reevaluate itself in some way such that it rechecks for the values of var1 or var2.


Afterwards, I realized I could just make each variable into a function and pass it the required parameters. It's a bit more work, but it allows me to have all of the SQL on a separate file. Some of those queries are a bit long and I'd rather have them organized.

[jethrow]

Afterwards, I realized I could just make each variable into a function and pass it the required parameters. It's a bit more work, but it allows me to have all of the SQL on a separate file. Some of those queries are a bit long and I'd rather have them organized.

Yep - that would be a better setup:

Code: Select all

MsgBox % dummySalesPlaceholderSQL(486)

dummySalesPlaceholderSQL(mainID) {
	p =
	( LTrim
    INSERT INTO ``site_Sale`` (``mainID``, ``partialSiteID``, ``siteSalePrice``, ``siteSaleQty``, ``siteSaleDate``, ``dateCollected``)
    VALUES('%mainID%', 'NoSalesFound','0.00','0', NOW(),NOW());
	)
	return p
}

If you want even more organization, you could store them in an object.

[just me]

Functions are better, just to show what I was talking about:

Code: Select all

endVar := "Var 1 is: |%var1%|`nVar2 is: |%var2%|"
var1 := "ABC"
var2 := "123"
Transform, outVar, Deref, %endVar%
MsgBox, % outVar

[Sergio]

Afterwards, I realized I could just make each variable into a function and pass it the required parameters. It's a bit more work, but it allows me to have all of the SQL on a separate file. Some of those queries are a bit long and I'd rather have them organized.

Yep - that would be a better setup:

Code: Select all

MsgBox % dummySalesPlaceholderSQL(486)

dummySalesPlaceholderSQL(mainID) {
	p =
	( LTrim
    INSERT INTO ``site_Sale`` (``mainID``, ``partialSiteID``, ``siteSalePrice``, ``siteSaleQty``, ``siteSaleDate``, ``dateCollected``)
    VALUES('%mainID%', 'NoSalesFound','0.00','0', NOW(),NOW());
	)
	return p
}

If you want even more organization, you could store them in an object.

Thanks! An object sounds better but it might be more complicated than my needs.

Functions are better, just to show what I was talking about:

Code: Select all

endVar := "Var 1 is: |%var1%|`nVar2 is: |%var2%|"
var1 := "ABC"
var2 := "123"
Transform, outVar, Deref, %endVar%
MsgBox, % outVar

Ahh, thanks! I read up on the Transform command, but I saw that it was used to convert between unicode & Ascii or HTML encoded tags. Useful, but I didn't understand until now.