Jump to content

Sky Slate Blueberry Blackcurrant Watermelon Strawberry Orange Banana Apple Emerald Chocolate
Photo

[Func] ADOSQL - Uses ADO to manage SQL transactions (v5.03L)


  • Please log in to reply
16 replies to this topic
VxE
  • Moderators
  • 3622 posts
  • Last active: Dec 24 2015 02:21 AM
  • Joined: 07 Oct 2006
Here's yet another wrapper of ADO COM objects for doing SQL stuff. All it does is open a connection to a database, submit a SQL statement, parse the results, then return them.

Here's the rundown of the differences between this function and the others on the forum:

Benefits: I wrote it.

Drawbacks: I wrote it.


Special thanks to beardboy whose work provided a starting point for this project.


ZIP: Download ADOSQL-L.zip

AHK: Download ADOSQL.ahk

Or just copy this:
/*
###############################################################################################################
######                                      ADOSQL v5.04L - By [VxE]                                     ######
###############################################################################################################

	Wraps the utility of ADODB to connect to a database, submit a query, and read the resulting recordset.
	Returns the result as a new object (or array of objects, if the query has multiple statements).
	To instead have this function return a string, include a delimiter option in the connection string.

	For AHK-L (v1.1 or later).
	Freely available @ http://www.autohotkey.com/community/viewtopic.php?p=558323#p558323

	IMPORTANT! Before you can use this library, you must have access to a database AND know the connection
	string to connect to your database.

	Varieties of databases will have different connection string formats, and different drivers (providers).
	Use the mighty internet to discover the connection string format and driver for your type of database.

	Example connection string for SQLServer (2005) listening on port 1234 and with a static IP:
	DRIVER={SQL SERVER};SERVER=192.168.0.12,1234\SQLEXPRESS;DATABASE=mydb;UID=admin;PWD=12345;APP=AHK
*/

Global ADOSQL_LastError, ADOSQL_LastQuery ; These super-globals are for debugging your SQL queries.

