_SQL.au3. ADODB.Connection

[Gardi]

Hi I`m a bit confussed, my english is not so good, and so i am not sure if the Posts above solve my problem...

here is my tasl

i am working on a skript, which includes an sql query to an DSN, so far so good,

im using an odbc and i need a direct access, my db is an AS400 db and its called Ci98PROD

and my query is a kind of complicated^^

Querry:

(select vhistory, fo.fo_id from vhistory vh

inner join vinstanz vi on vi.vinstanz=vh.vinstanz

left outer join titel ti on ti.titel_nr=vi.ankervar1

left outer join forderun fo on fo.fo_nr=vi.ankervar1

where vh.vhistory ="value*")

union

(select vhistory, fo1.fo_id from vhistory vh

inner join vinstanz vi on vi.vinstanz=vh.vinstanz

left outer join titel ti on ti.titel_nr=vi.ankervar1

left outer join forderun fo1 on ti.leitford=fo1.fo_nr

where vh.vhistory ="vallue*")

* value that needs to be transformed

so my problem is, how can I get this in a script?

however, my programming skill is very low when it comes to such specific problems, can anybody help me out?

[Xenobiologist]

Hi Gardi,

post in support and I'll try to help you.

Mega

[jokke]

ChrisL: you have any good ideas on how to get recaffected, like one do with php?

I found a ADO reference over at w3schools (here). But where unable to make it work inn a script.

[ChrisL]

ChrisL: you have any good ideas on how to get recaffected, like one do with php?

I found a ADO reference over at w3schools (here). But where unable to make it work inn a script.

No I don't sorry, have you tried a post in the main support forum, someone there might be able to help

[ChrisL]

NEW VERSION UPLOADED

It's way better than the old one!

See first post

Edited April 23, 2009 by ChrisL

[franzp]

NEW VERSION UPLOADED

It's way better than the old one!

See first post

have anyone tried with MS SQL 2008? or SQL 2008 express ?

thanks in advance

[stinson6016]

I just can't seem to get the example to work for me, I'm running win xp with SQL 2005 express. It won't connect to the database, errors out with the _sql_connect, I've tried changing the localhost to my computer's name. I'm not great at SQL but I know that it should be working

looks like on my system it's "computer name"\SQLEXPRESS

ok, now to get everything else working

-guy

Edited May 5, 2009 by stinson6016

[ChrisL]

I just can't seem to get the example to work for me, I'm running win xp with SQL 2005 express. It won't connect to the database, errors out with the _sql_connect, I've tried changing the localhost to my computer's name. I'm not great at SQL but I know that it should be working

looks like on my system it's "computer name"\SQLEXPRESS

ok, now to get everything else working

-guy

It's been installed as an instance called SQLEXPRESS, you can install multiple instances of SQL

[goldenix]

so this wont work, if Ihave mysql 5.1 running?

[HeXetic]

This function does not appear to work when you try and get information from a table. Each time I get the following message:

_sql.au3 (262) : ==> Error in expression.:

$hQuery = $hConHandle.Execute($vQuery)

$hQuery = ^ ERROR

I'm using AutoIt 3.3.0, I notice the fimction used AutoIt 3.2. Is this the cause of the problem?

[Prozac]

This function does not appear to work when you try and get information from a table. Each time I get the following message:

_sql.au3 (262) : ==> Error in expression.:

$hQuery = $hConHandle.Execute($vQuery)

$hQuery = ^ ERROR

I'm using AutoIt 3.3.0, I notice the fimction used AutoIt 3.2. Is this the cause of the problem?

Got the same problem here. using 3.3.0 and SQL 2008 Express.

Any idea how to fix it?

[ChrisL]

I haven't tried it with the SQL Express editions. They must have different properties.

[tnek]

Thank you!! Thank you!! Thank you!!

As a newb I had been struggling with getting ANY connection working. The _sql.au3 solved my problems (I am still working on why). I am testing with SQLExpress 2008 and did not change anything except the server/db name.

[ChrisL]

Ahh that's good to know!

Thanks for the feedback

[learvit]

Hi ChrisL:

Your Work is great, Help me to make my firs Program with SQL, and now is working fine, So now im working in another software but I need to kno How i can get only the data from a specific field only the exact information, without the table names y try of many ways whit this

_SQL_GetTableAsString(-1,"SELECT Client,Credit FROM Gen_Clientes where Id=1;",$vString)

but always i get the table name and then, the data what i whant.

and i try this

_SQL_Execute(-1,"SELECT Client,Credit FROM Gen_Clientes where Id=1")

But dont show nothing

can you please helpme?

many thanx!

[learvit]

My Mistake, I find out in _SQL.au3 in

Show table names = 1 need to be 0 sorry

IS GREAT..

Many Thanx!

[Tomasz]

I have this error from SQL

[Microsoft][ODBC SQL Server Driver]Sessiontimeout

SQLManager runs query 2:13min but AutoIT don't wait this time.

Edited May 27, 2010 by Tomasz

[ChrisL]

I have this error from SQL

[Microsoft][ODBC SQL Server Driver]Sessiontimeout

SQLManager runs query 2:13min but AutoIT don't wait this time.

Bit late but...

expandcollapse popup

; #FUNCTION# ===================================================================
; Name ..........: _SQL_ConnectionTimeout
; Description ...: Sets and retrieves SQL ConnectionTimeout
; Syntax.........:  _SQL_ConnectionTimeout($ADODBHandle,$iTimeout)
; Parameters ....: $ADODBHandle - ADODB.Connection handle.
;                  $iTimeout   - The timeout period to set if left blank the current value will be retrieved
; Return values .: On Success - Returns Connection timeout period
;                  On Failure - Returns $SQL_ERROR and $SQLErr is set.
;                  .Use _SQL_GetErrMsg() to get text error information
; Author ........: Chris Lambert
; Modified ......:
; Remarks .......:
; Related .......:
; Link ..........;
; Example .......; no
; ==============================================================================
Func _SQL_ConnectionTimeout($ADODBHandle = -1,$iTimeOut = "")
    
    $SQLErr = ""
     If $ADODBHandle = -1 Then $ADODBHandle = $SQL_LastConnection
    
    If Not IsObj($ADODBHandle) Then
        $SQLErr = "Invalid ADODB.Connection object, use _SQL_Startup()"
        Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf
    
    If $iTimeOut = "" then Return $ADODBHandle.ConnectionTimeout
    
    If NOT isInt($iTimeOut) then 
        $SQLErr = "ConnectionTimeOut value must be an integer"
        Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf
    
    $ADODBHandle.ConnectionTimeout = $iTimeOut
    Return $ADODBHandle.ConnectionTimeout
    
EndFunc   ;==>_SQL_Startup

; #FUNCTION# ===================================================================
; Name ..........: _SQL_CommandTimeout
; Description ...: Sets and retrieves SQL CommandTimeout
; Syntax.........:  _SQL_CommandTimeout($ADODBHandle,$iTimeout)
; Parameters ....: $ADODBHandle - ADODB.Connection handle.
;                  $iTimeout   - The timeout period to set if left blank the current value will be retrieved
; Return values .: On Success - Returns SQL Command timeout period
;                  On Failure - Returns $SQL_ERROR and $SQLErr is set.
;                  .Use _SQL_GetErrMsg() to get text error information
; Author ........: Chris Lambert
; Modified ......:
; Remarks .......:
; Related .......:
; Link ..........;
; Example .......; no
; ==============================================================================
Func _SQL_CommandTimeout($ADODBHandle = -1,$iTimeOut = "")
    
    $SQLErr = ""
     If $ADODBHandle = -1 Then $ADODBHandle = $SQL_LastConnection
    
    If Not IsObj($ADODBHandle) Then
        $SQLErr = "Invalid ADODB.Connection object, use _SQL_Startup()"
        Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf
    
    If $iTimeOut = "" then Return $ADODBHandle.CommandTimeout
    
    If NOT isInt($iTimeOut) then 
        $SQLErr = "CommandTimeOut value must be an integer"
        Return SetError($SQL_ERROR, 0, $SQL_ERROR)
    EndIf
    
    $ADODBHandle.CommandTimeout = $iTimeOut
    Return $ADODBHandle.CommandTimeout
    
EndFunc   ;==>_SQL_Startup

[ChrisL]

The timeout functions have been added to the main _SQL.au3 script on post 1

[CarlH]

I found a small bug in _SQL_ExcelConnect() which produces the error "Could not find installable ISAM." After some searching, I found the answer at http://www.connectionstrings.com/excel - the "Extended Properties" values in the connection string need to be quoted.

IE. Change the line "Extended Properties=Excel 8.0;HDR=" & $HDR & ";")

to "Extended Properties='Excel 8.0;HDR=" & $HDR & "';")

Thanks for the great UDF,

Carl