Help with SQL

[Ace08]

Hi what I'm trying to do here is get the names in our database and return the results to a dbf file. The file creation is working fine even the select statement, however I'm getting an error "Syntax error in INSERT INTO statement." when i add the insert into query with the select query. any help would be appreciated thank you.

expandcollapse popup

Local $DBFConn
Global $oMyError = ObjEvent("AutoIt.Error", "MyErrFunc")
$DBFConn = OpenDBFConn("c:")
Func OpenDBFConn($DBFPath)
$DBFConn = ObjCreate("ADODB.Connection")
$DBFConn.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & $DBFPath & ";" & _
    "Extended Properties=""DBASE IV;"";")
$DBFOpenDBFConn = $DBFConn
$DBFrs = ObjCreate( "ADODB.RecordSet" )
;~ Create a new DBF file named DBFFILETABLE
$DBFrs.open("Create Table DBFFILETABLE (FNAME char(50), LNAME char(50), MINIT char(50))",$DBFConn)
OpenCon()
EndFunc
Func OpenCon()
;~ ======================================================================
;~ DATABASE Connection and Query
;~ ======================================================================
$DBconn = ObjCreate( "ADODB.Connection" )
$DSN = "DRIVER={SQL Server};SERVER=Servername;DATABASE=sqldb;UID=userid;PWD=pwd;"
$DBrs = ObjCreate( "ADODB.RecordSet" )
$DBFrs.open("Insert into DBFFILETABLE (FNAME,LNAME,MINIT;" & $DBrs.Open("select top 10FNAME,LNAME,MINIT from" & _
  " names with (nolock) where date between '02-01-2012 00:00:00.000' and '02-01-2012 23:59:59.998'",$DBConn) ,$DBFConn)
$DBrs.close
MsgBox(0,"Report Maker","File has been created")
;~ ======================================================================
EndFunc


Func MyErrFunc()
 Local $HexNumber, $oMyRet[2]
 $HexNumber = Hex($oMyError.number, 8)
 $oMyRet[0] = $HexNumber
  $oMyRet[1] = StringStripWS($oMyError.description, 3)
 MsgBox(0,"Debug","### COM Error !  Number: " & $HexNumber & "   ScriptLine: " & $oMyError.scriptline & "   Description:" & $oMyRet[1] & @LF); Error Description
 SetError(1); something to check for when this function returns
 Exit
EndFunc   ;==>MyErrFunc

Edited March 9, 2012 by Ace08

[jchd]

You're working with objects here. The .open of your select will not evaluate to the result you expect!

Try issuing the select first, get result into a string and concatenate the string once checked in the insert statement.

[Ace08]

Thanks jchd for the response, I already did that in fact it was my original script.

expandcollapse popup

Local $DBFConn
Global $oMyError = ObjEvent("AutoIt.Error", "MyErrFunc")
$DBFConn = OpenDBFConn("c:")
Func OpenDBFConn($DBFPath)
$DBFConn = ObjCreate("ADODB.Connection")
$DBFConn.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & $DBFPath & ";" & _
    "Extended Properties=""DBASE IV;"";")
$DBFOpenDBFConn = $DBFConn
$DBFrs = ObjCreate( "ADODB.RecordSet" )
;~ Create a new DBF file named DBFFILETABLE
$DBFrs.open("Create Table DBFFILETABLE (FNAME char(50), LNAME char(50), MINIT char(50))",$DBFConn)
OpenCon()
EndFunc
Func OpenCon()
;~ ======================================================================
;~ DATABASE Connection and Query
;~ ======================================================================
$DBconn = ObjCreate( "ADODB.Connection" )
$DSN = "DRIVER={SQL Server};SERVER=Servername;DATABASE=sqldb;UID=userid;PWD=pwd;"
$DBrs = ObjCreate( "ADODB.RecordSet" )
$DBrs.open("select top 10FNAME,LNAME,MINIT from names with (nolock) where date between '02-01-2012 00:00:00.000' and '02-01-2012 23:59:59.998'",$DBConn)
Do
If $DBrs.EOF() Then ExitLoop
$DBFrs.open("Insert into DBFFILETABLE Values('" & $DBrs.Fields(0).value & "', '" & $DBrs.Fields(1).value & "', '" & $DBrs.Fields(2).value & "')" ,$DBFConn)
Until $DBrs.EOF()
$DBrs.close
MsgBox(0,"Report Maker","File has been created")
;~ ======================================================================
EndFunc

