Jump to content
Sign in to follow this  
Ace08

Help with SQL

Recommended Posts

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.

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 by Ace08

Work smarter not harder.My First Posted Script: DataBase

Share this post


Link to post
Share on other sites
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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
Ace08

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

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 by Ace08

Work smarter not harder.My First Posted Script: DataBase

Share this post


Link to post
Share on other sites
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).

Share this post


Link to post
Share on other sites
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 by jchd

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites
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."


Work smarter not harder.My First Posted Script: DataBase

Share this post


Link to post
Share on other sites
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?


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
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. :oops:

Oh man my head hurts. :bye:


Work smarter not harder.My First Posted Script: DataBase

Share this post


Link to post
Share on other sites
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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×