Getting insert error

[Rock Ridge Farm (Larry)]

I have the following code that faiils to insert a record.
It gives me Syntax Error in Insert Into Statement.

SUB DoNewPW(),INT
   hSql=dbPrepareSQL(pDB,"INSERT INTO users (userver,userid,pw,nuid,group,comment,home,shell) VALUES(?,?,?,?,?,?,?,?)")
   IF LEN(dbGetErrorCode(hSql))
      MESSAGEBOX win,dbGetErrorText(hSql),"INSERT"
      dbFreeSQL(hSql)
      RETURN 1
   ELSE
      dbBindParameter(hSql,1,inssvr,254)
      dbBindParameter(hSql,2,insnm,254)
      dbBindParameter(hSql,3,inspw,254)
      dbBindParameter(hSql,4,insuid,254)
      dbBindParameter(hSql,5,insgrp,254)
      dbBindParameter(hSql,6,inscmt,254)
      dbBindParameter(hSql,7,inshome,254)
      dbBindParameter(hSql,8,insshell,254)
      dbExecute(hSql)
      IF LEN(dbGetErrorCode(hSql))
         MESSAGEBOX win,dbGetErrorText(hSql),"UPDATE"
         dbFreeSQL(hSql)
         RETURN 1
      ENDIF
      dbFreeSQL(hSql)
   ENDIF
   RETURN 0
ENDSUB

[LarryMc]

I took your code; made these definitions:

Code Select Expand

pointer pdb
window win

string comment,home,shell,userver,userid,pw,nuid,group
string inssvr,insnm,inspw,insuid,insgrp,inscmt,inshome,insshell
and it compiled without error

Other than that I don't know what to tell you.

Larry

[LarryMc]

Are "comment,home,shell,userver,userid,pw,nuid,group" exactly the same as when you created your db file?  Seems like that part is case sensitive.

Larry

[Rock Ridge Farm (Larry)]

I thought you had found it - but when I matched the case it still failed.
Compiles but fails when I execute it.

[LarryMc]

Is it too much to send the whole code so I can try to run it?

If you wanted to do that I would be happy to try and help.

I'll PM you my email address if you want to do that.

Larry

[Ionic Wind Support Team]

I beleive it should be:

"INSERT INTO users (userver,userid,pw,nuid,group,comment,home,shell) VALUES('','','','','','','','')"

At lease that is how I have always done it.  See the addressbook sample.

Paul.

[LarryMc]

Paul,

In the program I wrote to put all the help files stuff in a database I used VALUES(?,?,?,?,?,?,?,?) exclusively with the INSERT.


Larry

[Ionic Wind Support Team]

Perhaps both methods work, or is driver dependant?

Paul.

[Ionic Wind Support Team]

Sorry... brain fart.

The line I gave you creates a blank record which I use UPDATE for later on.

Paul.

[Rock Ridge Farm (Larry)]

Larry - I could send you the code but you would need to hack it to work.
It does and sftp to a unix box and gets the password file and loads it into a database.
I do this for 1400 system that I am the admin on.
I will try to strip it down and send the part that is at issue.

[LarryMc]

Or I can strip it.  I just need enough to make sure there's no conflicts with variables.

Larry

[LarryMc]

Larry

Really all I need is the code you used to create the database file; the code you used to open the db file; the code you use to insert; and the code where you declare the variables.

Larry

[LarryMc]

working on it now

Larry

[LarryMc]

I see that you didn't create the database inside your program.

I need the defining structure of that dbase file so I can create it or I need a copy of that file.

Larry

[LarryMc]

In dummying things up Ihad to create a temppw.txt file.

That is when I discovered your line parser was wrong.

This has the one correction and all the missing lines.

Code Select Expand

WHILE READ(myfile,tmptxt) = 0
start = 0
pos = instr(tmptxt, "|", start)
inssvr = mid$(tmptxt, start, pos - 1)
start = pos + 1
pos = instr(tmptxt, "|", start)
insnm = mid$(tmptxt, start, pos - start)
start = pos + 1
pos = instr(tmptxt, "|", start)
insuid = mid$(tmptxt, start, pos - start)
start = pos + 1
pos = instr(tmptxt, "|", start)
insgrp = mid$(tmptxt, start, pos - start)
start = pos + 1
pos = instr(tmptxt, "|", start)
inscmt = mid$(tmptxt, start, pos - start)
start = pos + 1
pos = instr(tmptxt, "|", start)
inshome = mid$(tmptxt, start, pos - start)
start = pos + 1
pos = instr(tmptxt, "|", start)
insshell = mid$(tmptxt, start, pos - start)
IF DoNewPW() = 1
MESSAGEBOX win,"Error Updating Password File","ERROR"
ENDIF
ENDWHILE

Still trying to get there.

Larry

[Rock Ridge Farm (Larry)]

I sent it to your email address you provided.
It is large even stripped down 42K - it was too big and I got an error.

[LarryMc]

did you zip it?

Larry

[LarryMc]

Okay, I found the cause of the problem on insering info from the text file into the users table.

The problem is in this line:

Code Select Expand

hSql=dbPrepareSQL(pDB,"INSERT INTO users (userver,userid,pw,nuid,group,comment,home,shell) VALUES(?,?,?,?,?,?,?,?)")

BTW, I changed everything to lower case while I was looking for the problem.
It appears it doesn't like you naming a field "group".  I renamed it to "xgroup" and it worked (meaning information was written to the users table I created.)

That doesn't mean your program works.  I never saw anything displayed in any of the listviews.  But I didn't dummy that part up.

Hope this gets you over the hump.