Func MyErrFunc()
Local $HexNumber, $oMyRet[2]
$HexNumber = Hex($oMyError.number, 8)
$oMyRet[0] = $HexNumber
  $oMyRet[1] = StringStripWS($oMyError.description, 3)
MsgBox(0,"Debug","### COM Error !  Number: " & $HexNumber & "   ScriptLine: " & $oMyError.scriptline & "   Description:" & $oMyRet[1] & @LF); Error Description
SetError(1); something to check for when this function returns
Exit
EndFunc   ;==>MyErrFunc

Im looking for a way on how to speed up the insert statement. in the above code I'm inserting it per record but this takes time to finish specialy with huge records.

Edited March 12, 2012 by Ace08

[Spiff59]

There are "mass insert" methods.

Something like this might work for you

INSERT INTO DBFFILETABLE AS A (A.FName, A.LName, A.Minit)
SELECT B.FName, B.LName, B.Minit FROM NAMES AS B
WHERE Date(B.Minit) BETWEEN '02-01-2012 00:00:00.000' and '02-01-2012 23:59:59.998';

Since your tables have the same field names you need to qualify at least one of them using an "AS" clause. I did on both tables just for clarity. Also "date" is a keyword for an SQL built-in function, in your example I'm not sure what you are doing with it. This statement is untested and may not be entirely correct. I also think you ought to try using the .execute verb instead of the .open verb on some of your SQL statements (when you aren't planning on manually looping through a result containing multiple records).

[jchd]

Spiff59,

You're overlooking that one DB is SQL server and the other is a dBase IV one.

Ace08,

You may try grouping all your inserts in a transaction on the dBase IV engine.

Also, I'd try grabbing all resultset from dBase at once, using something like:

First get names and store them in AutoIt array using .GetRows(). They are constant over the query, so you don't have to read them repreatedly using $DBrs.Field(n). With that, build your insert query string up to "... values ("

Then:

$aResultset = $DBrs.GetRows()

If successful, $aResultset is a 2D array of data (rows only), which is then faster to manipulate than invoking $DBrs.Value(n) repeatedly.

Lastly, are you bound to creating a dBase IV DB? IMHO switching to an SQLite DB would be way faster.

Edited March 12, 2012 by jchd

[Spiff59]

Spiff59,

You're overlooking that one DB is SQL server and the other is a dBase IV one.

Doh! You're entirely correct! That slipped right past me...

Scratch the "do it all with one SQL statement" idea!

[Ace08]

Thanks Spiff59 and jchd for the help.

@jchd: I've tried using getrows() however this won't work with me, my query results ranges from 700k+ records and autoit(via MyErrFunc()) gives me an error "Not enough storage is available to complete this operation."

[jchd]

OMG if we're talking about that mass of data then things are different.

I'd still try to perform the operation in smaller chunks. SQL server should be smart enough to handle limit and offset SQL clauses efficiently (MS could have invented non-standard names for that, but you get the idea). Use that to read stuff that will always fit within limits (youy may have to experiment worst case). Perform the inserts (wrapped in a transaction) then bump offset and loop until done.

BTW dBase IV is a dog. Are you really bound to use it?

[Ace08]

Thanks jchd for the quick response Unforunately yes im bound with dBase IV as the users require the results in this format. Looks like i'll be needing a different approach with this one.

Oh man my head hurts.

[jchd]

Then I'm afraid I run out of speedup directions, sorry. Someone else will come up with a working idea hopefully!

Good luck anyway.