ADOSQL( Connection_String, Query_Statement ) {
; Uses an ADODB object to connect to a database, submit a query and read the resulting recordset.
; By default, this function returns an object. If the query generates exactly one result set, the object is
; a 2-dimensional array containing that result (the first row contains the column names). Otherwise, the
; returned object is an array of all the results. To instead have this function return a string, append either
; ";RowDelim=`n" or ";ColDelim=`t" to the connection string (substitute your preferences for "`n" and "`t").
; If there is more than one table in the output string, they are separated by 3 consecutive row-delimiters.
; ErrorLevel is set to "Error" if ADODB is not available, or the COM error code if a COM error is encountered.
; Otherwise ErrorLevel is set to zero.

	coer := "", txtout := 0, rd := "`n", cd := "CSV", str := Connection_String ; 'str' is shorter.

; Examine the connection string for output formatting options.
	If ( 9 < oTbl := 9 + InStr( ";" str, ";RowDelim=" ) )
	{
		rd := SubStr( str, oTbl, 0 - oTbl + oRow := InStr( str ";", ";", 0, oTbl ) )
		str := SubStr( str, 1, oTbl - 11 ) SubStr( str, oRow )
		txtout := 1
	}
	If ( 9 < oTbl := 9 + InStr( ";" str, ";ColDelim=" ) )
	{
		cd := SubStr( str, oTbl, 0 - oTbl + oRow := InStr( str ";", ";", 0, oTbl ) )
		str := SubStr( str, 1, oTbl - 11 ) SubStr( str, oRow )
		txtout := 1
	}

	ComObjError( 0 ) ; We'll manage COM errors manually.

; Create a connection object. > http://www.w3schools.com/ado/ado_ref_connection.asp
; If something goes wrong here, return blank and set the error message.
	If !( oCon := ComObjCreate( "ADODB.Connection" ) )
		Return "", ComObjError( 1 ), ErrorLevel := "Error"
		, ADOSQL_LastError := "Fatal Error: ADODB is not available."


	oCon.ConnectionTimeout := 3 ; Allow 3 seconds to connect to the server.
	oCon.CursorLocation := 3 ; Use a client-side cursor server.
	oCon.CommandTimeout := 900 ; A generous 15 minute timeout on the actual SQL statement.
	oCon.Open( str ) ; open the connection.

; Execute the query statement and get the recordset. > http://www.w3schools.com/ado/ado_ref_recordset.asp
	If !( coer := A_LastError )
		oRec := oCon.execute( ADOSQL_LastQuery := Query_Statement )

	If !( coer := A_LastError ) ; The query executed OK, so examine the recordsets.
	{
		o3DA := [] ; This is a 3-dimensional array.
		While IsObject( oRec )
			If !oRec.State ; Recordset.State is zero if the recordset is closed, so we skip it.
				oRec := oRec.NextRecordset()
			Else ; A row-returning operation returns an open recordset
			{
				oFld := oRec.Fields
				o3DA.Insert( oTbl := [] )
				oTbl.Insert( oRow := [] )

				Loop % cols := oFld.Count ; Put the column names in the first row.
					oRow[ A_Index ] := oFld.Item( A_Index - 1 ).Name

				While !oRec.EOF ; While the record pointer is not at the end of the recordset...
				{
					oTbl.Insert( oRow := [] )
					oRow.SetCapacity( cols ) ; Might improve performance on huge tables??
					Loop % cols
						oRow[ A_Index ] := oFld.Item( A_Index - 1 ).Value	
					oRec.MoveNext() ; move the record pointer to the next row of values
				}

				oRec := oRec.NextRecordset() ; Get the next recordset.
			}

		If (txtout) ; If the user wants plaintext output, copy the results into a string
		{
			Query_Statement := "x"
			Loop % o3DA.MaxIndex()
			{
				Query_Statement .= rd rd
				oTbl := o3DA[ A_Index ]
				Loop % oTbl.MaxIndex()
				{
					oRow := oTbl[ A_Index ]
					Loop % oRow.MaxIndex()
						If ( cd = "CSV" )
						{
							str := oRow[ A_Index ]
							StringReplace, str, str, ", "", A
							If !ErrorLevel || InStr( str, "," ) || InStr( str, rd )
								str := """" str """"
							Query_Statement .= ( A_Index = 1 ? rd : "," ) str
						}
						Else
							Query_Statement .= ( A_Index = 1 ? rd : cd ) oRow[ A_Index ]
				}
			}
			Query_Statement := SubStr( Query_Statement, 2 + 3 * StrLen( rd ) )
		}
	}
	Else ; Oh NOES!! Put a description of each error in 'ADOSQL_LastError'.
	{
		oErr := oCon.Errors ; > http://www.w3schools.com/ado/ado_ref_error.asp
		Query_Statement := "x"
		Loop % oErr.Count
		{
			oFld := oErr.Item( A_Index - 1 )
			str := oFld.Description
			Query_Statement .= "`n`n" SubStr( str, 1 + InStr( str, "]", 0, 2 + InStr( str, "][", 0, 0 ) ) )
				. "`n   Number: " oFld.Number
				. ", NativeError: " oFld.NativeError
				. ", Source: " oFld.Source
				. ", SQLState: " oFld.SQLState
		}
		ADOSQL_LastError := SubStr( Query_Statement, 4 )
		Query_Statement := ""
		txtout := 1
	}

; Close the connection and return the result. Local objects are cleaned up as the function returns.
	oCon.Close()
	ComObjError( 1 )
	ErrorLevel := coer
	Return txtout ? Query_Statement : o3DA.MaxIndex() = 1 ? o3DA[1] : o3DA
} ; END - ADOSQL( Connection_String, Query_Statement )
The version for AHK-Basic can be found here.

Here's a sample showing the use of microsoft's ODBC text driver with a csv file.
SetWorkingDir, % A_ScriptDir
IfNotExist, products.csv
	FileAppend,