Larry

[Rock Ridge Farm (Larry)]

I changed the group field name to Grp - now I no longer get an error but
it creates empty records and never updates the data.

[LarryMc]

Attached is a quick fix file that I created to see if I was actuially reading the pwtmp file; parsing the fields; and adding records to the osaa.mdb file.

I created the osaa file based upon the info in your insert statement.

Just change the path in the eba file to match where you unzip stuff.

You're correct that your program is complicated.
It's awful hard for me to follow(my problem, not yours).

Seems like I had to comment out an if statement about a server string while I was dumming things up.

I would suggest that you break your program up into isolated pieces and address the problem without all the other clutter.

Sorry I'm not being very helpful.

Larry

[LarryMc]

forgot to mention.  Everytime you open a mdb file an .ldb file is created.
If the mdb file is closed properly the .ldb file is automatically deleted.

If your program crashes before the .mdb is closed properly the .ldb is not deleted.

I have had problems before when I tried using a .mdb file when there was already a .ldb existing.
If your program is NOT running and you have a .ldb file you should delete it.

Larry

[Rock Ridge Farm (Larry)]

Still creating empty records - If I comment out the section where the values are assigned to the db it still does the same. I suspect that the problem dbbindparameter is not working - any ideas here?
I verified this by assigning a known value to the first field and it still did not update.

Code Select Expand


IF OPENFILE(myfile,GETSTARTPATH+"pwtmp.txt", "R") = 0
setcontroltext statwin,2000,"Deleting old PW File...Please Wait"
DoDeletePWF()
setcontroltext statwin,2000,"Updating PW Data...Please Wait"
inssvr = "TISCOM2"
WHILE READ(myfile,tmptxt) = 0
start = 0
pos = instr(tmptxt, ":", start)
insnm = mid$(tmptxt, start, pos - start)
start = pos + 1
pos = instr(tmptxt, ":", start)
insuid = mid$(tmptxt, start, pos - start)
start = pos + 1
pos = instr(tmptxt, ":", start)
insgrp = mid$(tmptxt, start, pos - start)
start = pos + 1
pos = instr(tmptxt, ":", start)
inscmt = mid$(tmptxt, start, pos - start)
start = pos + 1
pos = instr(tmptxt, ":", start)
inshome = mid$(tmptxt, start, pos - start)
start = pos + 1
pos = instr(tmptxt, ":", start)
insshell = mid$(tmptxt, start, pos - start)
IF (hStmt_insert)
DBFREESQL(hStmt_insert)
hStmt_insert=dbPrepareSQL(pDB,"INSERT INTO users (userver,UserID,PW,NUID,GID,Comment,Home,Shell) VALUES('','','','','','','','')")
' hStmt_insert=dbPrepareSQL(pDB,"INSERT INTO users (userver,UserID,PW,NUID,GID,Comment,Home,Shell) VALUES('inssvr','insnm','inspw','insuid','insgrp','inscmt','inshome','insshell')")
'MESSAGEBOX win,dbGetErrorText(hStmt_insert),"INSERT"
IF LEN(dbGetErrorCode(hStmt_insert))
MESSAGEBOX win,dbGetErrorText(hStmt_insert),"INSERT"
dbFreeSQL(hStmt_insert)
ELSE
'MESSAGEBOX win,inssvr + " " + insnm,"DEBUG"
dbBindParameter(hStmt_insert,1,"ZZZZZ",254)
dbBindParameter(hStmt_insert,2,insnm,254)
dbBindParameter(hStmt_insert,3,inspw,254)
dbBindParameter(hStmt_insert,4,insuid,254)
dbBindParameter(hStmt_insert,5,insgrp,254)
dbBindParameter(hStmt_insert,6,inscmt,254)
dbBindParameter(hStmt_insert,7,inshome,254)
dbBindParameter(hStmt_insert,8,insshell,254)
dbExecute(hStmt_insert)
IF LEN(dbGetErrorCode(hStmt_insert))
MESSAGEBOX win,dbGetErrorText(hStmt_insert),"UPDATE"
ENDIF
dbFreeSQL(hStmt_insert)
ENDIF
ENDWHILE
ELSE
MESSAGEBOX win,"Failed to get PW file","ERROR"
ENDIF
*/

[LarryMc]

Larry
Attached in the zip are 3 files; the pwtmp.txt file I used; the program to read the mdb directly to see the records in there; a program that shows what works.

The answer was in the help file examples if we had just recognized what we were looking at.

The "insert" line is basically correct after we got the case matching; got rid of "group"; and used the "?" in VALUES.
The dbbind worked as is  although I changed everything to 255 there and in the defs.

Significant problem was that you were executing a dbPrepareSQL(INSERT) for each line from the text file along with the DBBinds.  You were parsing the variables  and then doing the SQL stuff.

If you look at the example, dbPrepareSQL(INSERT) is executed once.
If successful then you do the bindings one time
THEN you do the WHILE and for each line read you parse it and then do a dbExecute.

Works rock solid.

BTW, since you had a $indclude "ctl.inc" I change your parsing routine to use SplitTwo.
Reduced the amount of code from 18  lines to 6  lines.

Unzip the zip to a new empty directory.
Compile as a console and run
It will create a mdb file and then it will read the pw file.

insnm will be printed for each record added.

Then use my other program to read the mdb file to see the records there.

Larry

[Rock Ridge Farm (Larry)]

Thanks - that did it.
Now I need to work on reposition after this routine has done it's work.
This would be to put the screen back where it was prior to the fetch and update.
You have a magic bullet for that?

[LarryMc]

No magic bullet

Larry