create table seems to work everywhere but in ajax callback

[Lance Gurd]

I have the following code that first checks if a table exists, if it does it deletes (drops) it from the MySQL database, and then is supposed to create a new table based on a sub-set of the data in another table

debug(1)
dim ti as sql::TableInfo
dim cn as sql::Connection 
dim args as sql::Arguments
dim sqlnewtable as c
flag=cn.Open("::name::latest")
if cn.GetTableInfo(ti,"bup")=.t. then
    sqlstatement = "Drop table bup"
    cn.Execute(sqlstatement)
end if

sqlnewtable= "create TABLE bup select  TYPE, DESCRIPTION, CONSTANT, A_WASTE, UNITS, COST, NOTE, EXT, Q_iten_no from buildup where QSUM_NO = :qsumno  "
args.Add("qsumno",convert_type(e._currentRowDataNew.QSUM_NO,"n"))
flag = cn.Execute(sqlnewtable,args)
cn.Close()

everything works great until the line flag = cn.Execute(sqlnewtable,args), this returns false. On the debugger the argument qsumno is populated correctly with a numeric figure something like 116707.

If I then hardcode this into SQL command window in Alpha or Toad with create TABLE bup select TYPE, DESCRIPTION, CONSTANT, A_WASTE, UNITS, COST, NOTE, EXT, Q_iten_no from buildup where QSUM_NO = 116707 and execute, the table gets created and the populated with the correct data.

Anyone any ideas why it won't work in a callback?

[Lenny Forziati]

Re: create table seems to work everywhere but in ajax callback

Examine cn.CallResult for more information about the result of cn.Execute()

[Lance Gurd]

Re: create table seems to work everywhere but in ajax callback

Hi Lenny,

I put cn.CallResult in the debugger and it returned

{Code=0,Text=Success,NativeCode=0,NativeText=,Syntax=,API=,RowsAffected=0,Success=.T.,Error=.F.,Canceled=.F.,Warnings=.F.}

the result of flag is .F. and the table is not created

[Pat Bremkamp]

Re: create table seems to work everywhere but in ajax callback

Lance,

Have you checked to make sure that none of the field names are reserved words? If they are, then they need to have backtics "`" around them. Maybe Alpha and Toad are adding those so you can't see the problem.

[Lance Gurd]

Re: create table seems to work everywhere but in ajax callback

Thanks Pat, will check that out, but when I have examined that problem in the past there are [] brackets around any bad field, not spotted that here

[Lance Gurd]

Re: create table seems to work everywhere but in ajax callback

Pat,

2 of the field names were reserved words, I put backticks around them, still no table created, I then changed the offending field names completely and still no table. I am stumped.

Lenny,

Am I missing something with cn.callresult or is everything I get back from the debugger saying that it has worked?

[Davidk]

Re: create table seems to work everywhere but in ajax callback

Lance,

From what I can tell, it's the argument that is causing the problem. This statement works

sqlnewtable = "CREATE  TABLE tblSQLDump3 (PRIMARY KEY (CustId)) SELECT CustId, FName, LName FROM tblUsers WHERE CustId = " + newint

This statement does not

sqlnewtable = "CREATE  TABLE tblSQLDump3 (PRIMARY KEY (CustId)) SELECT CustId, FName, LName FROM tblUsers WHERE CustId = :getCust "

The argument works with a single SELECT, but not with the CREATE. I've no idea why, though.
Since you only have 1 argument you can get around it, but why it fails I don't get.

In my tests I could create the table without the "PRIMARY KEY" stuff, but the table would not open... so I think it's needed.

[Lance Gurd]

Re: create table seems to work everywhere but in ajax callback

Ok, its think outside the box time!

Got it working by different method.

Create BUP table using toad with correct fields in it.

Change my sqlstatement to a 'delete from BUP', to get rid of all existing rows, but keep the table

Change my sqlnewtable to insert the records needed to the BUP table from the buildup table where the argument matches.

This works perfectly.

And thanks for looking David, I was actually typing this solution (but very sidetracked by work) when your solution popped up in my inbox.

[Pat Bremkamp]

Re: create table seems to work everywhere but in ajax callback

Lance,

If the table gets to be big, then using "truncate" will be faster. Plus, the delete approach leaves the autoincrement at wherever it left off, while truncate sets it back to the beginning (in case that matters)

[Lance Gurd]

Re: create table seems to work everywhere but in ajax callback

Thanks Pat, now that I know what truncate does I will definately use it elsewhere.

The BUP table with never be very big.

Basically this is a copy and paste operation, this being the copy part.

Copy a few records from the Buildup table (were all my components that go to make up a price for an object reside) into a temporary holding table - BUP, there is no index field in this table. It uses the parent object number to group the components together. Then use the BUP table to copy it back into the buildup table assigning the new parent object number as they are entered (with a few other bits of code for some other fields), the auto-increment buildup field then looks after itself as it is not copied to/from the BUP table.

For others who may need to do something similar:

Copy part:

dim ti as sql::TableInfo
dim cn as sql::Connection 
dim args as sql::Arguments
dim sqlnewtable as c
flag=cn.Open("::name::latest")
if cn.GetTableInfo(ti,"bup")=.t. then
	sqlstatement = "Delete from bup"
	cn.Execute(sqlstatement)
end if

sqlnewtable= "Insert into bup (TYPE, DESCRIPTION, CONSTANT, A_WASTE, UNITS, COST, NOTE, EXT, Q_iten_no) select TYPE, DESCRIPTION, CONSTANT, A_WASTE, UNITS, COST, NOTE, EXT, Q_iten_no from buildup where QSUM_NO = :qsumno  "
args.Add("qsumno",convert_type(e.dataSubmitted.QSUM_NO,"n"))
flag = cn.Execute(sqlnewtable,args)
cn.Close()

Paste Part:

dim cn as sql::Connection 
dim args as sql::Arguments
dim sqlnewtable as c
flag=cn.Open("::name::latest")


sqlnewtable= "Insert into buildup (TYPE, DESCRIPTION, OUTPUT, CONSTANT, A_WASTE, TOTAL_QTY, UNITS, COST, NOTE, EXT, QSUM_NO, Q_iten_no) select TYPE, DESCRIPTION, :oput, CONSTANT, A_WASTE, (CONSTANT * :oput *(1+(A_WASTE /100))), UNITS, COST, NOTE, EXT,:qsumno, Q_iten_no from bup  "
args.Add("qsumno",convert_type(e.dataSubmitted.QSUM_NO,"n"))
args.Add("oput",convert_type(e.dataSubmitted.quantity,"n"))
flag = cn.Execute(sqlnewtable,args)
cn.Close()