(
P_CODE,P_DESCRIPT,P_INDATE,P_QOH,P_MIN,P_PRICE,P_DISCOUNT,V_CODE
"11QER/31","Power painter,15 psi.,3-nozzle","03-NOV-2011",8,5,109.99,0.00,25595
"13-Q2/P2","7.25-in. pwr. saw blade","13-DEC-2011",32,15,14.99,0.05,21344
"14-Q1/L3","9.00-in. pwr. saw blade","13-NOV-2011",18,12,17.49,0.00,21344
"1546-QQ2","Hrd. cloth,1/4-in.,2x50","15-JAN-2012",15,8,39.95,0.00,23119
"1558-QW1","Hrd. cloth,1/2-in.,3x50","15-JAN-2012",23,5,43.99,0.00,23119
"2232/QTY","B\&D jigsaw,12-in. blade","30-DEC-2011",8,5,109.92,0.05,24288
"2232/QWE","B\&D jigsaw,8-in. blade","24-DEC-2011",6,5,99.87,0.05,24288
"2238/QPD","B\&D cordless drill,1/2-in.","20-JAN-2012",12,5,38.95,0.05,25595
"23109-HB","Claw hammer","20-JAN-2012",23,10,9.95,0.10,21225
"23114-AA","Sledge hammer,12 lb.","02-JAN-2012",8,5,14.40,0.05,NULL 
"54778-2T","Rat-tail file,1/8-in. fine","15-DEC-2011",43,20,4.99,0.00,21344
"89-WRE-Q","Hicut chain saw,16 in.","07-FEB-2012",11,5,256.99,0.05,24288
"PVC23DRT","PVC pipe,3.5-in.,8-ft","20-FEB-2011",188,75,5.87,0.00,NULL 
"SM-18277","1.25-in. metal screw,25","01-MAR-2012",172,75,6.99,0.00,21225
"SW-23116","2.5-in. wd. screw,50","24-FEB-2012",237,100,8.45,0.00,21231
"WR3/TT3","Steel matting,4""x8""x1/6",.5" mesh","17-JAN-2012",18,5,119.95,0.10,25595
), products.csv

connection_string =
( ltrim join;
	Driver={Microsoft Text Driver (*.txt; *.csv)}
	Extensions=asc,csv,tab,txt
	Persist Security Info=False
)

MsgBox % ADOSQL( connection_string ";coldelim=   `t", "
(
	SELECT P_CODE, P_PRICE, P_DESCRIPT
	FROM products.csv
	WHERE P_PRICE > ( SELECT AVG( P_PRICE ) FROM products.csv )
)")

filedelete, products.csv


epikkara
  • Members
  • 12 posts
  • Last active: Nov 13 2012 08:45 AM
  • Joined: 26 Oct 2012
I found a work around which I will use now. AHK script writes records to CSV text file, which is linked as external table to Access database.
I cannot edit it in Access but I can query and make reports.
Thank you

G. Sperotto
  • Members
  • 539 posts
  • Last active: Jun 20 2015 04:54 PM
  • Joined: 12 Dec 2011
Wow!

Thank you VxE.

A LOT!

With your code, i've managed to get some queries to run on Microsoft SQL Server 2008 no problem. And it was so simple i can hardly believe it.

Do i have your permission to write a tutorial for easy SQL Server connections using your code (Will credit you for the function)?

"What is a suitable automation? Whatever saves your day for the greater matters."
Barcoder - Create QR Codes and other Barcodes using only Autohotkey !!


Grendahl
  • Members
  • 416 posts
  • Last active: Jul 07 2014 08:01 PM
  • Joined: 10 Aug 2009
VxE,

Wow, this is really cool!

I have a question though...

Using your function I can get a proper query to run and can drop the output either into a file or display it in a single MsgBox.

How do I get only one column of the query to show, say in a list select box in a gui?
Perhaps parsing through an output .csv for it?

Long story short, I'm going to attempt writing a tool that will allow a user name to be selected, and that will then reset that user's password. (username and password are columns in a table for the application this tool will be for)
Always have your scripts when you need them with Dropbox.
Sign up for free! http://db.tt/9Hrieqj

VxE
  • Moderators
  • 3622 posts
  • Last active: Dec 24 2015 02:21 AM
  • Joined: 07 Oct 2006

@ G.Sperotto: yes you may write guides or tuts for using the function. It's good to hear that you've found it useful.

 

 

@ Grendahl: you have several choices, the simplest may be to have your SELECT query ask for only one column and use ";rowdelim=|" in the connection string (assuming "|" is yor listbox's delimiter). Another is to parse the output and add strings to the listbox individually.



lagomorph
  • Members
  • 403 posts
  • Last active: May 15 2014 03:41 PM
  • Joined: 02 Apr 2010

Thank you very much for this elegant function.

 

It took me many, many hours and lots of trial and error to get it to work for me. Here is what I ended up having to do, in case anyone else is trying to query an Oracle db:

 

SETUP INSTRUCTIONS
1) Install Client 32bit Windows Access Component drivers for Oracle matching your db version # (i.e. 11g or whatever)
This function wants 32bit drivers...regardless of if your OS is 64bit or not!
=========
2) Create TNSNames.ORA file in Oracle directory with these contents:
 
