… the following code was noted as a way past the Try/Catch piece we had used
put clientInfo into clientInfoCopy -- make a copy not connected to the db
set clientInfoCopy's Birthdate to "12/11/1966"
add record clientInfoCopy to infoTable
So we adjusted our script and it is now as follows:
// Setting up the database
set myDB to (type:"odbc",DSN:"eggPlantDSN",user:"",password:"")
// Puts table values into variables
put table "CreateNewCerts" of myDB into dataTable
put table "ResultsTable" of myDB into resultsTable
// Setting up iterator for CreateNewCertsDB
set dataIterator to iterate over the records of dataTable
// Getting the record from the table
put dataIterator’s nextValue into currentData
put currentData into currentDataCopy
log currentDataCopy
// Adding a record to the Results Table
add record currentDataCopy to resultsTable
When we do that, all of the script runs correctly except for that last ‘add’ line, we get the following error (which I also attached): Syntax error in INSERT INTO statement., SQLSTATE=42000
The error message from the Microsoft Access ODBC driver isn’t very explicit about why the insert into the database failed. You’re reading a record from one table and adding it to another, which should be fine but apparently something is wrong there. I would look at what fields are required in resultsTable or must be unique and be sure you’re meeting those requirements. You may need to delete or modify the primary key value to work in the resultsTable. You might try just creating a record and adding it to that table first to make sure you can add a record there.
I’ve been working with dm83737 and we were running into problems using MS Access so we decided to set up a connection to a Microsoft SQL Server to hopefully avoid getting the INSERT error.
So now when I want to add a record to the Microsoft SQL DB, I get an error. The code we are using is listed below along with the error.
//set up connection to DB
set myDB to (type:"odbc",DSN:"eggPlantTestDB",user:"eggplant",password:"EggPl@nt$")
put table "eggPlant_test.dbo.ResultsTable" of myDB into resultsTable
put table "eggPlant_test.dbo.CreateNewAccount" of myDB into dataTable
//setting up iterator for CreateNewCerts DB
set dataIterator to iterate over the records of dataTable
//getting the record in the table
put dataIterator’s nextValue into currentData
//makes a copy of currentData that is not connected to the db to avoid No Primary Key error w/ MS Access
put currentData into clientInfo
//verify right data is being sent
log clientInfo
//adding a record to the Results Table
add record clientInfo to resultsTable
I’ve ran:
log resultsTable recordInfo
And the result comes back with (Name:“ID”, Type:“integer”) for the fields in the DB. None come back with (KeyType:“Primary”, Name:“ID”, Type:“integer”) and I have a primary key set up. When I run
log dataTable recordInfo
The primary key is not shown for any of the fields in that table as well, even though I have a primary key set up.
I was excited when you came out with the ability to read/write to a DB in the last update because I knew this would help our team greatly when creating the scripts we are needing. So any help with this would be very much appreciated! Thanks!
In case anyone was watching this post for a solution, we received this from TestPlant support:
After further investigation, I think you may be right. I think we’re getting the table definition and using it when we do the insert. And because no value is specified for the identity column, some null value is being used, which the DB doesn’t like. I’ve entered this as bug #2927 in our tracking system.
Sorry for the inconvenience. In the meantime, you can use the executeSQL() function to perform table inserts, e.g.:
put executeSQL(myDB,<<INSERT>>)
I will post the reply from support when it gets fixed.