YourTNSServiceNameHere=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=<DB ADDRESS>)
      (PORT=<PORT NUMBER>)
    )
    (CONNECT_DATA=
      (SERVICE_NAME=<SERVICE NAME>)
    )
  )
 
Then set Environment Variable Paths for TNS_ADMIN and SQLPATH to TNSNames.ORA file's directory
Also check the PATH Env. Var. and make sure it includes path to oracle drivers bin folder that you installed in step 1
=========
 
3) Create System DSN entry in ODBC Data Source Administrator (found in windows control panel)
***ON 64 BIT SYSTEMS, SEARCH C:\WINDOWS\SysWow64 for odbcad32.exe! The default control panel ODBC data source admin GUI on a 64bit system only shows 64bit drivers and will not do us any good.
 
DSN ENTRY:
Data Source Name: <DB ADDRESS>
Description: <DESCRIPTION>
TNS Service Name: <THAT SERVICE NAME YOU PUT INTO YOUR TNSNAME FILES UP THERE the first one, not the one at the end>
User ID: <USER ID>
 
You can test the connection now and it will prompt you for the password.
============
4) In AHK, using this ADOSQL function, I used this code:
 
#INCLUDE ADOSQL.ahk

connection_String := "Driver={Oracle in OraClient11g_home132bit};Dbq=DSNTNSServiceNameFromUpThere;Uid=MyUserID;Pwd=abc123;"

MsgBox % ADOSQL( connection_string ";coldelim=   `t", "
(
	SELECT EDI_VENDOR_ID, EDI_SHIP_TO_NAME
	FROM E_EDI_INVOICE_HDR
	WHERE EDI_SHIP_TO_NAME LIKE 'CH%'
)")

The first thing your connection string does is refer to the NAME OF THE 32BIT ORACLE DRIVER. Get this name from the ODBC Data Source Administrator! The default for mine was "Oracle in OraClient11g_home1" but I changed it to specify 32bit at the end when I was testing. Dbq=DSNTNSServiceNameFromUpThere refers to the DSN Name/TNS Service Name referred to above.

 

Good luck. This took me a long, long time to figure out...Very tricky to set up.



Grendahl
  • Members
  • 416 posts
  • Last active: Jul 07 2014 08:01 PM
  • Joined: 10 Aug 2009

VxE (or anyone else that can answer...)

 

I'm having trouble with this script. how can i variablize the part and lot numbers in MyVar?

 

Sending the part and lot normally works, but getting those values inserted as variables into MyVar doesn't work...

;
; AutoHotkey Version: 1.x
; Language:       English
; Platform:       Win9x/NT
; Author:         A.N.Other <myemail@nowhere.com>
;
; Script Function:
;	Retrieve Part/Lot/User/Inspection Time/Labeling Time
;
; References:
; 	http://www.autohotkey.com/board/topic/87780-write-to-ms-sql-from-autohotkey/
;	http://www.autohotkey.com/board/topic/83542-func-adosql-uses-ado-to-manage-sql-transactions-v503l/
;	pass1234 = B4AF804009CB036A4CCDC33431EF9AC

#NoEnv
SendMode Input
SetWorkingDir %A_ScriptDir%
#SingleInstance Force

part := "21-1660-000"
lot := "88274"

; Define a database connection string 
ConnectString =
( ltrim join;
	DRIVER={SQL SERVER}
	SERVER=MyServerName	
	DATABASE:="MyDatabaseName"
	UID=MyUserName
	PWD=MyPassword
	APP=IQS AHK
)

; Create a variable to hold the results of our query - tab delimited -  ConnectString ";coldelim=   `t", "
MyVar := ADOSQL( ConnectString ";rowdelim=   `n", "
(
USE [MyDatabaseName]

DECLARE @return_value INT,
		@PART_NAME VARCHAR(32),
		@LOT_NUM VARCHAR(32)

EXEC	@return_value = RI.GetProcessingTime
		@PART_NAME = N'21-1660-000', ;works
		@LOT_NUM = N'88274' ;works
		@PART_NAME = N' . %part% . ', ;doesn't work
		@LOT_NUM = N' . %lot% . ' ;doesn't work
)")

MsgBox %MyVar%
FileAppend, %MyVar%`n, %A_ScriptName%.txt
;MsgBox Done
ExitApp


; Hotkeys
*$Esc:: ExitApp
!$p:: Pause  ; Alt+P
Pause:: Pause
F12:: Reload
F11:: ListVars

; Functions
/*
###############################################################################################################
######									  ADOSQL v5.04L - By [VxE]									 ######
###############################################################################################################

	Wraps the utility of ADODB to connect to a database, submit a query, and read the resulting recordset.
	Returns the result as a new object (or array of objects, if the query has multiple statements).
	To instead have this function return a string, include a delimiter option in the connection string.

	For AHK-L (v1.1 or later).
	Freely available @ http://www.autohotkey.com/community/viewtopic.php?p=558323#p558323

	IMPORTANT! Before you can use this library, you must have access to a database AND know the connection
	string to connect to your database.

	Varieties of databases will have different connection string formats, and different drivers (providers).
	Use the mighty internet to discover the connection string format and driver for your type of database.

	Example connection string for SQLServer (2005) listening on port 1234 and with a static IP:
	DRIVER={SQL SERVER};SERVER=192.168.0.12,1234\SQLEXPRESS;DATABASE=mydb;UID=admin;PWD=12345;APP=AHK
*/

Global ADOSQL_LastError, ADOSQL_LastQuery ; These super-globals are for debugging your SQL queries.

ADOSQL( Connection_String, Query_Statement ) {
; Uses an ADODB object to connect to a database, submit a query and read the resulting recordset.
; By default, this function returns an object. If the query generates exactly one result set, the object is
; a 2-dimensional array containing that result (the first row contains the column names). Otherwise, the
; returned object is an array of all the results. To instead have this function return a string, append either
; ";RowDelim=`n" or ";ColDelim=`t" to the connection string (substitute your preferences for "`n" and "`t").
; If there is more than one table in the output string, they are separated by 3 consecutive row-delimiters.
; ErrorLevel is set to "Error" if ADODB is not available, or the COM error code if a COM error is encountered.
; Otherwise ErrorLevel is set to zero.

	coer := "", txtout := 0, rd := "`n", cd := "CSV", str := Connection_String ; 'str' is shorter.

; Examine the connection string for output formatting options.
	If ( 9 < oTbl := 9 + InStr( ";" str, ";RowDelim=" ) )
	{
		rd := SubStr( str, oTbl, 0 - oTbl + oRow := InStr( str ";", ";", 0, oTbl ) )
		str := SubStr( str, 1, oTbl - 11 ) SubStr( str, oRow )
		txtout := 1
	}
	If ( 9 < oTbl := 9 + InStr( ";" str, ";ColDelim=" ) )
	{
		cd := SubStr( str, oTbl, 0 - oTbl + oRow := InStr( str ";", ";", 0, oTbl ) )
		str := SubStr( str, 1, oTbl - 11 ) SubStr( str, oRow )
		txtout := 1
	}

	ComObjError( 0 ) ; We'll manage COM errors manually.

; Create a connection object. > http://www.w3schools.com/ado/ado_ref_connection.asp
; If something goes wrong here, return blank and set the error message.
	If !( oCon := ComObjCreate( "ADODB.Connection" ) )
		Return "", ComObjError( 1 ), ErrorLevel := "Error"
		, ADOSQL_LastError := "Fatal Error: ADODB is not available."


	oCon.ConnectionTimeout := 3 ; Allow 3 seconds to connect to the server.
	oCon.CursorLocation := 3 ; Use a client-side cursor server.
	oCon.CommandTimeout := 900 ; A generous 15 minute timeout on the actual SQL statement.
	oCon.Open( str ) ; open the connection.

; Execute the query statement and get the recordset. > http://www.w3schools.com/ado/ado_ref_recordset.asp
	If !( coer := A_LastError )
		oRec := oCon.execute( ADOSQL_LastQuery := Query_Statement )

	If !( coer := A_LastError ) ; The query executed OK, so examine the recordsets.
	{
		o3DA := [] ; This is a 3-dimensional array.
		While IsObject( oRec )
			If !oRec.State ; Recordset.State is zero if the recordset is closed, so we skip it.
				oRec := oRec.NextRecordset()
			Else ; A row-returning operation returns an open recordset
			{
				oFld := oRec.Fields
				o3DA.Insert( oTbl := [] )
				oTbl.Insert( oRow := [] )

				Loop % cols := oFld.Count ; Put the column names in the first row.
					oRow[ A_Index ] := oFld.Item( A_Index - 1 ).Name

				While !oRec.EOF ; While the record pointer is not at the end of the recordset...
				{
					oTbl.Insert( oRow := [] )
					oRow.SetCapacity( cols ) ; Might improve performance on huge tables??
					Loop % cols
						oRow[ A_Index ] := oFld.Item( A_Index - 1 ).Value	
					oRec.MoveNext() ; move the record pointer to the next row of values
				}

				oRec := oRec.NextRecordset() ; Get the next recordset.
			}

		If (txtout) ; If the user wants plaintext output, copy the results into a string
		{
			Query_Statement := "x"
			Loop % o3DA.MaxIndex()
			{
				Query_Statement .= rd rd
				oTbl := o3DA[ A_Index ]
				Loop % oTbl.MaxIndex()
				{
					oRow := oTbl[ A_Index ]
					Loop % oRow.MaxIndex()
						If ( cd = "CSV" )
						{
							str := oRow[ A_Index ]
							StringReplace, str, str, ", "", A
							If !ErrorLevel || InStr( str, "," ) || InStr( str, rd )
								str := """" str """"
							Query_Statement .= ( A_Index = 1 ? rd : "," ) str
						}
						Else
							Query_Statement .= ( A_Index = 1 ? rd : cd ) oRow[ A_Index ]
				}
			}
			Query_Statement := SubStr( Query_Statement, 2 + 3 * StrLen( rd ) )
		}
	}
	Else ; Oh NOES!! Put a description of each error in 'ADOSQL_LastError'.
	{
		oErr := oCon.Errors ; > http://www.w3schools.com/ado/ado_ref_error.asp
		Query_Statement := "x"
		Loop % oErr.Count
		{
			oFld := oErr.Item( A_Index - 1 )
			str := oFld.Description
			Query_Statement .= "`n`n" SubStr( str, 1 + InStr( str, "]", 0, 2 + InStr( str, "][", 0, 0 ) ) )
				. "`n   Number: " oFld.Number
				. ", NativeError: " oFld.NativeError
				. ", Source: " oFld.Source
				. ", SQLState: " oFld.SQLState
		}
		ADOSQL_LastError := SubStr( Query_Statement, 4 )
		Query_Statement := ""
		txtout := 1
	}

; Close the connection and return the result. Local objects are cleaned up as the function returns.
	oCon.Close()
	ComObjError( 1 )
	ErrorLevel := coer
	Return txtout ? Query_Statement : o3DA.MaxIndex() = 1 ? o3DA[1] : o3DA
} ; END - ADOSQL( Connection_String, Query_Statement )

Always have your scripts when you need them with Dropbox.
Sign up for free! http://db.tt/9Hrieqj

yash.dhiman
  • Members
  • 210 posts
  • Last active: May 12 2016 05:49 AM
  • Joined: 22 Aug 2011

hi VxE,

 

I used this function and its works perfectly fine for Inserting,deleting data from sql server but I am having trouble in select statement. I don't know how to get result using select statement.

 

for example I tried following:

 

connectionString := "Provider=SQLOLEDB.1;Password=;Persist Security Info=True;User ID=;Initial Catalog=;Data Source=;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False"
 
query2:="Select" "'" "abc" "'" "from [122014] "
 
objReturn2 := ADOSQL(connectionString, query2)
 
FileAppend,%objReturn2%,%a_workingdir%\abc.txt

 

but it returend nothing...Am I making any mistake... :/

 

 

Thanks



G. Sperotto
  • Members
  • 539 posts
  • Last active: Jun 20 2015 04:54 PM
  • Joined: 12 Dec 2011

Hello Yash.dhiman.

 

The function returns a 2D object (a table), so you will need another syntax to dereference the results. It goes like:

Object[Row, Column]

This syntax is valid only inside expressions.

 

Example:

connectionString := "Provider=SQLOLEDB.1;Password=;Persist Security Info=True;User ID=;Initial Catalog=;Data Source=;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False"
 
query2:="Select " "'" "abc" "'" " from [122014] " ; Added a space after select and before from.
 
objReturn2 := ADOSQL(connectionString, query2)

msgbox % objReturn2[2,1] ; This works because we are forcing expression mode. The first row with useful data is actually the second, since the first contains the column names (None is this case).
 
Value_Of_Row_2_Column_1 := objReturn2[2,1] ; As an alternative, Here we are assigning the value of Row 2, Column 1 to the variable "Value_Of_Row_1_Column_1"

FileAppend, %Value_Of_Row_2_Column_1% ,%a_workingdir%\abc.txt ; And now we can dereference the variable to get the value of Row 2, Column 1 with double percent signs.

Best wishes.


"What is a suitable automation? Whatever saves your day for the greater matters."
Barcoder - Create QR Codes and other Barcodes using only Autohotkey !!


yash.dhiman
  • Members
  • 210 posts
  • Last active: May 12 2016 05:49 AM
  • Joined: 22 Aug 2011

Thanks G.Sperotto. Modification done by you works fine for me.

 

I think there is some alteration required to get Data set (containing multiple tables) in the adosql script or it can return dataset if I run store procedure??



tommyrot
  • Members
  • 35 posts
  • Last active: Jun 23 2015 10:12 AM
  • Joined: 02 Aug 2006

This is really awesome! Thanks for making it.

 

I'm having a little trouble figuring out how to insert a variable from elsewhere in my AHK script into the bit that calls the adosql function. Specifically, I'd like to put a variable into the sqlquery 

Query_Statement := "select borrower#, name from borrower where birth_date in (select birth_date from borrower where borrower# = %myvariable%)"

If I try to do the variable like the above, I get this error:

 

Incorrect syntax near 'myvariable'

 

Any suggestions?



jNizM
  • Members
  • 928 posts
  • Last active: Jul 26 2017 09:19 AM
  • Joined: 01 Aug 2012
Query_Statement := "select borrower#, name from borrower where birth_date in (select birth_date from borrower where borrower# = " myvariable ")"

[AHK] 1.1.26.01 x64 Unicode | [WIN] 10 Pro (Version 1703)
My GitHub Profile | Donations are appreciated if I could help you

tommyrot
  • Members
  • 35 posts
  • Last active: Jun 23 2015 10:12 AM
  • Joined: 02 Aug 2006
Query_Statement := "select borrower#, name from borrower where birth_date in (select birth_date from borrower where borrower# = " myvariable ")"

Thanks very much!



tommyrot
  • Members
  • 35 posts
  • Last active: Jun 23 2015 10:12 AM
  • Joined: 02 Aug 2006

Sorry, one more question about this:

 

The following query is now returning the first row of results for a query that uses a variable defined elsewhere in the script, I am expecting to get several rows of results for this query, any suggestions about how to get more than just the first row in the msgbox?

{
ControlGetText, Bnum, MucTextile1
connectionString := "Provider=SQLOLEDB.1;Password=[mypassword];Persist Security Info=True;User ID=[myuser];Initial Catalog=[mydatabase];Data 


Source=[myserver];Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when 


possible=False"
Query_Statement := "select borrower#, name from borrower where birth_date in (select birth_date from borrower where borrower# = " Bnum ")"
objReturn := ADOSQL(connectionString, Query_Statement)
msgbox, % ADOSQL_LastError "`n`n" ADOSQL_LastQuery
Loop, % objReturn.MaxIndex()
 row .= objReturn[2,A_Index] " "
msgbox, % row
Return
}


jNizM
  • Members
  • 928 posts
  • Last active: Jul 26 2017 09:19 AM
  • Joined: 01 Aug 2012
try objReturn[A_Index, 2]
[AHK] 1.1.26.01 x64 Unicode | [WIN] 10 Pro (Version 1703)
My GitHub Profile | Donations are appreciated if I could help you